Export (0) Print
Expand All

Key Performance Indicator Representation (Tabular)

A KPI is used to gauge performance of a value, defined by a Base measure, against a Target value

In tabular object models a key performance indicator –kpi- is a measure with additional information for the client application to display it graphically. A kpi usually has information about a goal to be obtained, the status of the measure compared to a the goal, and information for the client tool on how to display graphically the status.

Key Performance Indicator in AMO

When using AMO to manage a Tabular Model kpi there is no one-to-one object match for a kpi in AMO, the AMO Kpiobject is not used for this purpose; in AMO, for tabular models, a kpi is represented the by a series of objects created in one of the elements in the Commands collection and in the CalculationProperties.

The following code snippet shows how to create one of many possible kpi definitions.

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

        }

To have an understanding on how to use AMO to create and manipulate Key Performance Indicator representations see the source code of the AMO to Tabular sample; specifically check in the following source file: AddKPIs.cs. The sample is available at Codeplex. An important note about the code: the code is provided only as a support to the logical concepts explained here and should not be used in a production environment; nor should it be used for other purpose other than the pedagogical one.

Community Additions

ADD
Show:
© 2014 Microsoft