Partilhar via

How to retreive KPI from Analysis Services 2005

  • Artigo

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

  • Anonymous
    December 06, 2004
    ADOMD.NET source code for querying KPIs in Ananlysis Services 2005 in ASP.NET

  • Anonymous
    October 25, 2007
    Last week I worked with a customer who wanted to give users the ability to create and subscribe to alerts

  • Anonymous
    January 05, 2008
    PingBack from http://boxing.247blogging.info/?p=3049

  • Anonymous
    January 05, 2008
    PingBack from http://boxing.247blogging.info/?p=3178

  • Anonymous
    June 14, 2008
    One of the more publicized features of Analysis Services 2005 is the intrinsic support for the KPIs (for

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