OLAP Overview#
In this chapter, some theoretical concepts about multi-dimensional structures are explained:
- Cubes
- Measure Groups
- Measures
- Dimensions
- Security
- Client Applications
Cubes#
Cubes are self-contained aggregation data objects that enhance information by allowing the end user to filter, slice, and perform manual calculations in a multi-dimensional manner. The image below illustrates the SQL Server Management Studio connected to the Critical Manufacturing MES's Analysis Services database instance listing the available cubes.
Measure Groups#
Measure groups define the measures inside a cube and also determine the dimensions that can be used to filter or present the measure values. Notice that specific dimensions are available for each cube, but not all measure groups on that cube can make usage of all the dimensions available.
It is important to respect the measure groups and associated dimensions as documented in this document, in order to ensure good performance and logically correct data.
The image below illustrates the SQL Server Management Studio connected to the Critical Manufacturing MES's Analysis Services database instance listing the available cubes and respective measure groups.
Measures#
Measures are a calculated/aggregated fact. All slicing and dicing of data will be done against measures, for example: Resource status time and Production volume.
Measures are available in a specific Cube and are organized in Measure Groups.
The image above illustrates the SQL Server Management Studio, connected to the Analysis Services database instance, and some of the measures available on this product version.
Dimensions#
Dimensions allow users to filter and organize data. All dimension hierarchies have pre-computed data available for filtering (filter expression values). In order to present data, at least one measurement must be selected. In the example below: "Stand By", "Productive Time" and "Engineering Time" were used.
Security#
In order to have access to the available OLAP cubes, please ask the system administrator to add the user windows account into one of the following roles listed in the table below.
| Role | Description |
|---|---|
| cmNavigoDWHAdministrator | This role has administration privileges on the OLAP cubes |
| cmNavigoDWHReader | This role has read-only access to the OLAP cubes |
Table: OLAP Security
Client Applications#
It is possible to access the OLAP data using different client applications. Users are encouraged to use Microsoft Excel or Microsoft SQL Server Management Studio for browsing OLAP Cubes.
Microsoft Excel#
For more information on how to use Microsoft Excel to drill down OLAP cube information, please check the following Microsoft documentation:
- How to Enable Power Pivot on Microsoft Excel ⧉
- Microsoft Power Query ⧉
- Get Data From Analysis Services ⧉
Microsoft SQL Server Management Studio#
To access the OLAP cubes using SQL Server Management Studio follow the steps below:
-
Open Microsoft SQL Server Management Studio (SSMS)
-
Click on Connect and then select Analysis Services
-
Provide the Server name and press Connect. Notice that your Windows account will be used
-
Right-click on the desired cube and then select Browse
-
Select the data, by dragging the desired measures to the selection window and optionally select some dimensions as well
