Skip to content

Using (or not using) tabular KPIs

April 3, 2012

You can create KPIs in the tabular model. They work like KPIs in the multidimensional model. Here is the MSDN tutorial on creating KPIs in case you are interested in the details.

There’s a bug in SQL Server 2012 in the SSDT designer. Those icons at the bottom of the KPI dialog box do not work. You see the ordinary red, yellow, and green icons for the KPI status regardless of the icon set you choose (note: the icons work fine in PowerPivot). Also, you cannot specify an expression for KPI trends. The trend field is exposed in the field list, but it is always an arrow across.

Say you’re fine with the stock dots and lack of KPI trends. You went and created a KPI. Now try to use it. You can use the KPI in Excel without issue. Don’t try to use the KPI in Power View – KPIs are not supported in Power View in SQL Server 2012. Don’t try to use the KPI in PerformancePoint – there is a bug in PerformancePoint that prevents the import of the KPIs. Don’t expect to see the status indicator in Reporting Services – you have to set the performance indicator again anyway.

So what is the advantage of having a KPI over having a measure that computes a value of –1, 0, or 1 depending on various thresholds? Not much. The KPI UI just saves you the trouble of writing the thresholds out in DAX. It doesn’t buy you icon selection, it doesn’t allow you to set information for trends, and it doesn’t buy you an instant drag/drop of KPIs (including status indicator) in any tool other than Excel. I will leave the determination as to whether or not a tabular KPI is useful in SQL Server 2012 as an exercise for the reader.

PS: if you don’t like the stock dot in Excel for your KPIs, you can change by applying Conditional Formatting, like so:


Leave a Comment

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: