Walkthrough: Creating a Cube
In this walkthrough, you will create a cube that will enable you to analyze customer invoice data from Microsoft Dynamics AX. You will use the Business Intelligence (BI) properties in Microsoft Dynamics AX to specify measures and dimensions for the cube, and then generate a BI project so that you can work with the cube in SQL Server Business Intelligence Development Studio (BIDS).
This walkthrough includes the following tasks:
-
Creating a perspective for a cube
-
Defining measures and dimensions on the tables
-
Generating a BI project
-
Deploying a cube in a BI project
-
Browsing cube data
-
Creating the master company reporting currency dimension
To complete this walkthrough, you will need:
-
Microsoft Dynamics AX with sample data
Note This walkthrough uses the following tables: AddressCounty, AddressState, CustInvoiceJour, CustInvoiceTrans, CustGroup, CustTable, InventTable and InventItemGroup. In order to browse data in the cube, these tables must be populated with data.
-
Microsoft SQL Server 2005
-
SQL Server Business Intelligence Development Studio
-
Completion of setup steps for BI (see "Business intelligence and reporting setup" in the System and Application Setup documentation)
A cube is defined by its measures and dimensions. You can specify measures and dimensions at different levels and on different objects in the Application Object Tree (AOT). A perspective is used to identify the tables that contain the measures and dimensions for a cube.
To create a perspective for the cube
-
In the AOT, expand the Data Dictionary node.
-
Right-click the Perspectives node, and then click New Perspective.
-
Select the node for the perspective.
-
In the Properties sheet, specify the following property values.
Property
Value
Name
SalesAnalysis
Label
Sales Analysis
Usage
OLAP
The Usage property determines how the perspective will be used. Setting the Usage property to OLAP indicates that the perspective will be used to generate a BI project for SQL Server Analysis Services (SSAS).
To add tables to the perspective
-
In the AOT, right-click the Data Dictionary node, and then click Open New Window.
-
In the new window, expand the Tables node.
-
Drag the following tables onto the Tables node for the SalesAnalysis perspective.
-
AddressCounty
-
AddressState
-
CustGroup
-
CustInvoiceJour
-
CustInvoiceTrans
-
CustTable
-
InventItemGroup
-
InventTable
-
-
In the AOT, right-click the SalesAnalysis perspective, and then click Save.
Next, you will specify the measures and dimensions for the cube. To do this, you need to set BI properties on each table included in the SalesAnalysis perspective.
To set BI properties on the AddressCounty table
-
In the AOT, expand the node for the SalesAnalysis perspective, and then expand the Tables node.
-
Select the AddressCounty table.
-
In the Properties sheet, specify the following values.
Property
Value
IsLookup
Yes
AnalysisIdentifier
Name
-
In the AOT, expand the node for the AddressCounty table, expand the Fields node, and then select the Name field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
County
AnalysisUsage
Attribute
To set BI properties on the AddressState table
-
In the AOT, select the AddressState table in the SalesAnalysis perspective.
-
In the Properties sheet, specify the following values.
Property
Value
IsLookup
Yes
AnalysisIdentifier
Name
-
In the AOT, expand the node for the AddressState table, expand the Fields node, and then select the Name field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
State
AnalysisUsage
Attribute
To set BI properties on the CustGroup table
-
In the AOT, select the CustGroup table in the SalesAnalysis perspective.
-
In the Properties sheet, specify the following values.
Property
Value
SingularLabel
Customer group
IsLookup
Yes
AnalysisIdentifier
Name
-
In the AOT, expand the node for the CustGroup table, expand the Fields node, and then select the Name field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
Name
AnalysisUsage
Attribute
To set BI properties on the CustInvoiceJour table
-
In the AOT, select the CustInvoiceJour table in the SalesAnalysis perspective.
-
In the Properties sheet, specify the following values.
Property
Value
SingularLabel
Customer invoice
IsLookup
No
AnalysisDimensionType
Transaction
-
In the AOT, expand the node for the CustInvoiceJour table, expand the Fields node, and then select the DueDate field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
Due date
AnalysisUsage
Attribute
-
In the AOT, select the InvoiceAmount field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisUsage
Measure
AnalysisDefaultTotal
Sum
To set BI properties on the CustInvoiceTrans table
-
In the AOT, select the node for the CustInvoiceTrans table in the SalesAnalysis perspective.
-
In the Properties sheet, specify the following values.
Property
Value
SingularLabel
Customer invoice transaction
IsLookup
No
AnalysisDimensionType
Transaction
-
In the AOT, expand the node for the CustInvoiceTrans table, expand the Fields node, and then select the InvoiceDate field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
Invoice date
AnalysisUsage
Attribute
-
In the AOT, select the LineAmount field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
Revenue
AnalysisUsage
Measure
AnalysisDefaultTotal
Sum
-
In the AOT, select the Qty field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
Quantity
AnalysisUsage
Measure
AnalysisDefaultTotal
Sum
-
In the AOT, select the Remain field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
Remaining units
AnalysisUsage
Measure
AnalysisDefaultTotal
Sum
To set BI properties on the CustTable table
-
In the AOT, select the CustTable table in the SalesAnalysis perspective.
-
In the Properties sheet, specify the following values.
Property
Value
IsLookup
No
AnalysisIdentifier
Name
-
In the AOT, expand the node for the CustTable table, expand the Fields node, and then select the Name field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
Customer
AnalysisUsage
Attribute
To set BI properties on the InventItemGroup table
-
In the AOT, select the node for the InventItemGroup table in the SalesAnalysis perspective.
-
In the Properties sheet, specify the following values.
Property
Value
IsLookup
Yes
AnalysisIdentifier
Name
-
In the AOT, expand the node for the InventItemGroup table, expand the Fields node, and then select the Name field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
Item group
AnalysisUsage
Attribute
To set BI properties on the InventTable table
-
In the AOT, select the InventTable table in the SalesAnalysis perspective.
-
In the Properties sheet, specify the following values.
Property
Value
IsLookup
No
AnalysisIdentifier
ItemName
-
In the AOT, expand the node for the InventTable table, expand the Fields node, and then select the ItemName field.
-
In the Properties sheet, specify the following values.
Property
Value
AnalysisLabel
Item
AnalysisUsage
Attribute
Now that you have created a perspective and specified the measures and dimensions for the cube, you will generate a BI project so that you can work with the cube in BIDS. After generating the BI project, you will view several of the cube objects that were generated.
To generate a BI project
-
On the Microsoft Dynamics AX menu, point to Tools, point to Business Intelligence (BI) tools, and then click BI project generation options. The BI project generation options form displays.
-
Click the General tab.
-
In the Datasource type field, specify the type of database you are using.
-
Select the Enable logging check box, and then specify a path and file name for the log file.
-
Click the Time Dimensions tab.
-
Select the Use the standard calendar check box, and specify start and end dates that are appropriate for the data that you are analyzing.
Note The time interval that you specify for the time dimension should align with existing or expected data for the tables that are used in the cube.
-
Select the following check boxes in the Time periods list for the standard calendar.
-
Days
-
Year
-
Quarter
-
Month
-
-
Click the Translations tab.
-
Select the Create metadata translations check box, and then select the check box next to the following languages.
-
English (United States)
-
French (Standard)
-
-
Click the Generate BI project button. The Generate a Business Intelligence project form is displayed.
-
In the Folder field, specify a location for the project. You can click the folder icon to browse and select a folder.
-
In the Project name field, type SalesAnalysis.
-
Select the Open generated project check box. This indicates that the project is to be opened in BIDS after it is generated.
Note For this walkthrough, it is assumed that Microsoft Dynamics AX and BIDS are installed on the same computer.
-
Select the SalesAnalysis perspective. Be sure that this is the only perspective selected.
-
Click OK. This generates a BI project and opens the BI project in BIDS.
To view cube objects in the generated project
-
In Visual Studio, open Solution Explorer.
-
Expand the Data Sources node. A data source that connects to the Microsoft Dynamics AX OLTP database is displayed.
A data source is used to source and refresh cube data.
Note You should verify that the OLTP connection is valid.
-
Expand the Data Source Views node, and then double-click SalesAnalysis.
A data source view provides a unified view of the tables and their relationships.
-
In Solution Explorer, expand the Cubes node, and then double-click SalesAnalysis.cube to display Cube Designer.
Cube Designer lets you view and edit various properties of a cube. There are several tabs that display different views of the cube. For example, click the Dimension Usage tab to display the mappings between dimensions and measure groups. Click the Translations tab to view the translations that exist for the cube. The SalesAnalysis cube has two translations, English (United States) and French (France).
-
In Solution Explorer, expand the Dimensions node to view the dimensions for the cube.
-
Double-click the Customers dimension.
The Customers dimension consists of attributes from several tables. These include CustTable, CustGroup, AddressState, and AddressCounty. Hierarchies were created based on the relationships between these tables.
-
In Solution Explorer, double-click the Items dimension.
The Items dimension consists of attributes from several tables. These include InventTable, InventItemGroup, and AddressState. Hierarchies were created based on the relationships between these tables.
-
In Solution Explorer, double-click the Time dimension.
The Time dimension includes attributes for all possible levels. The hierarchies that are in the Time dimension depend on the hierarchy levels that were selected when specifying project generation options in Microsoft Dynamics AX.
-
Review the remaining dimensions for the cube.
The Company, Cost Center, Department, and Purpose system dimensions are automatically added to the generated cube.
Next, you will deploy the cube the BI project. During deployment, cube objects are materialized and processed in an instance of SQL Server Analysis Services. When a cube is processed, data from the data source is extracted and mapped into the cube objects.
To deploy the cube
-
In Solution Explorer, right-click the SalesAnalysis project, and then click Deploy.
Now that the SalesAnalysis cube has been deployed and processed, you can browse the cube data in the BI project. The following procedure explains how to browse the cube data.
To browse the cube data
-
In Visual Studio, open the BI project that you want to browse.
-
In Solution Explorer, double-click SalesAnalysis.cube.
-
Click the Browser tab.
-
Expand the Measures node, expand Customer invoice node, right-click Invoice amount, and then click Add to Data Area.
-
Expand the Time node, right-click the Year - Quarter - Month - Days hierarchy, and then click Add to Column Area.
-
Expand the Customers - Invoice account node, right-click the Customers - Invoice account.Customer groups - Customers hierarchy node, and then click Add to Row Area.
-
Browse the data. You can expand and collapse rows and columns in the table. You can modify the rows and columns that display in the table or add other dimensions to further slice the data.
In order to display the KPIs for a cube in a Business Overview Web part in Enterprise Portal, the cube must contain a Master Company Reporting Currency dimension. This dimension facilitates the reporting of financial measures in all the currencies used by the companies implemented in Microsoft Dynamics AX. In Microsoft Dynamics AX 2009, this dimension is not automatically created for you when you generate a BI project for a cube. You must manually create this dimension.
To create a mapping for the Master Company Exchange Rate
-
In Cube Designer, click the Dimension Usage tab.
-
Click the ellipsis button (…) that appears at the intersection of the Time dimension and the Master company exchange rate measure group. The Define Relationship dialog box is displayed.
-
For the Select relationship type field, select Regular.
-
For the Granularity attribute field, select Days.
-
In the relationship table, select DATEKEY in the Measure Group Columns column.
-
Click OK.
To create the Master Company Reporting Currency named query
-
In Solution Explorer, double-click SalesAnalysis located in the Data Source Views folder.
-
Click the New Named Query button.
-
In the Name field, type Master Company Reporting Currency.
-
Replace the empty SQL query with the following query:
SELECT RC.CURRENCYCODE, RC.ISOCURRENCYCODE, RC.CURRENCYNAME, RC.SYMBOL, DA.ISPIVOT FROM (SELECT CURRENCYCODE, ISOCURRENCYCODE, CURRENCYNAME, SYMBOL FROM (SELECT CURRENCYCODE, CURRENCYCODEISO AS ISOCURRENCYCODE, TXT AS CURRENCYNAME, SYMBOL FROM CURRENCY AS A) AS BICURRENCYDIMENSION WHERE (CURRENCYCODE IN (SELECT CASE WHEN UPPER(CURRENCYCODE) IS NULL THEN '' ELSE UPPER(CURRENCYCODE) END AS CURRENCYCODE FROM COMPANYINFO))) AS RC INNER JOIN (SELECT D.ID, D.NAME, D.ISVIRTUAL, (CASE WHEN C.CURRENCYCODE IS NULL THEN '' ELSE UPPER(C.CURRENCYCODE) END) AS CURRENCYCODE, (CASE WHEN C.SECONDARYCURRENCYCODE IS NULL THEN '' ELSE UPPER(C.SECONDARYCURRENCYCODE) END) AS SECONDARYCURRENCYCODE, (CASE WHEN (SELECT TOP 1 UPPER(EXCHANGERATECOMPANY) AS EXPR1 FROM [DBO].BICONFIGURATION AS B) = UPPER(D .ID) THEN 0 ELSE 1 END) AS ISPIVOT FROM DATAAREA AS D LEFT OUTER JOIN COMPANYINFO AS C ON C.DATAAREAID = D.ID) AS DA ON RC.CURRENCYCODE = DA.CURRENCYCODE UNION SELECT DISTINCT N'Local' AS [Local 1], N'Local' AS Local, N'Local' AS [Local 3], N'Local' AS [Local 2], 1 AS [Local 4] FROM (SELECT CURRENCYCODE, CURRENCYCODEISO AS ISOCURRENCYCODE, TXT AS CURRENCYNAME FROM CURRENCY AS A) AS BICURRENCYDIMENSION_1 -
Click OK.
To add the Master Company Reporting Currency dimension to the cube
-
In Solution Explorer, right-click Dimension and then click New Dimension. Click Next.
-
Use the Dimension Wizard to add a dimension called Master Company Reporting Currency.
Note The options that you select depend on which version of BIDS you use.
-
In Solution Explorer, double-click Master Company Reporting Currency.dim located in the Dimensions folder.
-
Click the Dimension Structure tab if it is not already displayed.
-
In the Attributes pane, select the Master Company Reporting Currency dimension.
-
Set the ErrorConfiguration property to (custom).
-
Expand the ErrorConfiguration node, and then set the KeyDuplicate, KeyNotFound, and NullKeyNotAllowed properties to IgnoreError.
-
Set the UnknownMember property to Visible, and then save your changes.
To add the Master Company Reporting Currency dimension
-
In Solution Explorer, double-click SalesAnalysis.cube.
-
Click the Dimension Usage tab.
-
On the Dimension Usage toolbar, click Add Cube Dimension.
-
Select Master Company Reporting Currency, and then click OK.
To deploy the cube
-
In Solution Explorer, right-click the SalesAnalysis project, and then click Deploy.
Once you have defined a cube and created a BI project, you are ready to add features to the cube in BIDS. For more information, see Walkthrough: Defining KPIs for a Cube. You can also view the cube data in Microsoft Office Excel. For more information, see Walkthrough: Analyzing Cube Data in Excel.