Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Testing KPIs for SQL Server Analysis Services


By:   |   Read Comments (1)   |   Related Tips: > Analysis Services Development

Problem

Testing for logic errors in SQL Server Analysis Services Key Performance Indicators (KPIs) can be cumbersome at times. In this tip we look at how you can test your KPIs.

Solution

Fortunately, we can use Reporting Services and T-SQL along with Analysis Services to test the logic in our KPIs. For this tip, I will be using objects provided with the Adventure Works DW 2012 Multidimensional Standard Edition tutorial. I will also be using the cube example I created in the tip Defining Role Playing Dimensions for SQL Server Analysis Services.

We will begin this tip by creating a simple KPI in Analysis Services as shown below. This KPI will return a value of 1 for the Status when the [Measures].[Sales Amount] as shown in the Value Expression box is greater than or equal to 500 dollars, a value of -1 for the Status when the measure value is less than 450, or a value of 0 for the Status when the measure value is greater than or equal to 450 and less than 500. Also, notice that the Status indicator selected is the Traffic light.

Analysis Services KPI Testing

After creating the KPI, the Analysis Services objects need to be deployed and processed so the new KPI and its properties can be seen when browsing the cube. The KPI properties can be dragged and dropped onto the query palette to view their properties.

the Analysis Services objects need to be deployed and processed

We can also execute a T-SQL query to view the rows that contain the source data for the cube measure.

We can also execute a T-SQL query to view the rows

To ensure there are no logic errors for the indicator, we can create a new Reporting Services project in Visual Studio that will allow us to visualize the KPI Indicator and the KPI's property values.

create a new Reporting Services project in Visual Studio

In the first step of the wizard, we will choose our Analysis Services database to be our data source.

choose our Analysis Services database to be our data source

The connection string can be typed manually or created automatically in the Connection Properties window.

the Connection Properties window

The next screen of the wizard is where we will design our query.

Design our query

Clicking on the Query Builder button launches the Query Designer screen. Drag and drop the KPI properties onto the palette just as we did in Analysis Services. Click on OK to return to the wizard.

Clicking on the Query Builder button launches the Query Designer screen

We can see the Analysis Services MDX query displayed on the screen. Click on Next to move to the Report Type screen.

We can see the Analysis Services MDX query displayed on the screen

On the Report Type screen we will choose Table and then click on Next.

On the Report Type screen we will choose Table and then click on Next

On the Design the Table screen, we will move all values in the Available Fields box to the Displayed Fields Details box.

the Design the Table screen

Clicking on Next brings us to the table style page. Select the style of your preference and then click on Next.

Select the style of your preference and then click on Next

Since this is a new Reporting Services project, we are shown the Choose the Deployment Location screen in the wizard. We will leave the defaults and click on Next.

a new Reporting Services project

On the Complete the Wizard screen, name the report and click on Finish.

Complete the Wizard

Next, we will need to add an indicator to the table. In the Reporting Services toolbox, we can see the Indicator object.

the Reporting Services toolbox

Insert a column on the right side of the table and drag an Indicator object from the toolbox to the new table data row cell. When you release the mouse button, the Select Indicator Type window will display. We will select the "3 Signs" indicator in the Shapes box highlighted below and then click on OK.

the Select Indicator Type

The yellow sign indicator displays on the report design palette.

The yellow sign indicator displays on the report design palette

Next, we will set the indicator's properties by right clicking on the Indicator and selecting Indicator Properties. We will select the KPI's Status from the Value drop-down box. For the red icon, we will set the Start and End values to -1. For the yellow icon, we will set the Start and End values to 0. For the green icon, we will set the Start and End values to 1. Click on OK.

the Indicator and selecting Indicator Properties

After clicking on OK, the green sign indicator displays on the report palette.

the green sign indicator displays on the report palette

Clicking on the preview tab allows us to see the display of the red indicator and the KPI's property values. This is expected based on the Status expression we configured in Analysis Services.

the display of the red indicator and the KPI's property values

Next, we will add a row to the Analysis Services cube's source table to bring the measure up to a value that should display the yellow indicator.

we will add a row to the Analysis Services cube's source table

After processing the cube, we can refresh the report and see the yellow indicator displayed along with the updated KPI property values.

refresh the report and see the yellow indicator displayed

Next, we will add another row to the Analysis Services cube's source table to bring the measure up to a value that should display the green indicator.

add another row to the Analysis Services cube's source table

After processing the cube, we can refresh the report and see the green indicator displayed along with the updated KPI property values.

refresh the report and see the green indicator displayed along with the updated KPI property values

This lets us know the logic in the Status expression is working correctly.

Next Steps
  • Review the tip Defining Role Playing Dimensions for SQL Server Analysis Services
  • Read through our Analysis Services Tutorial.
  • Bookmark the SQL Server Analysis Services Glossary for future reference.


  • Last Update:






    About the author
    MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

    View all my tips





    More SQL Server Solutions











    Post a comment or let the author know this tip helped.

    All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

    *Name    *Email    Notify for updates 


    Get free SQL tips:

    *Enter Code refresh code     



    Friday, March 07, 2014 - 2:23:37 AM - lixia Back To Top

     

    very useful, thanks!


    Learn more about SQL Server tools