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:
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:
Actions (Currently only Drillthrough Actions are supported)
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.
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"
The name of the measure group in the cube. This name must be unique within the cube.
Relationship or CustomFact
The target of the measure group, which must be a fact in the data warehouse.
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:
<Substitution MeasureGroupName="IncidentAssignedTouser" RelationshipEndpoint="Source" Relationship="Workitem!System.WorkItemAssignedToUser" TargetDimension="DWBase!WorkItemDim" ReplacementDimension="IncidentDW!IncidentDim"/>
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:
<Substitution MeasureGroupName="IncidentAssignedToUser" RelationshipEndpoint="Target" Relationship="Workitem!System.WorkItemAssignedToUser" AliasTargetDimensionAs="AssignedToUserDim" TargetDimension="DWBase!UserDim"/>
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” .
The measure group name on which to perform the substitution
(Target, Source, None)
The endpoint of the relationship to perform the substitution. By default, the value is None (for custom facts)
The relationship to use for the substitution.
The role playing name of the original targeted dimension
The role playing name for the substituted dimension
The dimension alias from a custom fact if one exists
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 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.
Name of the named calculation.
The target dimension for the measure
The sql type of the column
The type of the measure
The sub-element <Calculation> contains as its value the definition of the named calculation as a MDX expression.
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.
Name of the measure
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" />
Name of the many-to-many cube dimension
The target measure group to create the many-to-many relationship
The intermediate measure group to create the many-to-many relationship
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>
Name of the KPI
Description of the KPI
MDX script defining the numeric value of the KPI
String (between 0.1 and 1)
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 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:
<Action ID="DrillThroughOnWICreatedByUser" MeasureGroupName="CreatedByUser" ActionType="DrillThrough"> <DrillThroughColumns CubeDimension="WorkItemCreatedByUser_UserDim"> <Property PropertyName="FirstName" /> <Property PropertyName="LastName" /> <Property PropertyName="Company" /> <Property PropertyName="Department" /> <Property PropertyName="Office" /> </DrillThroughColumns> </Action>
Name of the drillthrough action
Targeted measure group of the action
Type of action (Only drillthrough is supported in SCSM 2012)
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:
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.
Give the sample code of a management pack with the CubeExtension
I got crazy then found out that not "AliasReplacementDimensionsAs" but "AliasReplacementDimensionAs ".