OLAP Cubes in the SCSM Data Warehouse : Modeling the OLAP Cube in Management Packs

System-Center-Team · ‎Feb 15 2019

First published on TECHNET on Feb 03, 2012

This is the third 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.

Modeling the OLAP Cube in a Management Pack

The ability to define customized MP elements was leveraged in order to model the new cube MP elements that are introduced in SCSM 2012. These MP elements allow the user to declaratively define and customize an OLAP cube at a higher level of abstraction. Based on the definition, the deployment of these MP elements would create the correct relationships, components, and fundamental building blocks of the cube at a more granular level of detail without any further user guidance. Two main MP elements were introduced for OLAP cubes:

SystemCenterCube
CubeExtension

SystemCenterCube

The SystemCenterCube element defines the OLAP cube to a varying degree of detail depending on the user's specific needs. This element contains the following subelements:

MeasureGroups
Substitutions
CustomMDX
NamedCalculations
Measures
KPIs
Actions (Currently only Drillthrough Actions are supported)
ManyToManyRelationships

MeasureGroupss:

Each cube contains a collection of facts that exist in the datamart, where each member in the collection corresponds to a measure group. Each measure group must have its own unique name within the cube, but a single fact may correspond to multiple measure groups in a cube. For example, the abstract relationship “WorkItemAssignedToUser” may be defined three times in a cube, with the unique measure group names of “ChangeRequestAssignedToUser, IncidentAssignedToUser, and ProblemAssignedToUser”. As we will see in the Substitution section, the user can customize the fact so only Change Requests, Incidents, and Problems are included in the respective measure group for the cube.

The example below illustrates the MP element for the IncidentAssignedToUser measure group.

< MeasureGroup DateDimAlias ="IncidentAssignedToUserDateDim" MeasureGroupName -"IncidentAssignedTouser" Fact ="DWBase!WorkItemAssignedToUserFact"/>

When the cube is deployed, the dimension, outriggers, and foreign key relationships are automatically calculated and the DSV will be updated with these new elements.

Attribute	Required	Values	Definition
DateDimAlias	No	String	The role playing name of the date dimension which will filter on this measure group. If no alias is defined, the date dim role playing name will automatically be "(MeasureGroupName)_DateDim"
MeasureGroupName	Yes	String	The name of the measure group in the cube. This name must be unique within the cube.
Fact	Yes	Relationship or CustomFact	The target of the measure group, which must be a fact in the data warehouse.

Substitutions:

Since Relationship Facts in the Data Warehouse may target abstract relationships and dimensions, the need exists to allow the user to substitute in concrete dimensions so the measure group will contain only instances of the specific instances that they desire to browse.

This is illustrated in the example below:

In this example, the “IncidentAssignedToUser” measure group points at the “WorkitemAssignedToUser” relationship. This relationship, however, will not only contain incidents but will also contain change requests and problems that have also been assigned to any users. To ensure this measure group only contains incidents, we substitute “WorkItemDim” with “IncidentDim”. What this essentially means under the covers is that the table created in the DSV for this measure group will automatically perform an inner join on WorkItemDim with IncidentDim and return only those instances where a join is valid based on the EntityDimKey or BaseManagedEntityId.

Note that the user must define the relationship endpoint where they want to perform the substitution. This element is required because it is possible that the source and endpoint dimensions are identical and we need a methodology to unique identify which dimension to substitute. An example of such a relationship is “WorkItemRelates to WorkItem”.

The Substitution element is also used to define roleplaying dimensions for the cube. In other words, the user can define a role playing name for a dimension but is not required to actually substitute a dimension. In effect, the substitution in this case is not on the dimension but on the cube dimension or role playing dimension name. An example is shown below:

In this example, the role playing cube dimension name is “AssignedToUserDim”. This is the name of the dimension that will be used to actually filter on this cube. By allowing users to define role playing names, names can be specifically tailored to enable desired many to many relationships in the cube which will allow more advanced filtering and analytical capabilities.

Finally, substitutions are valid not only for relationship facts but custom facts as well. In this scenario, the relationship endpoint would be set to “None” .

Attribute	Required	Values	Definition
MeasureGroupName	Yes	String	The measure group name on which to perform the substitution
RelationshipEndPoint	Yes	(Target, Source, None)	The endpoint of the relationship to perform the substitution. By default, the value is None (for custom facts)
Relationship	No	ManagementPackRelationship	The relationship to use for the substitution.
AliasTargetDimensionAs	No	String	The role playing name of the original targeted dimension
AliasReplacementDimensionsAs	No	String	The role playing name for the substituted dimension
DimensionAlias	No	ManagementPackDimension	The dimension alias from a custom fact if one exists

