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">





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

        <CUBE_NAME>Adventure Works</CUBE_NAME>






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

        <Catalog>Adventure Works DW</Catalog>


        <SspropInitAppName>Microsoft Visual Studio</SspropInitAppName>












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">


      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

      <CUBE_NAME>Adventure Works</CUBE_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_WEIGHT />





      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

      <CUBE_NAME>Adventure Works</CUBE_NAME>


      <KPI_NAME>Net Income</KPI_NAME>


      <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_TREND_GRAPHIC>Status Arrow - Ascending</KPI_TREND_GRAPHIC>

      <KPI_WEIGHT />





<... SNIP ...>




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