Search
Twitter

Entries in MDX (2)

Tuesday
Mar292011

MDX: Retrieving Descriptions for Period over Period on One Row

Overview:  A SQL Server Analysis Services calculation to retrieve the description, on a single row, for a value which differs from period to period.  Useful when retrieving a flattened rowset for the purpose of generating a report from Reporting Services.

In the following example (shown using the cube browser in BIDS), we have values from 0 through 5.  When the time period is added to the columns, and a Metric Name label on the rows, our measures line up nicely from year-to-year when browsing a cube:

     image

The above values are meaningless without some sort of description to help the user understand its meaning.  However, when we add the description associated to the value, we end up with 2 rows per Metric Name, which is not quite what we want:

     image

Instead of the above, what I want is for the data to remain on one row.  This was very important so that I could structure a Reporting Services report correctly.  This is an example of what I do want:

     image

I’m sure there’s several ways to do this … I accomplished it using the following calculated member:

Filter([Metric].[Scale Member Name].children,

       NOT IsEmpty([Measures].[School Measure Value])

       ).Item(0).name

     image

To break down the expression:

  • Filter:  Returns the set that results from filtering a set based on a search condition. 
  • .Children:  This is passing a Children function to the Member being retrieved by the Item(0).name.  If I didn’t specify Children, I would get “All” returned for each row.
  • NOT IsEmpty:  Returns the member name for a non-null measure only.
  • .Item(0).name:  Since the Filter returns a set, we want to use the .Item to return the first tuple of the set only.  The .name specifies we want to see the member name.

Please keep in mind that this approach only works with a “strict” decode list – in my case, I have one and only one description that can equate to a value.  That data integrity is maintained by the ETL process in my situation, so in the above example I didn’t include a condition to gracefully handle the existence of bad data.

Finding More Information

Kevin Goff’s Blog:  When PREVMEMBER alone isn’t enough in MDX

MSDN:  Filter Function

MSDN:  Children Function

MSDN:  IsEmpty Function

MSDN:  Item (Tuple)

Sunday
Oct102010

Display of SSAS KPI Indicators in Excel

When defining a KPI in SQL Server Analysis Services, the indicators selected in SSAS are not matched precisely by a KPI-aware client application such as Excel. For some SSAS indicators, the Excel rendering is very similar; for other indicators, a close option is unavailable. The indicators Excel uses are the same as what’s used for its Icon Sets (found under the Conditional Formatting menu).  

This discussion focuses on what the indicators selected in an SSAS KPI definition will look like in Excel. We will focus on Status and Trend, as shown in the following KPI configuration screen in SSAS:

SSAS_KPI_Configuration 

KPI Properties

The 4 main properties of a KPI include Value, Goal, Status, and Trend, as follows:

Value:

Uses Indicator: No (numeric value only)

Example:

[Measures].[Growth in Customer Base]

 

Goal:

Uses Indicator: No (numeric value only)

Example:

Case

    When IsEmpty

         (

           ParallelPeriod

           (

             [Date].[Fiscal].[Fiscal Year],

             1,

             [Date].[Fiscal].CurrentMember

           )

         )

    Then [Measures].[Sales Amount]             

    Else 1.15 *

         (

           [Measures].[Sales Amount],

           ParallelPeriod

           (

             [Date].[Fiscal].[Fiscal Year],

             1,

             [Date].[Fiscal].CurrentMember

           )

         )

End

Status:

Uses Indicator:  Yes (indicator only; the numeric value of -1, 0 or 1 is not displayed in Excel)

Default Indicator:  Gauge

Example:

Case

    When KpiValue( "Revenue" ) / KpiGoal( "Revenue" ) >  1

    Then 1

    When KpiValue( "Revenue" ) / KpiGoal( "Revenue" ) <= 1

         And

         KpiValue( "Revenue" ) / KpiGoal( "Revenue" ) >= .85

    Then 0

    Else -1

End

Trend:

Uses Indicator:  Yes (indicator only; the numeric value of -1, 0 or 1 is not displayed in Excel)

Default Indicator:  Standard Arrow

Example:

