What is the KPI Browser doing?

Have you ever run the KPI Browser and wondered how it produces its summary of KPI's. I had someone ask me about this today so I fired up SQL Profiler and had a look at the queries that were fired off while the KPI Browser tab was populating.

What I found was that a number of XML/A Discover commands were executed, the Key one appears to be the one against MDSCHEMA_KPIS

Here is the query I captured against the Adventure Works Database: 

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

 

      <RequestType>MDSCHEMA_KPIS</RequestType>

 

    <Restrictions>

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

        <CUBE_NAME>Adventure Works</CUBE_NAME>

        <CUBE_SOURCE>3</CUBE_SOURCE>

      </RestrictionList>

      </Restrictions>

 

    <Properties>

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

        <Catalog>Adventure Works DW</Catalog>

        <ShowHiddenCubes>True</ShowHiddenCubes>

        <SspropInitAppName>Microsoft Visual Studio</SspropInitAppName>

        <Timeout>3600</Timeout>

        <LocaleIdentifier>3081</LocaleIdentifier>

        <ClientProcessID>200</ClientProcessID>

        <Content>Data</Content>

        <Format>Tabular</Format>

      </PropertyList>

    </Properties>

 

</Discover>

 

 

If you want to see the full resultset open a new xmla query in SSMS and paste in the above query. The following is a subset of the XML/A results:

 

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

  <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

    <row>

      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

      <CUBE_NAME>Adventure Works</CUBE_NAME>

      <MEASUREGROUP_NAME>Internet Sales</MEASUREGROUP_NAME>

      <KPI_NAME>Growth in Customer Base</KPI_NAME>

      <KPI_CAPTION>Growth in Customer Base</KPI_CAPTION>

      <KPI_DESCRIPTION>The ratio between the customer count in the current period to that of the previous period.</KPI_DESCRIPTION>

      <KPI_DISPLAY_FOLDER>Customer Perspective\Expand Customer Base</KPI_DISPLAY_FOLDER>

      <KPI_VALUE>[Measures].[Growth in Customer Base]</KPI_VALUE>

      <KPI_GOAL>[Measures].[Growth in Customer Base Goal]</KPI_GOAL>

      <KPI_STATUS>[Measures].[Growth in Customer Base Status]</KPI_STATUS>

      <KPI_TREND>[Measures].[Growth in Customer Base Trend]</KPI_TREND>

      <KPI_STATUS_GRAPHIC>Road Signs</KPI_STATUS_GRAPHIC>

      <KPI_TREND_GRAPHIC>Standard Arrow</KPI_TREND_GRAPHIC>

      <KPI_WEIGHT />

      <KPI_PARENT_KPI_NAME />

      <ANNOTATIONS />

    </row>

    <row>

      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

      <CUBE_NAME>Adventure Works</CUBE_NAME>

      <MEASUREGROUP_NAME>Financial Reporting</MEASUREGROUP_NAME>

      <KPI_NAME>Net Income</KPI_NAME>

      <KPI_CAPTION>Net Income</KPI_CAPTION>

      <KPI_DESCRIPTION>Total earnings less operating expenses, taxes, interest, depreciation and other expenses.</KPI_DESCRIPTION>

      <KPI_DISPLAY_FOLDER>Financial Perspective\Maintain Overall Margins</KPI_DISPLAY_FOLDER>

      <KPI_VALUE>[Measures].[Net Income Value]</KPI_VALUE>

      <KPI_GOAL>[Measures].[Net Income Goal]</KPI_GOAL>

      <KPI_STATUS>[Measures].[Net Income Status]</KPI_STATUS>

      <KPI_TREND>[Measures].[Net Income Trend]</KPI_TREND>

      <KPI_STATUS_GRAPHIC>Road Signs</KPI_STATUS_GRAPHIC>

      <KPI_TREND_GRAPHIC>Status Arrow - Ascending</KPI_TREND_GRAPHIC>

      <KPI_WEIGHT />

      <KPI_PARENT_KPI_NAME />

      <ANNOTATIONS />

    </row>

 

<... SNIP ...>

 

  </root>

</return>

It then appeared to fire off a separate MDX query for each row in the KPI schema resultset. I don’t know if there was some higher level API involved in this, but my guess is that the Browser Tab in VS.NET is doing these two steps. Chris Harrington has done some interesting stuff around the MDSCHEMA_KPIS, you can find it at http://www.activeinterface.com/b2005_11_22.html.

Print | posted on Tuesday, February 21, 2006 6:44 PM

Comments on this post

# re: What is the KPI Browser doing?

Requesting Gravatar...
Tks very much for your useful post.

Other sources about KPI, I think also useful

http://www.humanresources.hrvinet.com/human-resources-kpi/

rgs
Left by ngo on Jan 16, 2009 1:10 AM

# re: What is the KPI Browser doing?

Requesting Gravatar...
Nice post,

thanks for posting your query, its come in very handy

Thanks
Left by software development on Oct 14, 2009 7:22 PM

Your comment:

 (will show your gravatar)