How to retreive KPI from Analysis Services 2005


This sample is written in C# and use SQL Server 2005 beta 2 and a beta version of the .Net Framework 2.0. It show how, with ADOMD.Net, you can retreive KPI that are defined in Analysis Services 2005.

1) reference the namespace for ADOMD:

using Microsoft.AnalysisServices.AdomdClient;

2) Build your connection string and connect to Analysis Services

string myConnectionString;

AdomdConnection myKPIConnection;

CubeDef myCubeDef;

 

myConnectionString = “Data Source=” + @myOlapServer + “;Catalog=\”” + @myOlapDatabase + “\””;

myKPIConnection = new AdomdConnection(myConnectionString);

myKPIConnection.Open();

myCubeDef = myKPIConnection.Cubes[myCube];

3) Build the command and query the Olap database :

AdomdCommand myKPICommand;

foreach (Kpi k in myCubeDef.Kpis)

{

   myKPICommand = new AdomdCommand();

   myKPICommand.Connection = myKPIConnection;

//build the MDX query that return the KPI Value

   myKPICommand.CommandText = “SELECT { strtomember(@Value), strtomember(@Goal), strtomember(@Status), strtomember(@Trend) } ON COLUMNS FROM [” +myCubeDef.Name + “]”;

   myKPICommand.Parameters.Clear();

   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 + “])”));

   // Execute query

   CellSet cellset = myKPICommand.ExecuteCellSet();

   // Get values for KPIs

   string kpiName = k.Name;

   try

      {

      string kpiValue = cellset.Cells[0].FormattedValue;

      string kpiGoal = cellset.Cells[1].FormattedValue;

      //Display the Result

      Response.Write(“<td><font face=arial size=3><img src=\”images\\kpi_icon.gif\”></td>”);

      Response.Write(“<td align=right><font face=arial size=3>” + kpiValue + “</td>”);

      Response.Write(“<td align=right><font face=arial size=3>” + kpiGoal + “</td>”);

      Response.Write(“<td><center><img src=” + myGraphicFileInfo.GetKpiImage(k.StatusGraphic, Convert.ToDouble(cellset.Cells[2].Value)) + “></center></td>”);

      //Show the description of the KPI      

      if (k.Description != null && k.Description != “”)

         Response.Write(“<td><center><img src=images\\info.gif title=\”” + k.Description + “\”></center></td>”);

      else

      Response.Write(“<td></td>”);

      Response.Write(“</tr>”);

   }

catch (AdomdErrorResponseException myException)

   {

   }

 

4) the query return a status graphic, and a value, now you have to retreive which graphic you should display, this is the goals of the method myGraphicFileInfo.GetKpiImage(k.StatusGraphic, Convert.ToDouble(cellset.Cells[2].Value), and this is the source of this methode :

using System;

using System.Collections;

/// <summary>

/// Summary description for GraphicFileInfo

/// </summary>

public class GraphicFileInfo

{

public GraphicFileInfo()

{

   InitializeGraphicFileInfo();

}

/// <summary>

/// private class to manage the KPI Icon

/// </summary>

private class GraphicFileInformation

{

   public string FileName;

   public int LastFileNumber;

   public GraphicFileInformation(string fileName, int LastFileNumber)

{

this.FileName = fileName;

this.LastFileNumber = LastFileNumber;

}

}

 

private Hashtable graphicFiles;

/// <summary>

/// Initiatlize the hashtable that contains the icons

/// </summary>

private void InitializeGraphicFileInfo()

{

graphicFiles = new Hashtable();

graphicFiles.Add(“Standard Arrow”, new GraphicFileInformation(“Arrow_Beveled”, 4));

graphicFiles.Add(“XP Arrow”, new GraphicFileInformation(“Arrow_XP”, 4));

graphicFiles.Add(“Status Arrow – Ascending”, new GraphicFileInformation(“Arrow_Status_Asc”, 4));

graphicFiles.Add(“Status Arrow – Descending”, new GraphicFileInformation(“Arrow_Status_Desc”, 4));

graphicFiles.Add(“Traffic Light – Single”, new GraphicFileInformation(“Stoplight_Single”, 2));

graphicFiles.Add(“Traffic Light – Multiple”, new GraphicFileInformation(“Stoplight_Multiple”, 2));

graphicFiles.Add(“Road Signs”, new GraphicFileInformation(“Road”, 2));

graphicFiles.Add(“Gauge – Ascending”, new GraphicFileInformation(“Gauge_Asc”, 4));

graphicFiles.Add(“Gauge – Descending”, new GraphicFileInformation(“Gauge_Desc”, 4));

graphicFiles.Add(“Thermometer”, new GraphicFileInformation(“Therm”, 2));

graphicFiles.Add(“Cylinder”, new GraphicFileInformation(“Cylinder”, 2));

graphicFiles.Add(“Smiley Face”, new GraphicFileInformation(“Smiley”, 2));

}

 

/// <summary>

/// Return the icon to display

/// </summary>

/// <param name=”graphicName”>Name of the KPI Icon</param>

/// <param name=”value”>value of the KPI</param>

/// <returns>The Icon name to display</returns>

public string GetKpiImage(string graphicName, double value)

{

GraphicFileInformation graphicFile = (GraphicFileInformation)graphicFiles[graphicName];

int fileNumber = (int)Math.Round(graphicFile.LastFileNumber * (value + 1.0) / 2.0);

if (fileNumber < 0)

{

fileNumber = 0;

}

else if (fileNumber > graphicFile.LastFileNumber)

{

fileNumber = graphicFile.LastFileNumber;

}

string fileName = “Images/” + graphicFile.FileName + fileNumber + “.gif”;

return fileName;

}

 

}

 

Comments (6)

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

  2. Last week I worked with a customer who wanted to give users the ability to create and subscribe to alerts

  3. One of the more publicized features of Analysis Services 2005 is the intrinsic support for the KPIs (for

  4. Sammy says:

    项目中要从Web端根据维护的参数在Cube中创建KPI,查了一天多资料,只查到MSDN中有一个