Walkthrough: Defining KPIs for a Cube
A Key Performance Indicator (KPI) is a business metric that can be displayed in places like reports and role center pages. For more information, see Cube Overview.
This walkthrough includes the following tasks:
-
Defining KPIs for a cube
To complete this walkthrough, you will need:
-
Microsoft Dynamics AX with sample data
Note The expressions in this walkthrough are specific to the Contoso sample data. If you are using different data, you may need to modify the expressions to align with your data.
-
The SalesAnalysis cube created in Walkthrough: Creating a Cube.
-
Microsoft SQL Server 2005
-
SQL Server Business Intelligence Studio (BIDS)
-
Analysis Services extensions for Microsoft Dynamics AX
To define a KPI for internet sales:
-
Create a calculated member for internet items sold during the previous month
-
Create a KPI for items sold via the internet
To create a calculated member for internet items sold during the previous month
-
Open the SalesAnalysis project in BIDS.
-
In Solution Explorer, double-click SalesAnalysis.cube. The cube displays in Cube Designer.
-
In Cube Designer, click the Calculations tab.
-
On the toolbar for the Calculations tab, click Form View if you are currently in Script View.
-
Click New Calculated Member. A form displays that allows you to define the calculated member.
-
In the Name field, type [Internet Items Sold Previous Month].
-
The Parent hierarchy field is set to MEASURES by default. Keep this default setting.
-
For the Expression field, type the following expression.
SUM(StrToMember("[Time].[Years Quarters Months Days]. [Months].[" + Format(Now(),"MMMM yyyy")+"]").PrevMember,([Measures].[Quantity], [Customers - Invoice account].[Customer groups - Customers]. [Customer groups].[Internet Customers]))This expression calculates the number of items sold to internet customer in the previous month. This calculated measure will be used when defining a trend in internet sales.
To create a KPI for items sold via the internet
-
Click the KPIs tab.
-
On the toolbar for the KPIs tab, click New KPI. A form displays that allows you to define the KPI.
-
For the Name field, type Items Sold via Internet.
-
For the Value Expression field, type the following expression.
SUM(YTD(StrToMember("[Time].[Years Quarters Months Days]. [Years].[Calendar " + Format(Now(),"yyyy")+"]")), ([Measures].[Quantity],[Customers - Invoice account]. [Customer groups - Customers].[Customer groups].[Internet Customers]))This expression calculates the number of items sold to internet customers for the current year.
-
For the Goal Expression field, type the following expression.
SUM(YTD(StrToMember("[Time].[Years Quarters Months Days]. [Years].[Calendar " + Format(Now(),"yyyy")+"]").PrevMember), ([Measures].[Quantity],[Customers - Invoice account]. [Customer groups - Customers].[Customer groups].[Internet Customers])) + 3000This expression identifies the goal for the number of items sold to internet customers. The goal is based on the number of items sold to internet customers the previous year with an added 3000 items.
-
For the Status indicator field, select Shapes from the drop-down list.
-
For the Status expression field, type the following expression.
Case When KpiValue("Items Sold via Internet") / KpiGoal("Items Sold via Internet") >= .80 Then 1 When KpiValue("Items Sold via Internet") / KpiGoal("Items Sold via Internet") < .80 And KpiValue("Items Sold via Internet") / KpiGoal("Items Sold via Internet") >= .60 Then 0 Else-1 EndThis expression provides a basis to evaluate progress toward meeting the goal. The graphic that displays for status of the KPI depends upon what value this expression evaluates to.
-
For the Trend indicator field, select Standard arrow from the drop-down list.
-
For the Trend expression field, type the following expression.
Case When [Measures].[Internet Items Sold Previous Month] < (KpiGoal("Items Sold via Internet") / 12) Then -1 When [Measures].[Internet Items Sold Previous Month] >= (KpiGoal("Items Sold via Internet") / 12) Then 1 EndThis expression provides a basis to evaluate the trend toward achieving the goal. This expression compares the internet sales sold in the previous month to the average value that must be maintained in order to reach the goal. The graphic that displays for the trend of the KPI depends upon what value this expression evaluates to.
To view the KPI
-
On the Build menu, click Deploy SalesAnalysis.
-
On the toolbar for the KPIs tab, click Browser View. The KPI displays in the list.
To define a KPI for sales revenue:
-
Create a calculated member for sales revenue for the current month
-
Create a calculated member for sales revenue for the current month of the previous year
-
Create a KPI for sales revenue
To create a calculated member for sales revenue for the current month
-
In Cube Designer, click the Calculations tab.
-
On the toolbar for the Calculations tab, click New Calculated Member. A form displays that allows you to define the calculated member.
-
In the Name field, type [Sales Revenue for Current Month].
-
The Parent hierarchy field is set to MEASURES by default. Keep this default setting.
-
For the Expression field, type the following expression.
Sum(StrToMember("[Time].[Years Quarters Months Days]. [Months].[" + Format(Now(),"MMMM yyyy")+"]"),[Measures].[Invoice amount])This expression calculates the total sales revenue for the current month. This calculated measure will be used when defining a trend in sales revenue.
To create a calculated member for sales revenue for the current month of the previous year
-
On the toolbar for the Calculations tab, click New Calculated Member.
-
In the Name field, type [Previous Year Sales Revenue for Current Month].
-
The Parent hierarchy field is set to MEASURES by default. Keep this default setting.
-
For the Expression field, type the following expression.
Sum(ParallelPeriod([Time].[Years Quarters Months Days]. [Months],12,StrToMember("[Time].[Years Quarters Months Days]. [Months].[" + Format(Now(),"MMMM yyyy")+"]")),[Measures].[Invoice amount])This expression calculates the total sales revenue for the current month in the previous year. This calculated measure will also be used when defining a trend in sales revenue.
To create a KPI for sales revenue
-
Click the KPIs tab.
-
If you are in browser view, click Form View on the KPIs toolbar.
-
Click New KPI.
-
For the Name field, type Sales Revenue.
-
For the Value Expression Field, type the following expression.
Sum(StrToMember("[Time].[Years Quarters Months Days]. [Years].[Calendar " + Format(Now(),"yyyy")+"]"),[Measures].[Invoice amount])This expression calculates the sales revenue so far for the current year.
-
For the Goal Expression field, type the following expression.
Sum(StrToMember("[Time].[Years Quarters Months Days]. [Years].[Calendar " + Format(Now(),"yyyy")+"]").PrevMember, [Measures].[Invoice amount]) * 1.05This expression identifies the goal for sales revenue. The amount is based on the sales revenue for the previous year with an increase of 5%.
-
For the Status indicator field, select Shapes from the drop-down list.
-
For the Status expression field, type the following expression.
Case When KpiValue("Sales Revenue") / KpiGoal("Sales Revenue") >= .95 Then 1 When KpiValue("Sales Revenue") / KpiGoal("Sales Revenue") < .95 And KpiValue("Sales Revenue") / KpiGoal("Sales Revenue") >= .85 Then 0 Else-1 EndThis expression provides a basis to evaluate progress toward meeting the goal. The graphic that displays for status of the KPI depends upon what value this expression evaluates to.
-
For the Trend indicator field, select Standard arrow from the drop-down list.
-
For the Trend expression field, type the following expression.
Case When [Measures].[Sales Revenue for Current Month] < [Measures].[Previous Year Sales Revenue for Current Month] Then -1 When [Measures].[Sales Revenue for Current Month] >= [Measures].[Previous Year Sales Revenue for Current Month] Then 1 EndThis expression provides a basis to evaluate the trend toward achieving the goal. The expression compares the sales revenue from the current month of the current year to that of the same month in the previous year. It uses the calculated measures that were defined earlier in this procedure.
-
On the Build menu, click Deploy SalesAnalysis.
-
On the toolbar for the KPIs tab, click Browser View. The KPI displays in the list.
To view the KPI
-
On the Build menu, click Deploy SalesAnalysis.
-
On the toolbar for the KPIs tab, click Browser View. The KPI displays in the list.
Once you have defined KPIs for a cube, you can display the KPIs in a Business Overview Web part. For more information, see Walkthrough: Displaying KPIs in a Role Center.