Online Analytical Processing (OLAP) cubes are a feature in System Center 2016 Service Manager that leverages the Service Manager 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 access to the most granular of data. These cubes are stored in SQL Server Analysis Services databases. Self-service BI tools such as Excel and SQL Server Reporting Services can target these cubes and allow the user to analyze the data from multiple perspectives.
In this recipe, we are going to show you how you can use Microsoft Excel to allow your users to quickly and easily create simple reports by directly accessing OLAP cubes from Service Manager.
Before you can work with OLAP cubes, it is a requirement that you have already installed a Service Manager Data Warehouse Management Server and that it has been registered with the Service Manager server installation. You also need to ensure that the initial synchronization of the management packs is complete and the ETL jobs have run. In addition, you need to make sure that the cubes that are defined in the Management packs have been created and fully processed. This normally takes several hours to complete from the time the Data Warehouse Management Server was registered.
In this example, we are going to create a report in Microsoft Excel that displays the number of incidents by affected user and incident classification. The output will be the same as from the report we created using Report Builder in the Creating reports with Report Builder recipe:
With only these few mouse clicks, we have created a report with the same content like the one created with Report Builder in the Creating reports with Report Builder recipe.
You can now use regular Microsoft Excel features to further customize your report. In order to refresh the data in your report, simply right-click anywhere in the PivotTable and click on Refresh.
Microsoft Excel uses an active connection in the background to connect to the OLAP cube in SQL Services Analysis Services. By making your selections using the PivotTable feature, Excel dynamically creates MultiDimensional eXpression (MDX) queries in the background that are sent to SQL Server Analysis Services. The results returned are then displayed in the PivotTable.
Excel comes with many more features for reporting than we could cover in this book. Next, we are going to show you one example of using Slicers to filter your data.
Slicers are easy-to-use filtering components that allow you to filter the data in the PivotTable with a set of buttons. For instance, you can create a slicer for filtering the PivotTable we created earlier by the status of the incident: