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:
- Open the Service Manager console and navigate to Cubes in the Data Warehouse section.
- Click on the Service Manager WorkItems Cube. Ensure that the Status indicates Processed. Then click on Analyze Cube In Excel from the task pane on the right-hand side. Microsoft Excel will start and automatically establish a connection to the respective cube in SQL Server Analysis Services.
- The list of measures and dimensions in the PivotTable Field List can be overwhelming. Hence we can reduce the size of it by selecting the measure group IncidentDim from the Show Fields related to dropdown.
- Now select the following fields from the list:
- IncidentDimIncidentDimCount
- AffectedUserDimDisplay Name
- IncidentDim_IncidentClassificationMore fieldsIncidentClassificationValue
- Move the IncidentClassificationValue field from the Row Labels area to the Column Labels area.
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:
- With the PivotTable report in Microsoft Excel still open, click anywhere in the PivotTable area, switch to the PivotTable Tools | Options ribbon, and then click on Insert Slicer.
- Select the IncidentDim_IncidentStatusMore fieldsIncidentDim_IncidentStatus.IncidentStatusValue field and click on OK. The Slicer appears in Excel.
- Now you can click on the buttons to filter data. For instance, click on the Resolved button to show only resolved incidents in the PivotTable
