One of the features I missed in OLAP clients are objects descriptions (measures, calculated member, dimensions and so on). Only SSAS administrator could see objects descriptions. But description information for end users are also needed, even critical. I would like to see description information on OLAP objects tooltip, as example.
In our BI enviroment, we use abbraviations for some fileds. As example we have calculated member “gross margin return on investment”, but more conveniant to name this field as “GMROI” and give him description “gross margin return on investment”. But how to set descriptions and how to show descriptions in OLAP clients? it could be a tricky task.
In Visual Studio, there is easy way to set description for a specific object. Right click on object you want to set a description and click properties. Among all other properties you’ll find “Description”. But there is no properties for calculated members, so therefore unable to set it in Visual Mode. To workaround, open cube code (In Visual Studio, right click on cube and choose “View Code”). Locate calculated member you want to describe under <CalculationProperties> section and add additional tag <Description>. If needed, add description field for Translation as well:
<CalculationProperty dwd:design-time-name=”96f39d00-3468-4509-9018-9bce0452ecf2″>
<CalculationReference>[MEASURES].[GMROI]</CalculationReference>
<CalculationType>Member</CalculationType>
<Description>Gross Margin Return On Investment</Description>
<Translations>
<Translation dwd:design-time-name=”c81e34a5-77f7-4443-85a7-c33044d88c7d”>
<Language>1063</Language>
<Caption>GMROI</Caption>
<Description>Investavimo grąža.</Description>
</Translation>
</Translations>
</CalculationProperty>
Deploy the cube. Now, description for field GMROI is ready.
Another way to add a description on calculated member is to use third party add ins, like BIDS helper: http://www.codeplex.com/bidshelper .
But how to show the actual field name and description in front end? As I said, I do not known any clients that shows OLAP objects descriptions. So, the alternative is to write your own application. Below I pasted the sample code how to connect to analysis services, loop through cubes, then loop through every measure and calculated member in cube, write object name and they descriptions.
Public Sub RunAmo(ByVal DatabaseName As String)
Dim AmoServer As New Microsoft.AnalysisServices.Server
Dim AmoDatabase As New Microsoft.AnalysisServices.Database
Dim AmoCube As New Microsoft.AnalysisServices.Cube
Dim AmoDimension As New Microsoft.AnalysisServices.Dimension
Dim AmoDimensionAttribute As New Microsoft.AnalysisServices.DimensionAttribute
Dim AmoDimensionHierarchy As New Microsoft.AnalysisServices.Hierarchy
Dim AmoMeasureGroup As New Microsoft.AnalysisServices.MeasureGroup
Dim AmoMeasure As New Microsoft.AnalysisServices.Measure
Dim AMOCalculationProperty As Microsoft.AnalysisServices.CalculationProperty
Dim AttributesHierarchiesList(1) As String
Dim DimensionsList(1) As String
Dim CubesList(1) As String
Dim MeasuresCalulationsList(1) As String ‘ Put measures and calculated members in the same array, because later this array is sorted.Dim iTmp As Integer = 0
Dim jTmp As Integer = 0
Dim kTmp As Integer = 0
Dim count As Integer
Dim tString As String
Dim tStringID As String
Dim tStringName As String
Dim tStringDescription As String
Dim DescriptionOK As BooleanAmoServer.Connect(”server_name”)
‘through cubes
iTmp = 0
For Each AmoCube In AmoServer.Databases(DatabaseName).Cubes
count = iTmp + 1
ReDim Preserve CubesList(count - 1)
CubesList(iTmp) = AmoCube.Name.ToString & “|” & AmoCube.ID & “|”For kTmp = 0 To AmoCube.Translations.Count - 1
If AmoCube.Translations(kTmp).Language = 1063 And Not AmoCube.Translations(kTmp).Description Is Nothing Then
If Not AmoCube.Translations(kTmp).Description Is Nothing Then
CubesList(iTmp) = CubesList(iTmp) & AmoCube.Translations(kTmp).Description
End If
End If
Next
iTmp = count
NextSortArray(CubesList)
Response.Write(”<h3>Cubes and fields:</h3>”)
Response.Write(”<table>”)For iTmp = 0 To UBound(CubesList)
tString = CubesList(iTmp).ToString
tStringName = Left(tString, InStr(tString, “|”, CompareMethod.Text) - 1)
tStringID = Mid(tString, InStr(tString, “|”, CompareMethod.Text) + 1, InStr(InStr(tString, “|”, CompareMethod.Text) + 1, tString, “|”) - InStr(tString, “|”, CompareMethod.Text) - 1)
tStringDescription = Mid(tString, InStr(InStr(tString, “|”, CompareMethod.Text) + 1, tString, “|”) + 1)Response.Write(”<tr><tdclsCubeHeader”">Fields for cube <spanclsCubeName”">” & tStringName & “</span></td><tdclsCubeHeader”">” & tStringDescription & “</td></tr>”)
AmoCube = AmoServer.Databases(DatabaseName).Cubes(tStringID)jTmp = 0
For Each AmoMeasureGroup In AmoCube.MeasureGroups
For Each AmoMeasure In AmoMeasureGroup.Measures
If AmoMeasure.Visible = True Then
count = jTmp + 1
ReDim Preserve MeasuresCalulationsList(count - 1)For kTmp = 0 To AmoMeasure.Translations.Count - 1
If AmoMeasure.Translations(kTmp).Language = 1063 Then
If Not AmoMeasure.Translations(kTmp).Caption Is Nothing Then
MeasuresCalulationsList(jTmp) = AmoMeasure.Translations(kTmp).Caption.ToString & “|”
Else
MeasuresCalulationsList(jTmp) = AmoMeasure.Name & “|”
End If
Exit For
End If
Next
DescriptionOK = False
For kTmp = 0 To AmoMeasure.Translations.Count - 1
If AmoMeasure.Translations(kTmp).Language = 1063 Then
If Not AmoMeasure.Translations(kTmp).Description Is Nothing Then
MeasuresCalulationsList(jTmp) = MeasuresCalulationsList(jTmp) & AmoMeasure.Translations(kTmp).Description.ToString & “<br>”
DescriptionOK = True
End If
Exit For
End If
Next
If Not AmoMeasure.Description Is Nothing And DescriptionOK = False Then
MeasuresCalulationsList(jTmp) = MeasuresCalulationsList(jTmp) & AmoMeasure.Description.ToString & “<br>”
End IfjTmp = count
End If
Next ‘AmoMeasure In AmoMeasureGroup.Measures
Next ‘AmoMeasureGroup In AmoCube.MeasureGroups
If AmoCube.MdxScripts.Count > 0 Then
For Each AMOCalculationProperty In AmoCube.MdxScripts(0).CalculationProperties
‘If AMOCalculationProperty.Visible = True Then
count = jTmp + 1
ReDim Preserve MeasuresCalulationsList(count - 1)If AMOCalculationProperty.Translations.Count <> 0 Then
MeasuresCalulationsList(jTmp) = AMOCalculationProperty.Translations(0).Caption.ToString & “|”
Else
MeasuresCalulationsList(jTmp) = AMOCalculationProperty.Description.ToString & “|”
End IfDescriptionOK = False
If AmoMeasure.Translations.Count <> 0 Then
If Not AMOCalculationProperty.Translations(0).Description Is Nothing Then
MeasuresCalulationsList(jTmp) = MeasuresCalulationsList(jTmp) & AMOCalculationProperty.Translations(0).Description.ToString & “<br>”
DescriptionOK = True
End If
End If
If Not AMOCalculationProperty.Description Is Nothing And DescriptionOK = False Then
MeasuresCalulationsList(jTmp) = MeasuresCalulationsList(jTmp) & AMOCalculationProperty.Description.ToString & “<br>”
End IfjTmp = count
Next ‘AMOCalculationProperty In AmoCube.MdxScripts(0).CalculationProperties
End IfSortArray(MeasuresCalulationsList) ‘Call external function to sort MeasuresCalulationsList
For jTmp = 0 To UBound(MeasuresCalulationsList)
tString = MeasuresCalulationsList(jTmp).ToString
tStringName = Left(tString, InStr(tString, “|”, CompareMethod.Text) - 1)
tStringDescription = Mid(tString, InStr(tString, “|”, CompareMethod.Text) + 1)Response.Write(”<tr><tdclsMeasures”">”)
Response.Write(tStringName)
Response.Write(”</td><td clsMeasuresDescription”">”)
Response.Write(tStringDescription)
Response.Write(”<tr><td>”)Next
Next
Response.Write(”</table”)End Sub
This is only way to describe you cube.