Case

    When IsEmpty

         (

           ParallelPeriod

           (

             [Date].[Fiscal].[Fiscal Year],

             1,

             [Date].[Fiscal].CurrentMember

           )

         )

    Then

    When VBA!Abs

         (

          (

            KpiValue( "Revenue" )

            -

            (

              KpiValue( "Revenue" ),

              ParallelPeriod

              (

                [Date].[Fiscal].[Fiscal Year],

                1,

                [Date].[Fiscal].CurrentMember

              )

            )

          )

          /

          (

            KpiValue( "Revenue" ),

            ParallelPeriod

            (

              [Date].[Fiscal].[Fiscal Year],

              1,

              [Date].[Fiscal].CurrentMember

            )

          ) 

         ) <=.02

    Then 0

    When (

           KpiValue( "Revenue" )

           -

           (

             KpiValue( "Revenue" ),

             ParallelPeriod

             (

               [Date].[Fiscal].[Fiscal Year],

               1,

               [Date].[Fiscal].CurrentMember

             )

           )

         )

         /

         (

           KpiValue( "Revenue" ),

           ParallelPeriod

           (

             [Date].[Fiscal].[Fiscal Year],

             1,

             [Date].[Fiscal].CurrentMember

           )

         ) >.02

    Then 1

    Else -1

End

SSAS Status Indicators

The 9 choices in SSAS translate to 5 displays in Excel.

SSAS Status Indicator selected: Shapes, Thermometer, Cylinder, or Faces 

           SSAS_Shapes

 Rendering in Excel:

Pro: Different shapes as well as different colors.

Con: Excel has no equivalent currently for thermometer, cylinder, or faces.


SSAS Status Indicator selected: Traffic light

          SSAS_TrafficLight

Rendering in Excel:

Status_TrafficLight

Pro: Clear to understand.

Con: Difficult for color blind users to see the difference.  


SSAS Status Indicator selected:  Road signs

          SSAS_RoadSigns

Rendering in Excel:

Status_RoadSigns

Pro: Different shapes as well as different colors


SSAS Status Indicator selected:  Gauge, or Reversed Gauge 

          SSAS_Gauge

Rendering in Excel:

Status_Gauge

Pro: Two choices for a “more is better” type of KPI (the status arrow), or a “less is better” type (the reversed choice).

Cons: Not as clear to users what is good (the solid circle for the regular Gauge) or bad (the open circle for the regular Gauge); Excel has no Gauge equivalent to display (although, as a sidenote, I believe gauges should be used judiciously).


SSAS Status Indicator selected:  Variance Arrow

          SSAS_VarianceArrow

Rendering in Excel:

Status_VarianceArrow

Pro: Clear to understand.

Con: Meaning of this status arrow could easily be confused with a trend arrow. 


  

SSAS Trend Indicators

SSAS Trend Indicator selected:  Standard arrow

          SSAS_StandardArrow

Rendering in Excel:

Trend_StandardArrow

Cons: Black & white; only useful for a “more is better” type of KPI.


SSAS Trend Indicator selected:  Status arrow, or Reversed status arrow

          SSAS_Trend_Indicators

Rendering in Excel:

Trend_StatusArrow

Pros: Clear to understand; Two choices for a “more is better” type of KPI (the status arrow), or a “less is better” type (the reversed choice).


SSAS Trend Indicator selected:  Faces

          SSAS_Faces

Rendering in Excel:

Trend_Faces

Pro: Different shapes as well as different colors.

Con: Not the typical way that an indicator is presented.


To reproduce the above in Excel, the following pivot table options were used with an Analysis Services data connection to the AdventureWorks 2008 cube:  

Excel_PivotTable

Additional comments:

  • Note that each indicator in SSAS will return 3 possibilities: red, yellow and green. Each associates to -1, 0 and 1.
  • If the SSAS indicator is changed (ex: from Gauge to Shapes) in BIDS, you’ll need to (1) redeploy the cube, (2) refresh the data connection in Excel, and (3) delete the Status (and/or Trend, whichever was modified) from the pivot table and then add it back to the pivot table again. The data refresh alone won’t update the indicator image.

If you’re intending to use Excel primarily to browse the cube, I believe that the defaults for KPI indicators in SSAS should usually be changed. Personally, my favorites for Status are Shapes or Road Signs. My favorite for Trends is the Status Arrow.