Managing KPIs from Analysis Services

   1: using System;

   2: using System.IO;

   3: using System.Data;

   4: using System.Collections.Generic;

   5: using System.Collections;

   6: using System.Text;

   7: using Microsoft.AnalysisServices;

   8: using Microsoft.AnalysisServices.AdomdClient;

   9: using AnalysisServicesWeb.Data;

   1: public static void CreateOrUpdateKPI(String ServerConnectionString, String DatabaseName, String CubeName,

   2:                                     string kpiName, string kpiDescription, 

   3:                                     String mdxValue, String mdxGoal, String mdxStatus, String mdxTrend, String mdxTimeMember)

   4:         {

   5:             string name = KPIManager.CleanKPIName(kpiName);

   6:             Server server = new Server();

   7:             server.Connect(ServerConnectionString);

   8:                 

   9:             if (server != null && server.Connected)

  10:             {

  11:                 Database database = server.Databases.FindByName(DatabaseName);

  12:                 if (database != null)

  13:                 {

  14:                     Cube cube = database.Cubes.FindByName(CubeName);

  15:                     if (cube != null)

  16:                     {

  17:                         // Check to see if KPI already exist with KPIName

  18:                         Microsoft.AnalysisServices.Kpi kpi = null;

  19:                         foreach (Microsoft.AnalysisServices.Kpi k in cube.Kpis)

  20:                         {

  21:                             if (k.Name == name)

  22:                             {

  23:                                 kpi = k;

  24:                             }

  25:                         }

  26:                         if (kpi == null) {    // New KPI

  27:                             try {

  28:                                 kpi = cube.Kpis.Add(name);

  29:                             } catch (Exception ex) {

  30:                                 // KPI really does exist.

  31:                                 kpi = cube.Kpis.GetByName(name);

  32:                             }

  33:                         }

  34:  

  35:                         kpi.Description = kpiDescription;

  36:                         kpi.CurrentTimeMember = mdxTimeMember;  // "[Request Date].[H1 Year-Month-Date]";                       

  37:                         kpi.Value = mdxValue;                   // "([Measures].[Value], [Metric].[Metric].&[36])"

  38:                         kpi.Goal = mdxGoal;                     // "([Measures].[Value], [Metric].[Metric].&[37])";

  39:                         kpi.StatusGraphic = "Road Signs";

  40:                         kpi.Status = mdxStatus;                 // "case when [Measures].[Measure1]>[Measures].[Measure2]*1.18 then 1 when [Measures].[Measure1]>[Measures].[Measure2]*1.177 then 0.5 when [Measures].[Measure1]>[Measures].[Measure2]*1.173 then 0 when [Measures].[Measure1]>[Measures].[Measure2]*1.17 then -0.5 else -1 end";

  41:                         kpi.TrendGraphic = "Standard arrow";

  42:                         kpi.Trend = mdxTrend;                   // "case when isempty([Date].[Month].PrevMember) then 0 when [Measures].[Measure1] > ([Date].[Month].PrevMember, [Measures].[Measure1]) then 1 when [Measures].[Measure1] > ([Date].[Month].PrevMember, [Measures].[Measure1])*0.94 then 0 else -1 end";

  43:                         cube.Update();

  44:                     }

  45:                 }

  46:                 server.Disconnect();

  47:             }                       

  48:         }

   1: public static void RemoveAllKPIs(String ServerConnectionString, String DatabaseName, String CubeName)

   2:         {

   3:             Server server = new Server();

   4:             server.Connect(ServerConnectionString);

   5:  

   6:             if (server != null && server.Connected)

   7:             {

   8:                 Database database = server.Databases.FindByName(DatabaseName);

   9:                 if (database != null)

  10:                 {

  11:                     Cube cube = database.Cubes.FindByName(CubeName);

  12:                     if (cube != null)

  13:                     {                                                

  14:                         // Check to see if KPI already exist with KPIName

  15:                         List<Microsoft.AnalysisServices.Kpi> kpis = new List<Microsoft.AnalysisServices.Kpi>();

  16:                         foreach (Microsoft.AnalysisServices.Kpi kpi in cube.Kpis)

  17:                         {

  18:                             kpis.Add(kpi);

  19:                             

  20:                         }

  21:                         foreach (Microsoft.AnalysisServices.Kpi kpi in kpis)

  22:                         {

  23:                             cube.Kpis.Remove(kpi, true);

  24:                         }

  25:                         cube.Update();

  26:                     }

  27:                 }

  28:                 server.Disconnect();

  29:             }

  30:         }

   1: public static string CleanKPIName(string KPIName)

   2:         {

   3:             // KPI Names cannot contain any of the following symbols: . , ; ' ` : / \ * | ? " & % $ ! + = ( ) [ ] { } < >

   4:             KPIName = KPIName.Replace(" & "," and ");

   5:             KPIName = KPIName.Replace("&","n");

   6:             KPIName = KPIName.Replace(".", " ");

   7:             KPIName = KPIName.Replace(",", " ");

   8:             KPIName = KPIName.Replace(";", " ");

   9:             KPIName = KPIName.Replace("'", " ");

  10:             KPIName = KPIName.Replace("\"", " ");

  11:             KPIName = KPIName.Replace("`", " ");

  12:             KPIName = KPIName.Replace(":", " ");

  13:             KPIName = KPIName.Replace("/", " ");

  14:             KPIName = KPIName.Replace("\\", " ");

  15:             KPIName = KPIName.Replace("*", " ");

  16:             KPIName = KPIName.Replace("|", " ");

  17:             KPIName = KPIName.Replace("?", " ");

  18:             KPIName = KPIName.Replace("%", " pct ");

  19:             KPIName = KPIName.Replace("$", " ");

  20:             KPIName = KPIName.Replace("!", " ");

  21:             KPIName = KPIName.Replace("+", " ");

  22:             KPIName = KPIName.Replace("=", " ");

  23:             KPIName = KPIName.Replace("(", " ");

  24:             KPIName = KPIName.Replace(")", " ");

  25:             KPIName = KPIName.Replace("[", " ");

  26:             KPIName = KPIName.Replace("]", " ");

  27:             KPIName = KPIName.Replace("}", " ");

  28:             KPIName = KPIName.Replace("{", " ");

  29:             KPIName = KPIName.Replace("<", " ");

  30:             KPIName = KPIName.Replace(">", " ");

  31:             while (KPIName.Length != KPIName.Length)

  32:             {

  33:                 KPIName = KPIName.Replace("  ", " ");   // Replace all double spaces with single space

  34:             }

  35:             return KPIName.Trim();

  36:         }

   1: public static bool KPIExists(string ServerConnectionString, string DatabaseName, string CubeName, string kpiName)

   2: {

   3:     string name = KPIManager.CleanKPIName(kpiName);

   4:     Server server = new Server();

   5:     server.Connect(ServerConnectionString);

   6:  

   7:     if (server != null && server.Connected)

   8:     {

   9:         Database database = server.Databases.FindByName(DatabaseName);

  10:         if (database != null)

  11:         {

  12:             Cube cube = database.Cubes.FindByName(CubeName);

  13:             if (cube != null)

  14:             {

  15:                 // Check to see if KPI already exist with KPIName

  16:                 foreach (Microsoft.AnalysisServices.Kpi k in cube.Kpis)

  17:                 {

  18:                     if (k.Name == name)

  19:                     {

  20:                         return true;

  21:                     }

  22:                 }

  23:             }

  24:         }

  25:     }

  26:     return false;

  27: }