Key Performance Indicator (KPI)-Darstellung (tabellarisch)
Ein KPI misst die Leistung eines Werts, der durch ein Basismeasure definiert wird, anhand eines Zielwerts.
KPI (Key Performance Indicator)-Darstellung
In tabellenbasierten Objektmodellen ist ein Schlüsselleistungsindikator -kpi- ein Measure mit zusätzlichen Informationen für die Clientanwendung, um sie grafisch anzuzeigen. Ein KPI weist normalerweise folgende Informationen auf: das angestrebte Ziel, den Status des Measures im Vergleich zum Ziel sowie Informationen dazu, wie der Status grafisch im Clienttool angezeigt werden soll.
KPI (Key Performance Indicator) in AMO
Wenn AMO zum Verwalten eines KPIs für ein tabellarisches Modell verwendet wird, besteht keine 1:1-Objektentsprechung zu einem KPI in AMO. Das AMO-Objekt Kpi wird nicht zu diesem Zweck verwendet. In AMO wird ein KPI für tabellarische Modelle durch eine Reihe von Objekten dargestellt, die in einem der Elemente in der Commands-Auflistung sowie in CalculationProperties erstellt wurden.
Der folgende Codeausschnitt veranschaulicht die Erstellung einer der vielen möglichen KPI-Definitionen.
private void addStaticKPI(object sender, EventArgs e)
{
double KPIGoal = 0, status1ThresholdValue = 0, status2ThresholdValue = 0
, redAreaValue = 0, yellowAreaValue = 0, greenAreaValue = 0;
string clientStatusGraphicImageName = "Three Circles Colored";
//Verify input requirements
//Goal values
if (staticTargetKPI.Checked)
{//Static KPI Goal selected
if (string.IsNullOrEmpty(staticTargetKPIGoal.Text)
|| string.IsNullOrWhiteSpace(staticTargetKPIGoal.Text)
|| !double.TryParse(staticTargetKPIGoal.Text, out KPIGoal))
{
MessageBox.Show(String.Format("Static Goal is not defined or is not a valid number."), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
else
{//Measure KPI Goal selected
if (!TargetMeasureForKPISelected)
{
MessageBox.Show(String.Format("Measure Goal is not selected."), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
//Status
if (string.IsNullOrEmpty(firstStatusThresholdValue.Text)
|| string.IsNullOrWhiteSpace(firstStatusThresholdValue.Text)
|| !double.TryParse(firstStatusThresholdValue.Text, out status1ThresholdValue)
|| string.IsNullOrEmpty(secondStatusThresholdValue.Text)
|| string.IsNullOrWhiteSpace(secondStatusThresholdValue.Text)
|| !double.TryParse(secondStatusThresholdValue.Text, out status2ThresholdValue))
{
MessageBox.Show(String.Format("Status Threshold are not defined or they are not a valid number."), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrEmpty(statusValueRedArea.Text)
|| string.IsNullOrWhiteSpace(statusValueRedArea.Text)
|| !double.TryParse(statusValueRedArea.Text, out redAreaValue)
|| string.IsNullOrEmpty(statusValueYellowArea.Text)
|| string.IsNullOrWhiteSpace(statusValueYellowArea.Text)
|| !double.TryParse(statusValueYellowArea.Text, out yellowAreaValue)
|| string.IsNullOrEmpty(statusValueGreenArea.Text)
|| string.IsNullOrWhiteSpace(statusValueGreenArea.Text)
|| !double.TryParse(statusValueGreenArea.Text, out greenAreaValue))
{
MessageBox.Show(String.Format("Status Area values are not defined or they are not a valid number."), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Set working variables
string kpiTableName = ((DataRowView)MeasuresInModelList.CheckedItems[0])[0].ToString();
string kpiMeasureName = ((DataRowView)MeasuresInModelList.CheckedItems[0])[1].ToString();
//Verify if KPI is already defined
if (modelCube.MdxScripts["MdxScript"].CalculationProperties.Contains(string.Format("KPIs.[{0}]", kpiMeasureName)))
{
//ToDo: Verify with the user if wants to update KPI or exit
//If user wants to update then remove KPI from mdxScripts and continue with the creating the KPI
//
// Until the code to remove KPI is finished we'll have to exit with a message of no duplicated KPIs are allowed
MessageBox.Show(String.Format("Another KPI exists for the same measeure."), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
StringBuilder kpiCommand = new StringBuilder();
AMO.MdxScript mdxScript = modelCube.MdxScripts["MdxScript"];
kpiCommand.Append(mdxScript.Commands[1].Text);
string goalExpression;
if (staticTargetKPI.Checked)
{//Static KPI Goal selected
goalExpression = KPIGoal.ToString();
}
else
{//Measure KPI Goal selected
string measureGoalMeasureName = ((DataRowView)KpiTargetMeasures.CheckedItems[0])[1].ToString();
goalExpression = string.Format("[Measures].[{0}]", measureGoalMeasureName);
}
kpiCommand.AppendLine(string.Format("CREATE MEMBER CURRENTCUBE.Measures.[_{1} Goal] AS '{2}', ASSOCIATED_MEASURE_GROUP = '{0}';"
, kpiTableName, kpiMeasureName, goalExpression));
string statusExpression;
if (staticTargetKPI.Checked)
{//Static KPI Goal selected
statusExpression = string.Format("KpiValue(\"{0}\")", kpiMeasureName).Trim();
}
else
{//Measure KPI Goal selected
string measureGoalMeasureName = ((DataRowView)KpiTargetMeasures.CheckedItems[0])[1].ToString().Trim();
string M = string.Format("[Measures].[{0}]", kpiMeasureName);
string T = string.Format("[Measures].[{0}]", measureGoalMeasureName);
if (KpiRelationDifference.Checked)
{
statusExpression = string.Format("{1} - {0}", M, T);
}
else
{
if (KpiRelationRatioMT.Checked)
{
statusExpression = string.Format("{0} / {1}", M, T);
}
else
{
statusExpression = string.Format("{1} / {0}", M, T);
}
}
}
kpiCommand.AppendLine(string.Format("CREATE MEMBER CURRENTCUBE.Measures.[_{1} Status] "
+ " AS 'Case When IsEmpty({9}) Then Null "
+ " When ({9}) {2} {3} Then {4} "
+ " When ({9}) {5} {6} Then {7} "
+ " Else {8} End'"
+ ", ASSOCIATED_MEASURE_GROUP = '{0}';"
, kpiTableName, kpiMeasureName // 0, 1
, statusThreshold1ComparisonOperator.Text, status1ThresholdValue, redAreaValue // 2, 3, 4
, statusThreshold2ComparisonOperator.Text, status2ThresholdValue, yellowAreaValue, greenAreaValue // 5, 6, 7, 8
, statusExpression // 9
));
kpiCommand.AppendLine(string.Format("CREATE MEMBER CURRENTCUBE.Measures.[_{1} Trend] AS '0', ASSOCIATED_MEASURE_GROUP = '{0}';"
, kpiTableName, kpiMeasureName));
kpiCommand.AppendLine(string.Format("CREATE KPI CURRENTCUBE.[{1}] AS Measures.[{1}]"
+ ", ASSOCIATED_MEASURE_GROUP = '{0}'"
+ ", GOAL = Measures.[_{1} Goal]"
+ ", STATUS = Measures.[_{1} Status]"
+ ", TREND = Measures.[_{1} Trend]"
+ ", STATUS_GRAPHIC = '{2}'"
+ ", TREND_GRAPHIC = '{2}';"
, kpiTableName, kpiMeasureName, clientStatusGraphicImageName));
{//Adding Calculation Reference for the Measure itself
if (!mdxScript.CalculationProperties.Contains(kpiMeasureName))
{
AMO.CalculationProperty cp = new AMO.CalculationProperty(kpiMeasureName, AMO.CalculationType.Member);
cp.FormatString = ""; // ToDo: Get formatting attributes for the member
cp.Visible = true;
mdxScript.CalculationProperties.Add(cp);
}
}
{//Adding Calculation Reference for the Goal measure
AMO.CalculationProperty cp = new AMO.CalculationProperty(string.Format("Measures.[_{0} Goal]", kpiMeasureName), AMO.CalculationType.Member);
cp.FormatString = ""; // ToDo: Get formatting attributes for the member
cp.Visible = false;
mdxScript.CalculationProperties.Add(cp);
}
{//Adding Calculation Reference for the Status measure
AMO.CalculationProperty cp = new AMO.CalculationProperty(string.Format("Measures.[_{0} Status]", kpiMeasureName), AMO.CalculationType.Member);
cp.FormatString = ""; // ToDo: Get formatting attributes for the member
cp.Visible = false;
mdxScript.CalculationProperties.Add(cp);
}
{//Adding Calculation Reference for the Status measure
AMO.CalculationProperty cp = new AMO.CalculationProperty(string.Format("Measures.[_{0} Trend]", kpiMeasureName), AMO.CalculationType.Member);
cp.FormatString = ""; // ToDo: Get formatting attributes for the member
cp.Visible = false;
mdxScript.CalculationProperties.Add(cp);
}
{//Adding Calculation Reference for the KPI
AMO.CalculationProperty cp = new AMO.CalculationProperty(string.Format("KPIs.[{0}]", kpiMeasureName), AMO.CalculationType.Member);
cp.FormatString = ""; // ToDo: Get formatting attributes for the member
cp.Visible = true;
mdxScript.CalculationProperties.Add(cp);
}
try
{
newDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
MessageBox.Show(String.Format("KPI successfully defined."), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (AMO.OperationException amoOperationException)
{
//ToDo: remove anything left in mdxScript up to the point where the exception was thrown
MessageBox.Show(String.Format("Error creating KPI for Measure '{0}'[{1}]\nError message: {2}", kpiTableName, kpiMeasureName, amoOperationException.Message), "AMO to Tabular message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
AMO2Tabular-Beispiel
Um einen Einblick in die Verwendung von AMO zur Erstellung und Bearbeitung von KPI-Darstellungen zu gewinnen, können Sie den Quellcode im AMO2Tabular-Beispiel einsehen; prüfen Sie insbesondere die Quelldatei: AddKPIs.cs. Das Beispiel ist auf Codeplex verfügbar. Ein wichtiger Hinweis zum Code: Der Code wird nur zur Verdeutlichung für die logischen Konzepte bereitgestellt, die hier erläutert werden, und sollte nicht in einer Produktionsumgebung verwendet oder zu anderen als Lehrzwecken eingesetzt werden.