Custom MDX:

Custom MDX (Multi-Dimensional Expression) scripts allow the user to modify and tailor the cube to the exact specifications to meet their precise needs. Since SCSM cubes are model-based, it is impossible to ascertain all the possible semantic needs of the user when taking into account the wide spectrum of requirements and exact specifications for the domain specific business needs of a particular user. Custom MDX allows the user to define MDX scripts that will be applied on top of the cube to light up the specific scenarios they need to measure and instrument.

Named Calculation:

Named calculations allow the user to define new attributes on a dimension that a custom measure can later target. This allows the end user to essentially extend the dimensional schema and customize the schema to fit their exact needs. Let us look an example from the SystemCenterWorkItemsCube.

<NamedCalculation ID="IncidentsPastTargetResolutionTime" Target="IncidentDW!IncidentDim" ColumnType="Int">
<Calculation>(case when ( (([Status] = 'IncidentStatusEnum.Resolved' OR [Status] = 'IncidentStatusEnum.Closed') AND ResolvedDate > TargetResolutionTime) OR (([Status] != 'IncidentStatusEnum.Resolved' AND [Status] != 'IncidentStatusEnum.Closed') AND GETUTCDATE() > TargetResolutionTime)) then 1 else 0 end )</Calculation>
</NamedCalculation>

In this example, the Incident Dimension contains data such as the status of the incident and the target resolution time. However, no native measure to calculate the number of incidents that did not meet their target resolution time exists. This data is very useful for a systems administrator. We can enumerate the desired scenario using a named calculation and aggregate the data so a custom measure my target the new attribute and bubble up this information to the end user.

Note that NamedCalculation support at this time is only targeted at dimensions . No support currently exists for facts.

Attribute	Required	Values	Definition
ID	Yes	String	Name of the named calculation.
Target	Yes	ManagementPackDimension	The target dimension for the measure
ColumnType	Yes	(Int, Double)	The sql type of the column
Type	No	(Count, Sum)	The type of the measure

The sub-element <Calculation> contains as its value the definition of the named calculation as a MDX expression.

Measures:

Custom measures allow the user to aggregate and display data based on numeric attributes from dimensions. At this time, no support for custom measure based on facts exist. Continuing our example from the Named Calculation above, we define a custom measure on IncidentsPastTargetResolutionTime:

<Measure ID="IncidentsPastTargetResolutionTimeCount" Target="IncidentDW!IncidentDim" Type="Sum" Property="IncidentsPastTargetResolutionTime"/>

Looking at the xml above, the target of the measure is the IncidentDimension and the specific property is “IncidentsPastTargetResolutionTime”. This is the custom property that was defined in the Named Calculation above. Custom Measures may target either native or calculated properties in the dimension.

Finally, the measure type is defined to be a sum. Possible values for a measure type include Sum and Count. At this time because of performance considerations, Distinct Count measure types are not allowed.

Attribute	Required	Values	Definition
ID	Yes	String	Name of the measure
Target	Yes	ManagementPackDimension	The target dimension for the measure
Property	Yes	String	The targeted dimension property
Type	No	(Count, Sum)	The type of the measure

ManyToManyRelationship:

The ManyToManyRelationship allows the cube designer to add custom many-to-many dimensions to the cube, enabling advanced analytic scenarios. Defining many-to-many relationships is out of the scope of this blog post, but the user is strongly encouraged to read and understand about this relatively new concept and its many benefits. The following link is very helpful for introducing these concepts to a novice user: The Many-To-Many Revolution

During cube deployment, we automatically add many-to-many dimensions to the cube for all "one-hop" relationships without any needed user guidance. We do not, however, add many-to-many dimensions for cascading (multi-hop) relationships because of the exponential increase of possible relationships that could be added. Adding all these relationships can have a significant performance hit when browsing the cube, since the aggregations of many-to-many relationships are generally not calculated during processing and the joins will be evaluated while browsing the cube If the user desires a specific cascading many-to-many relationship, they can define one using this MP element and it will be be added accordingly to the cube. Conversely, they can overwrite an automatically generated many-to-many relationship to use a different intermediate measure group in instances where multiple intermediate groups exist (we automatically take the first one encountered in those cases). An example of a many-to-many relationship MP element is given below:

