Ramunas Balukonis Blog

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 Boolean

        AmoServer.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
        Next

        SortArray(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 If

                        jTmp = 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 If

                    DescriptionOK = 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 If

                    jTmp = count

                Next    ‘AMOCalculationProperty In AmoCube.MdxScripts(0).CalculationProperties
            End If

            SortArray(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.