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;
}
}