The case of the vanishing KPIs

I was contacted today with an interesting issue, we had a tabular model that had some KPIs which were not showing up in Power View.

The first thing I checked was the version setting on the model. KPI support was not added to tabular models in SP1. If your model is set to a compatibility version of RTM (1100) Power View will detect this and will effectively not ask for metadata about the KPIs.

However in this case when we checked the database properties from SSMS the compatibility setting appeared to be correctly set to SP1 (1103)

image

So the next thing I did was to open a profiler trace and look at the metadata queries that Power View executed as it started up. Excel treats SSAS Tabular models as if they were multi-dimensional models and queries the metadata using a number of different DISCOVER queries against different schema rowsets. When SSAS Tabular was developed a new schema rowset was introduced called DISCOVER_CSDL_METADATA which is what DAX clients like Power View use to populate their field browser windows.

Checking the command I could see that it was correctly requesting a version 2.0 recordset. If the model was set to a compatibility setting of RTM (1100) or if there was a problem detecting the compatibility setting of the model you may see a 1.0 in the version restriction. Version 1.0 CSDL will not include KPI information. This is so that client tools can specify the version of metadata which they know how to handle.

image

At this point it looks like Power View and SSAS are correctly talking to each other, but we are still no closer to understanding why the KPIs are visible in Excel, but not in Power View.

The next thing to looks at was the actual response returned by the metadata query to see if there was anything strange in there. To do that I took the RestrictionList and PropertyList elements from the profiler trace and inserted them into the Restrictions and Properties elements in the query below. I also had to remove the LocaleIdentifier and DataSourceInfo elements from the PropertyList as these related to a different session. Below is an example of a DISCOVER_CSDL_METADATA command which can be run from an XMLA window in SSMS.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

    <RequestType>DISCOVER_CSDL_METADATA</RequestType>
   
    <Restrictions>
        <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
            <CATALOG_NAME>Adventure Works Tabular</CATALOG_NAME>
            <VERSION>2.0</VERSION>
        </RestrictionList>
    </Restrictions>
    <Properties>
        <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
            <Catalog>Adventure Works Tabular</Catalog>
            <Content>SchemaData</Content>
            <Format>Tabular</Format>
        </PropertyList>
    </Properties>
</Discover>

(you can simply replace the 2 references to the Catalog name in the XMLA below to run this against one of your models)

When I searched in the results for "KPI" I came across the following interesting piece of data.

SNAGHTML18885ef1

Notice the Hidden="true" attribute? It turns out that the original developer decided to hide the measures before creating the KPI which resulted in the KPI itself being hidden. Setting the Hidden property to false on the measure fixed this issue. Mystery solved.

So although the end solution turned out to something simple I thought it might be interesting to share the process

A footnote

Note that we still have one minor issue, now in Excel we can now see both the KPI and the measure, while in Power View we only see the KPI. My suspicion is that this may be a bug in the MDSCHEMA_MEASURES rowset which Excel uses to find out what measures a model has. My opinion is that in order to be consistent with Power View that measures which are used for KPI values should not also be displayed as "normal" measures.

Print | posted on Thursday, July 3, 2014 7:33 AM

Comments on this post

# re: The case of the vanishing KPIs

Requesting Gravatar...
Although this problem didn't end up being a problem with the DISCOVER_CSDL_METADATA schema rowset, I have discovered that there is a problem when you have defined an attribute's ValueColumn property as well as setting OrderBy as AttributeName. The schema rowset returns the following error:
<Error ErrorCode="3238002710" Description="Internal error: An unexpected exception occurred." Source="Microsoft SQL Server 2012 Analysis Services" HelpFile="" />
which means you cannot use any DAX clients. Unfortunately we use these properties heavily though out our cubes so rules out using Power BI.
Left by Will Reader on Dec 24, 2015 8:52 AM
comments powered by Disqus