THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

ADOMD.NET source code for querying KPIs in Ananlysis Services 2005 in ASP.NET

One of the more publicized features of Analysis Services 2005 is the intrinsic support for the KPIs (for more information on Analysis Services 2005 check out KPIs are defined by cube designer and any client application can programmatically get the rich metadata about them (i.e. Goals, Trends, Graphics etc) and query them. Support for programmability of KPIs is built-in into all layers of Analysis Services APIs - schema rowset in XML for Analysis, OLEDB for OLAP, MDX functions, objects in ADOMD.NET object model etc. In September Olivier Pieri visited our team in Redmond for two weeks in a SWAP project, and implemented KPIViewer - an ASP.NET application for querying and displaying KPIs. Now he was kind enough to publish the essentional ADOMD.NET code for it in his blog. Another interesting thing about that source code is that it makes use of parametric queries in ADOMD.NET. Parametric queries are something that application developers have asked for and now Analysis Services 2005 supports them. One additional comment about Olivier's code - when he wrote it, the MDX functions KPIValue, KPIGoal, KPITrend etc didn't accept KPI name as a string, and a result he was forced to use StrToMember functions in the MDX query generation. In the more recent Yukon builds it changed, so the code now will be simplified - i.e. the following snippet

 myKPICommand.CommandText = @"
    SELECT { strtomember(@Value), strtomember(@Goal), strtomember(@Status), strtomember(@Trend) } 
    ON COLUMNS FROM [" +myCubeDef.Name + "]";
 myKPICommand.Parameters.Add(new AdomdParameter("Value", "KPIValue([" + k.Name + "])"));
 myKPICommand.Parameters.Add(new AdomdParameter("Goal", "KPIGoal([" + k.Name + "])"));
 myKPICommand.Parameters.Add(new AdomdParameter("Status", "KPIStatus([" + k.Name + "])"));
 myKPICommand.Parameters.Add(new AdomdParameter("Trend", "KPITrend([" + k.Name + "])"));

would become

 myKPICommand.CommandText = @"
    SELECT { KPIValue(@Value), KPIGoal(@Goal), KPIStatus(@Status), KPITrend(@Trend) } 
    ON COLUMNS FROM [" +myCubeDef.Name + "]";
 myKPICommand.Parameters.Add(new AdomdParameter("Value", k.Name));
 myKPICommand.Parameters.Add(new AdomdParameter("Goal",  k.Name));
 myKPICommand.Parameters.Add(new AdomdParameter("Status",k.Name)); 
 myKPICommand.Parameters.Add(new AdomdParameter("Trend", k.Name)); 

Finally, below is the sample screenshot of how it would look like: 

Published Monday, December 06, 2004 7:31 PM by mosha
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement