KPI Preview Feature - SSAS Tabular

[Tsanka]

Hi

The blog information about the October update of PowerBI desktop states that KPI preview visualization is supported from PowerPivot or SSAS tabular.

I created a simple KPI in my tabular and added it into the PowerBI model. The results is having 3 new measure columns (value, status and goal). I cannot figure out how to visualize those 3 parts of the KPI  (as a green-yellow-red indicator for instance). Tried to find information on the documentation site, watched the published videos and ... nothing. Have you managed to do this?

Any help will be much appreciated 🙂

I really hope this to be possible as it is quite annoying when some end users open the powerbi site and point to me that KPI support is available

10x

[PowerBIGuy]

@Tsanka After enabling the KPI preview freature. Have you tired taking the Status value and using it in a matix or card visual?

[Tsanka]

Thanks for the quick reply.

I did try. Attached is a screenshot. The PowerBI desktop was restarted after enabling the preview feature. The status shows just values -1, 0 and 1 for the 3 KPI states.

PS: Just adding the part of the script that adds the 3 measures. May be I have added them incorrectly?

      #"Added Items" = Cube.Transform(MyCube, {{Cube.AddAndExpandDimensionColumn, 
    .....

        {Cube.AddMeasureColumn, "Value", "[Measures].[MarginKPI]"},
        {Cube.AddMeasureColumn, "Goal", "[Measures].[_MarginKPI Goal]"},
        {Cube.AddMeasureColumn, "Status", "[Measures].[_MarginKPI Status]"}}
        )

[meysun]

The upcoming November update should fix this. Once it's available ( very soon :), please give it a try and if the issue still repros, let us know.

[atul_kushwaha]

I think this issue still there even after Nov'15 Update. I have defined KPI in SSAS Tabular Model and connected it as LIVE SOURCE in PBI Desktop but only Measure is being shown, NO Status & Goal measures shown.

Whereas I can see KPI Measure Group in Excel when connect to same Tabular Model.

Regards,

Atul Kushwaha

[atul_kushwaha · Accepted solution]

I managed to resolve this issue.

Issue was witrh compatability mode of SSAS Tabular Model. I set it to "SQL Server 2014 / SQL Server 2012 SP1 (1103):" and defgined KPI started showing in both Power BI Desktopp & Power view.

Hope it will hep others those may face similar problem.

Atul,

[Tsanka]

Hi @atul_kushwaha,

Could you pease describe the steps that you have done to achive the goal? My cube has compatibility level 1103 (seen on database level and as a property in the VS) and still I am not able to see the KPIs. Obviously I am missing something.

If possible, could you pease also paste the defintion of one of your KPIs (the XML element as seen in the XMLA definition of the cube).

Thanks

Tsanka

[atul_kushwaha]

@Tsanka Just change Compatability Level to - SQL Server 2014 / SQL Server 2012 SP1 (1103).  

[Tsanka]

I managed to find some time to try the KPI functionality and again with no success.

I have on-premise tabular cube, its compatibility level is SQL Server 2012 SP1 (1103). There are KPIs defined in the tabular mode.

What I tried is the following:

- connect to the cube and select the fact table. Data are imported into my model, including the defined measures. The measure with KPI is imported, but seen as three fields - value, goal and status.

- connect to the cube and import data using a MDX query. The KPIs are again imported as value, goal and status.

@atul_kushwaha, please advise - how did you manage to have KPIs in your tabular? Again going to Excel? What data type your KPI is in Power Desktop? Thanks

[fabianhenzler]

And if you need to know how to switch from any SSAS Deployment Mode to Tabular Mode without installing a new instance, you should check this out https://www.fabbblyn.com/2016/08/sql-analysis-services-tabular-mode/

[Tsanka]

That's really good news. Thanks for sharing it. Looking forward the November update 🙂

[PowerBIGuy]

I initially had issues getting a KPI to work via a power Pivot model. My issue was that PBI was not recognizing my field as a true KPI. I had to add a power view sheet to my excel file using the kpi value and that somehow fourced PBI desktop to recognize the KPI. The point i'm trying to make is if you are somehow using code or a qiuery to bring in these columns then they might not be recognized as a KPI column. Can you try using a direct connection to the cube and see if that works?

[Tsanka]

Well.. - I managed to make it work

The steps are the following:

- have a tabular SSAS

- create an Excel data model and define the KPIs in the Excel data model

- create a PowerView with those KPIs

- import the Excel workbook into PowerBI desktop

- publish it to the service

- schedule the refresh of the report

As a result I have:

- a report with a matrix with my KPIs indicators;

- I can modify the matrix in the PowerBI desktop however the KPI measures are not displayed as indicators outside the matrix defined in the PowerView

- the KPI indicators are visualized correctly in the service, but the mobile apps present them as a value (-1, 0, 1)

I could not find a way the KPIs from the tabular to be presented in the PowerView->PowerBI. I had to redefine them in the Excel model.

If this is the only way for having KPIs, my opinion is that this is too clumsy and with very limited functionality. Hope this to be changed soon.