7.1 define KPIs for key indicators

Define key indicators KPI:

To define a key performance indicator (KPI) in Microsoft SQL Server 2005, you must first define the KPI name and measurement value group associated with the KPI. KPIs can be associated with all metric value groups or a single metric value group. Then define the following KPI elements:

• Value expression

  A value expression is a calculation defined in a KPI by a physical metric value (such as sales), a calculated metric value (such as profit), or a multidimensional expression (MDX.

• Target expression

  The target expression is a value or an MDX expression parsed as a value. It is used to define the target of the measurement value defined by the value expression. For example, the target expression can be the amount of sales or profits that the company business manager wants to increase.

• Status expression

  The status expression is an MDX expression, which is used by Analysis Services to calculate the current status of a value expression. The normal value range is-1 to + 1. -1 indicates very poor, while + 1 indicates very good. The status expression is displayed in graphs to help you easily determine the status of the value expression compared with the target expression.

• Direction expression

  The direction expression is an MDX expression, which is used by Analysis Services to calculate the current direction of the value expression compared with the target expression. A forward expression helps business users quickly determine whether the value expression is becoming better or worse than the target expression. You can associate one of several graphs with a trend expression to help business users quickly understand the trend.

In addition to defining these elements for KPIs, you also need to define several attributes for KPIs. These attributes include the display folder, parent KPI (if calculated from other KPIs), current time member (if any), KPI weight (if any), and KPI description.

For more examples of KPIs, see the KPI example in the "templates" tab in the "computing tools" pane or the "Adventure works DW" Sample Data Warehouse example. For more information, see .

 

In this topic task, you define two KPIs in the analysis services Tutorial Project, and then use these KPIs to browse the Analysis Services tutorial multi-dimensional dataset. The KPIs to be defined are as follows:

• Distributor revenue

  This KPI is used to measure how to compare the actual distributor sales with the distributor sales, the distance between the sales and the target, and how to achieve the target.

• Gross profit margin

  This KPI is used to determine the similarity between the gross profit margin of each product category and the specific target of each product, and to determine the trend to achieve this goal.

Define "distributor revenue" KPI
Define "distributor revenue" KPI

2. Open the multidimensional cube designer for the Analysis Services tutorial cube, and then clickKPITab.

   KPIThe tab consists of several tabs. On the left side of the tab"KPI OrganizationProgram"Pane and"Computing tools"Pane. The display pane in the middle of the tab contains"KPI organization program"The details of the selected KPI In the pane.

   ShowsKPITab.

4. InKPIOn the toolbar of the tab, click"Create KPI".

   A blank KPI template is displayed in the display pane, as shown in.

6. In"Name"Enter"Distributor revenue"And then click"Associated metric group"In the list"Distributor sales".

8. In"Computing tools"In the pane"Metadata"Tab, expand"Metric value", And then expand"Distributor revenue", And then"Distributor sales"Drag the measurement value"Value expression"Box.

10. In"Computing tools"In the pane"Metadata"Tab, expand"Metric value", And then expand"Sales quota", And then"Sales quota"Drag the measurement value"Target expression"Box.

11. Verify whether"Status indicator"Select from the list"Measurement"And then"Status expression"Enter the following MDX expression in the box:

    	Copy code
    Case when kpivalue ("reseller revenue")/kpigoal ("reseller revenue")> =. 95 then 1 when kpivalue ("reseller revenue")/kpigoal ("reseller revenue") <. 95 and kpivalue ("reseller revenue")/kpigoal ("reseller revenue")> =. 85 then 0 else-1 end

    This MDX expression provides basic information for the completion progress of the computing target.Algorithm. In this MDX expression, if the actual distributor sales exceed 85% of the target, the value 0 is used to fill the selected image. Since the measurement is the selected graph, the pointer in the measurement is located between null and full. If the actual distributor sales exceed 90%, the measurement pointer is located at 3/4 between the null and the full.

12. Verify whether"Direction indicator"Selected in the list"Standard arrow"And then"Direction expression"Enter the following expression in the dialog box:

    Copy code

    Case when isempty (parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember) then 0 when (kpivalue ("reseller revenue")-(kpivalue ("reseller revenue"), parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember)/(kpivalue ("reseller revenue"), parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember)> =. 02 then 1 when (kpivalue ("reseller revenue")-(kpivalue ("reseller revenue"), parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember)/(kpivalue ("reseller revenue"), parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember) <=. 02 then-1 else 0 end

    This MDX expression provides a basic algorithm for calculating the completion trend of a predetermined target.

Use "distributor revenue" KPI to browse Multi-Dimensional Datasets
Use "distributor revenue" KPI to browse Multi-Dimensional Datasets

2. In business intelligence development studio"Generate"Click"Deployment Analysis Services tutorial".

4. After the deployment is completed, goKPIClick"Browser View"And then click"Reconnect".

   Status and trend measurements are based on the default Member values of each dimension, together with the value and target value, displayed in the distributor's sales"KPI Browser"Pane. Because no other members of any dimension have been defined as default members, the default members of each dimension are "all" members at the "all" level.

   ShownKPITab"Browser View"In"KPI Browser"KPIs In the pane.

6. In"Filter"In the pane, select"Dimension"In the list"Sales region","Hierarchy"In the list"Sales region","Operator"In the list"Equal"And"Filter expression"In the listNorth AmericaAnd then click"OK".

8. In"Filter"In the pane, select"Dimension"In the list"Date","Hierarchy"In the list"Calendar time","Operator"In the list"Equal"And"Filter expression"In the listQ3 CY 2003And then click"OK".

10. Click"KPI Browser"Any position in the pane to update"Distributor revenue KPI".

    Note: KPI"Value","Purpose"And"Status"Partially reflects the value of the new time period, as shown in.

Define "product gross profit rate KPI"
Define "total gross profit rate KPI"

2. InKPIClick"Form View"And then click"Create KPI".

4. In"Name"Enter"Gross profit margin of Products", Verify again"<All>"Whether it has exists"Associated metric group"List.

6. In"Computing tools"In"Metadata"Tab"Total GPM"Drag the measurement value"Value expression"Box.

7. In"Target expression"Enter the following expression:

   	Copy code
   Case when [product]. [category]. currentmember is [product]. [category]. [accessories] then. 40 when [product]. [category]. currentmember is [product]. [category]. [bikes] then. 12 when [product]. [category]. currentmember is [product]. [category]. [clothing] then. 20 when [product]. [category]. currentmember is [product]. [category]. [components] then. 10 Else. 12 End

9. In"Status indicator"List, select"Column".

10. In"Status expression"Enter the following MDX expression in the box:

    	Copy code
    Case when kpivalue ("Product gross profit margin")/kpigoal ("Product gross profit margin")> =. 90 then 1 when kpivalue ("Product gross profit margin")/kpigoal ("Product gross profit margin") <. 90 and kpivalue ("Product gross profit margin")/kpigoal ("Product gross profit margin")> =. 80 then 0 else-1 end

    This MDX expression provides the basic algorithm for the completion progress of the computing target.

11. Verify whether"Direction indicator"Selected in the list"Standard arrow"And then"Direction expression"Enter the following MDX expression in the box:

    Copy code

    Case when isempty (parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember) then 0 when VBA! ABS (kpivalue ("Product gross profit margin")-(kpivalue ("Product gross profit margin"), parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember)/(kpivalue ("Product gross profit margin"), parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember) <=. 02 then 0 when kpivalue ("Product gross profit margin")-(kpivalue ("Product gross profit margin"), parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember)/(kpivalue ("Product gross profit margin"), parallelperiod ([date]. [calendar time]. [calendar year], 1, [date]. [calendar time]. currentmember)>. 02 then 1 else-1 end

    This MDX expression provides a basic algorithm for calculating the completion trend of a predetermined target.

Use "total gross margin KPI" to browse Multi-Dimensional Datasets
Use "total gross margin KPI" to browse Multi-Dimensional Datasets

2. In"Generate"Click"Deployment Analysis Services tutorial".

4. After the deployment is completedKPIClick"Reconnect"And then click"Browser View".

   The"Total gross profit rate KPI"And displayQ3 CY 2003AndNorth AmericaKPI value of the sales region.

6. In"Filter"In the pane, select"Dimension"In the list"Product","Hierarchy"In the list"Category","Operator"In the list"Equal"And"Filter expression"In the list"Bicycle"And then click"OK".

   The gross profit margin of North America distributors in Q3 CY 2003 in bicycle sales appeared, as shown in.

Download and install the updated example to obtain the complete project used in Lesson 7th. For more information, see To obtain the updated example ".