Walkthrough: Customizing a Default Cube
Microsoft Dynamics AX has default cubes included that you can customize. In this walkthrough, you will customize the Customer Relationship Management Cube by modifying the Status expression in the Sales Key Performance Indicator (KPI) and adding a KPI that reports the average revenue earned for each sale. These KPIs can be displayed from a role center in the Microsoft Dynamics AX client or Enterprise Portal.
The Customer Relationship Management cube can be modified in SQL Server Business Intelligence Development Studio (BIDS). To edit the cube in BIDS, you must generate a BI project from the Dynamics AX analysis services database.
To generate a BI project from the Dynamics AX Analysis Services Database
-
Click All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio.
-
On the File menu, point to New and then click Project. The New Project dialog box displays.
-
In the Project types pane, select Business Intelligence Projects, and in the Templates pane, select Import Analysis Services 9.0 Database.
-
In the Name box, type Dynamics AX, and in the Location box, type a location.
-
Click OK. This launches Import Analysis Services 9.0 Database Wizard.
-
On the Welcome page, click Next.
-
On the Source database page, type the name of the server that contains the Analysis Services database for Microsoft Dynamics AX, select the database from the drop-down menu, and then click Next.
-
Click Finish.
You can modify an existing KPI to display custom information.
To customize an existing KPI
-
In Solution Explorer, expand the Cubes node and then double-click Customer Relationship Management Cube.
-
Click the KPIs tab.
-
On the toolbar for the KPIs tab, click Form View if you are currently in Script View.
-
Click the Sales KPI.
-
For the Status Expression field, type the following expression.
Case When KpiValue("Sales") / KpiGoal("Sales") >= .80 Then 1 When KpiValue("Sales") / KpiGoal("Sales") < .80 And KpiValue("Sales") / KpiGoal("Sales") >= .60 Then 0 Else-1 EndThis expression provides a basis from which to evaluate progress toward meeting a goal. The status graphic that displays is based on the value the expression returns. The new expression will display the Guage status indicator with its hand
-
in the green region if the KPI value is within 80 percent of reaching the sales goal
-
in the yellow region if the KPI value is between 60 percent and 80 percent of the sales goal
-
in the red region if the KPI value is less than 60 percent of the sales goal
-
-
On the toolbar for the KPIs tab, click Browser View to see the KPI.
You can add new KPIs to default cubes.
In the following procedure, you will create a KPI that reports the average revenue per sale.
To add a new KPI
-
In Solution Explorer, expand the Cubes node and then double-click Customer Relationship Management Cube.
-
Click the KPIs tab.
-
On the toolbar for the KPIs tab, click Form View if you are currently in Script View.
-
Click New KPI A form displays that allows you to define the KPI.
-
For the Name filed, type Average Revenue per Sale.
-
For the Value Expression field, type the following expression.
[Measures].[Invoice revenue]/[Measures].[Sales count]
This expression divides the total invoice revenue by the number of sales.
-
For the Goal Expression field, type the following.
17000
This number represents the goal for the average revenue per sale.
-
For the Status Expression field, type the following expression.
Case When KpiValue("Average Revenue per Sale") / KpiGoal("Average Revenue per Sale") >= 1 Then 1 When KpiValue("Average Revenue per Sale") / KpiGoal("Average Revenue per Sale") < 1 And KpiValue("Average Revenue per Sale") / KpiGoal("Average Revenue per Sale") >= .80 Then 0 Else-1 EndThis expression provides a basis from which to evaluate progress toward meeting a goal. The status graphic that displays is based on the value the expression returns. The new expression will display the Guage status indicator with its hand
-
in the green region if the KPI value reaches the revenue per sale goal
-
in the yellow region if the KPI value is within 80 percent of reaching the goal
-
in the red region if the KPI value is less than 80 percent of the revenue per sale goal
-
-
For the Trend indicator field, select the Standard arrow from the drop-down list.
-
For the Trend Expression field, type the following expression.
Case When KpiValue("Average Revenue per Sale") < (KpiValue("Average Revenue per Sale"),[Time].[Years Quarters Months Weeks Days].PREVMEMBER) Then -1 When KpiValue("Average Revenue per Sale") >= 1.05 * (KpiValue("Average Revenue per Sale"),[Time].[Years Quarters Months Weeks Days].PREVMEMBER) Then 1 Else 0 EndThis expression provides a basis to evaluate the trend toward achieving the goal. The graphic that displays for the trend of the KPI depends upon what value this expression evaluates to.
-
On the Build menu, click Deploy Dynamics AX.
-
On the toolbar for the KPIs tab, click Browser View to see the KPI.
After you deploy the project, you can display the KPI data in a Business Overview Web part in a role center page.
![]() |
|---|
|
A Business Overview Web part only needs one ODC file to point to the OLAP database. A MOSS KPI List Web part needs individual ODC files for each cube. |
To display the KPIs in a Business Overview Web part
-
Navigate to the CEO role center that is located at http://<server>/sites/DynamicsAx/Enterprise%20Portal/RoleCenterCEO.aspx.
-
In the Customer KPIs web part, click Add KPIs.
-
In the Business Overview - Add KPI dialog box, select Customer Relationship Management Cube, select the Sales KPI, select Amount for the Display value as field, and then click OK.
-
In the Customer KPIs web part, click Add KPIs.
-
In the Business Overview - Add KPI dialog box, select Customer Relationship Management Cube, select the Average Revenue per Sale KPI, select Amount for the Display value as field, and then click OK.
