This is the first post in a series of posts detailing the capabilities of OLAP cubes in the Data Warehouse, a new feature that enables self-service reporting functionality added in SCSM 2012.
Introduction of OLAP Cubes
OLAP (Online Analytical Processing) cubes are a new feature in SCSM 2012 that leverage the existing Data Warehouse infrastructure to provide self-service Business Intelligence capabilities to the end user.
An OLAP Cube is a data structure that overcomes limitations of relational databases by providing rapid analysis of data. Cubes can display and sum up large amounts of data while also providing users queryable access to the most granular of data so it can be rolled up, sliced, and diced as needed to handle the widest variety of questions germane to a user’s domain of interest.
ISV or IT developers with a working knowledge of OLAP cubes will have the capability to define via Management Packs ( MPs) his/her own extensible and customizable OLAP cubes built on top of the Data Warehouse (DW) infrastructure that was provided in SCSM 2010. These cubes will be stored in SQL Server Analysis Services (AS) databases. Self-service BI tools such as Excel and SQL Server Reporting Services can target these cubes in AS and allow the user to analyze the data from multiple perspectives.
The databases that a business uses to store all their transactions and records are called online transaction processing (OLTP) databases. These databases usually have records entered one at a time and contain a wealth of information that can be leveraged by strategists to make informed decisions about their business. The databases that are used to store the data, however, were not designed for analysis and the time and expense involved in retrieving answers from these databases is prohibitive. Online analytical processing databases (OLAP) databases were specialized databases designed to help extract this business intelligence information from the data.
In SCSM 2010, the DW provided OLAP databases that contained data in simplified, read-optimized schemas ready for consumption. The topology of the system is shown in the figure below:
Figure: Topology of SCSM DW V1
One drawback to this system, however, is that OLAP databases essentially contained the exact same type of information found in OLTP databases. There was no pre-calculated aggregations of data to answer increasingly complex and varied queries.
OLAP Cubes can be considered the final piece of the puzzle for a data warehousing solution. An OLAP Cube (aka multidimensional cube or hypercube) is a data structure in SQL Server Analysis Services that is built on top of OLAP databases to allow near-instantaneous analysis of data. The topology of this new system is show below:
Figure: Topology of SCSM DW R2 with Cubes
The exciting new feature of OLAP cubes is that the data in the cube can be contained in an aggregated form. To the user, the cube will seem to know the answers in advance because an assortment of values have already been pre-computed. Without having to query the source OLAP database, the cube can return answers for a wide range of questions almost instantaneously.
Primary Function
The main goal of SCSM 2012 OLAP Cubes is to allow an ISV or IT developer the capability to perform near instantaneous analysis of data for both historical analysis and trending purposes. We do this by enabling the following:
- Allowing users to define cubes in management packs that will be automatically created in Analysis Services at deployment time
- Automatically maintaining the cube behind the scenes (Processing, Partitioning, Translations/Localization, Schema Changes)
- Allowing users to use self-service BI tools such as Excel to analyze the data from multiple perspectives
- Saving generated Excel reports for future reference
A screenshot of the primary DW Cubes UI view is shown below:
Figure: The DW Workspace in the SM UI
Here is an index list of the entire series:
- Part 1: OLAP Cubes in the SCSM Data Warehouse: Introduction
- Part 2: OLAP Cubes in the SCSM Data Warehouse : Key Concepts
- Part 3: OLAP Cubes in the SCSM Data Warehouse : Modeling the OLAP Cube in Management Packs
- Part 4: OLAP Cubes in the SCSM Data Warehouse : OLAP Cube Partitioning
- Part 5: OLAP Cubes in the SCSM Data Warehouse: OLAP Cube Processing
- Part 6: OLAP Cubes in the SCSM Data Warehouse: OLAP Cube Deployment
- Part 7: OLAP Cubes in the SCSM Data Warehouse: Troubleshooting
- Part 8: OLAP Cubes in the SCSM Data Warehouse : Maintenance and Administration