How to Build OLAP Cubes in the SCSM Data Warehouse

Published Feb 15 2019 10:02 AM 415 Views
First published on TECHNET on Feb 01, 2012


Service Manager 2012 data warehouse provides users a model-based approach to build OLAP cubes.

Cubes help us to analyze the Measures (like IncidentAssginedtoUser Count) defined on facts and dimensions (Incident Counts) on a multidimensional axis .  Each cube contains a collection of measure groups and corresponding cube dimensions (like Incident, Time, Priority).

The OLAP infrastructure provided with SCSM 2012 performs the following actions on import of a cube MP

1)     Deploys the cube defined in the Management Pack to SSAS  server provided during the

SCSM 2012 data warehouse setup

2)     Creates a Process job for the cube- Process.CubeID and it runs on the default schedule

The SCSM console provides customers a view of the cubes available and an option to open the cube in Excel  for analysis

System Center data warehouse setup performs the following steps so that the cube can be deployed as soon as an MP is imported:

1)     Creates the Analysis server database

2)     Creates the DataSources  for the data marts

A Data Source View is created up on the deployment of the first cube MP.   System Center deploys cubes out of the box so the DSV is available by default.

System Center Cube Model :

System Center Cube is defined as collection of following MP elements:

1) Measure groups which points to the facts to be included

2) Substitutions to be performed on the fact

3) MDX resources

4) Drill Through Actions

5) KPIs

6)Named Calculations

7) Custom Measures

8)Many to Many Relationships

Here is the XSD you may use to understand the cube schema:

All the elements other than MeasureGroups are optional and can be added as per the Business analysis requirement.


When a user defines a cube using the above schema and Imports MP, the SM 2012 data warehouse deploys the cube using AMO and creates the required infra to maintain it.

For example lets create a cube based on “ComputerHostsOperatingSystemFact”.  Here is the dimensional view of the fact in the warehouse:

The cube can be defined using the following elements in the MP.

< The complete xml  is attached>



< SystemCenterCube ID="ComputerCube">

< MeasureGroups >

<MeasureGroup ID="ComputerHostsOperatingSystem" Fact="DWBase!ComputerHostsOperatingSystemFact" />

</ MeasureGroups >

</ SystemCenterCube >



Import the MP and run the MPsync Job

The Cube will appear in the SM Console in an unprocessed state

Also a DW Process job is created for the cube with a default 24hr Job schedule

Now process the cube either from the UI or cmdlet using this command:

Start-SCDWJob -JobName Process.ComputerCube

Next open the cube in Excel using link from the task pane.

Let’s study the Cube structure which is created.


The following measure groups are created for the above cube:

1)     Measure group corresponding to the fact  ie ComputerHostsOperatingSystemFact with a Count measure

2)     Measure group corresponding to the Dimensions it points to

a)     Computerdim and OperatingsystemDim  with the count measure

For each measure group defined in the cube counts are defined by default for the relationship fact and corresponding custom measure and count for custom fact.

Here is the Pivot table view of the measuregroups corresponding to the fact inExcel

Cube Dimensions:

1)     The outrigger (like the Priority, Status)dimensions corresponding to the fact are added as Cube Dimensions so that user can slice the facts on those dimensions too.

2)     Date Dim is added by default  to the cube as they are relevant to any fact

3)     Also, Entity Status and Relationship Status cube dimensions are defined for all cubes to indicate whether the  entity or relationship is deleted.

Let’s take a look at the fields for the above cube in the Excel Pivot Table field list


Version history
Last update:
‎Mar 11 2019 09:03 AM
Updated by: