How to retreive KPI from Analysis Services 2005 - olivier pieri's WebLog - Site Home

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;

}