<ManyToManyRelationship CubeDimension="ServiceDim" TargetMeasureGroup="AlertAboutConfigItem" IntermediateMeasureGroup="ServiceContainsConfigItem" />

Attribute	Required	Values	Definition
CubeDimension	Yes	String	Name of the many-to-many cube dimension
TargetMeasureGroup	Yes	String	The target measure group to create the many-to-many relationship
IntermediateMeasureGroup	Yes	String	The intermediate measure group to create the many-to-many relationship

KPI:

Key Performance Indicators (KPI's) allow businesses the quickly estimate the health of an enterprise by measuring its progress toward a predefined goal. Each KPI has a target value and an actual value. The target value is a quantitative goal critical to the success of the organization. Large amounts of data are basically filtered down to one discrete value that can be used to monitor performance and progress towards goals and benchmarks. An example of a KPI is a college having a goal that 90% of their students graduate within 4 years, or a basketball team with a targeted goal of causing the opponent to shoot less than 50% for a game. A scorecard can be used to show a group of KPIs, providing in one instantaneous snapshot the overall health of a business. An example is shown below:

<KPI ID="IncidentResolutiuonKpi" >
<Caption> The ratio of incidents resolved </Caption>
<Value>IIF(([Measures].[IncidentDimCount])> 0,([Measures].[IncidentsResolvedCount]/[Measures].[IncidentDimCount]),null)</Value>
<Goal>1.0</Goal>
<GreenThreshold> 0.75</GreenThreshold>
<YellowThreshold>0.5 </YellowThreshold>
<Direction>Up</Direction>
<StatusGraphic>Thermometer</StatusGraphic>
</KPI>

Attribute	Required	Values	Definition
ID	Yes	String	Name of the KPI
Caption	Yes	String	Description of the KPI
Value	Yes	String	MDX script defining the numeric value of the KPI
Goal	Yes	String	The target value of the KPI
Green Threshold	Yes	String (between 0.1 and 1)	Any number that is above/below this threshold (depending on the direction) is marked as green in the status graphic.
Yellow Threshold	Yes	String (between 0.1 and 1)	Any number that is above/below this threshold (depending on the direction) but does not meet the green threshold is marked as yellow. A number that does not meet the yellow threshold is marked as red in the status graphic.
Direction	Yes	(Up, Down)	If the direction is Up, any numbers above the green/yellow threshold are marked as such. Similarly for Down, numbers below the green/yellow thresholds are marked as such.
Status Graphic	Yes	(Shapes, TrafficLight, RoadSigns, Gauge, ReversedGauge, Thermometer, Cylinder, Faces, VarianceArrow)	The graphic that will represent the KPI.

An example of the Gauge status graphic is illustrated below depicting the percentage of change requests which were completed on-time:

Figure: Gauge KPI Status Graphic for percentage of Change Requests completed on-time

Actions:

Actions are events that a user can trigger on a OLAP cube when accessing data within the cube. For SCSM 2012, only drill through actions are supported. An example is shown below:

Attribute	Required	Values	Definition
ID	Yes	String	Name of the drillthrough action
MeasureGroupName	Yes	String	Targeted measure group of the action
ActionType	Yes	(DrillThrough)	Type of action (Only drillthrough is supported in SCSM 2012)
CubeDimension	Yes	String	The cube dimension that is the target of the action (must be a slicer on the Measure Group)
PropertyName	Yes	String	Attribute of the dimension that is displayed when the drillthrough action is executed

CubeExtension:

The primary motivation for the CubeExtension element was to enable the user to modify the cube after the cube has deployed onto Analysis Services without having to uninstall and reinstall the cube. In scenarios where the cube has been fully processed with years of data, recreating the cube is a very expensive operation since all partitions have to be fully reprocessed.

The CubeExtension element can define the following elements:

NamedCalculation
ManyToManyRelationship
KPI
Measure
Action
CustomMdx

Note that each customization that is defined in a CubeExtension element can also be defined in a SystemCenterCube object. The only customization that is not allowed is the addition of facts/measure groups and substitutions to the cube.

Here is an index list of the entire series:

Products (49)

Special Topics (28)

Video Hub (462)

Most Active Hubs

Most Active Hubs

Video Hub

OLAP Cubes in the SCSM Data Warehouse : Modeling the OLAP Cube in Management Packs