Creating KPI’s for your tabular SSAS Database

Tabular databases give us the ability to create KPI’s for business metrics, which can be used to visually represent performance of a business metric of actual performance to target for quick display to a business unit or manager. If you’ve created KPI’s in Power Pivot, creating KPI’s in a tabular project is very similar. To create our KPI, we’re going to need at least 2 different pieces of information: what our actual performance is and what our target performance is. Most of the time target performance is defined by comparing this time period to last year (or the same time period within last year). This tells us if our business is doing better than it was a year ago, or worse. Let’s start with our Adventure Works tabular database by creating 2 measures: the sum of our internet sales, and the sum of out internet sales last year. On a side note, make sure your date dimension is marked as the date dimension, or your time intelligence function won’t work.

 

Our internet sales measure looks like:

Sales:=SUM(‘Internet Sales'[SalesAmount])

Wow, that was easy. Next is our Sales for last year.

LastYearSales:=CALCULATE(SUM(‘Internet Sales'[SalesAmount]), DATEADD(‘Date'[FullDateAlternateKey], -1, YEAR))

Now we have the 2 measures that we need. Right click on the first measure we created, about sales, and select “Create KPI”

image

 

Once the screen opens, you’ll then get the KPI screen, which looks like:

image

 

First we want to make sure that the name of our measure is the value. This is the value at the very top of the screen. Under the “Define Target Value” section, we’re selecting what we’re measuring against. In this case, it’s the measure called “Last Year Sales.” Additionally, if we wanted to measure our performance against an absolute value “like “10 million dollars in sales” we would just select the absolute value radio button and hard code the value in the field.

 

image

 

Underneath is where we specify what percentage to the target value we want to measure to. Just drag the slider bar until it is at the desired percentage of target that you want to see. Select the icons you want to use, and then click ok. Tabular will process it for a second and then indicate on the screen next to the measure that there is a KPI now associated with it.

image

 

And that’s it. Super simple to set up, visually super cool.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s