Adding the Description field to Service Manager 2012 Cubes

  • Article

I want to first caveat this post that modifying the cube via PowerShell could break something and it not supported, this should be used in a lab environment.

The Cubes in Service Manager are a great part of the product it makes for very robust reporting and calculating KPI’s a snap.  A common ask I have seen is how to add the description field to the cubes. 

Let me start with this, the primary reason for Analytic cubes is just that to do analysis, finding trends, tracking Key Performance Indicators and such.  For this reason Description doesn’t fit well as most descriptions will be unique and to be used in analysis would make it tough, but there are reasons to have it, comparing descriptions of say all tickets that were assigned to a group, or descriptions from tickets in the same category.

Below is a simple script to get the Description field added to Service Requests.  It can be used for others as well, you just need to update the Dimension and the Cube.  The script first enables the Description field on the Dimension and then enables the Description Field on the Dimension in the applicable Cube.  Both the normal Dimension and the Dimension in the Cube have to be updated for the Description to display.

 [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$servername=New-Object Microsoft.AnalysisServices.Server
$servername.connect("sqlservername")

$DWASDataBase = $servername.Databases | ? {$_.Name -eq 'DWASDataBase'}

$ServiceRequestDim = $DWASDataBase.Dimensions | ? {$_.Name -eq 'ServiceRequestDim'}
$Description = $ServiceRequestDim.Attributes | ? {$_.Name -eq 'Description'}
$Description.AttributeHierarchyEnabled = 'True'
$ServiceRequestDim.Update()

$SRCube = $DWASDataBase.Cubes | ? {$_.Name -eq 'SystemCenterServiceCatalogCube'}
$CubeServiceRequestDim = $SRCube.Dimensions | ? {$_.Name -eq 'ServiceRequestDim'}
$Description = $CubeServiceRequestDim.Attributes | ? {$_.AttributeID -eq 'Description'}
$Description.AttributeHierarchyEnabled = 'True'
$SRCube.Update()

Once the script has ran, you will need to reprocess the cube the description data will now be visible in excel or other BI tools (PowerPivot/Performance Point).