CREATING AN OLAP CUBE IN MICROSOFT DYNAMICS AX 2009

OLAP cubes can be managed and modified directly in Microsoft Dynamics AX 2009. This is done via the Perspectives node in the Data Dictionary in the AOT. In addition to the Perspectives node in the AOT, there are properties on other AOT objects that are specific to OLAP configuration.
Open the Properties window for a table. You will see the following properties:
• AnalysisVisibility
• AnalysisSelection
• TypicalRowCount
• IsLookup
• AnalysisDimensionType
• Singular Label
• AnalysisIdentifier

These are all OLAP related properties for the table. Here are further descriptions of some of these properties:

Property	Description
IsLookup	Determines whether to generate a consolidated dimension or a distinct dimension. You can specify one of the following values: es – Indicates that attributes from the table are to be consolidated into the parent dimension (Star schema – see balloon below). o – Indicates that a separate dimension is to be generated for the table (Snowflake schema – see balloon below).
AnalysisIdentifier	Specifies the table field that is referenced as the dimension instance identifier.
AnalysisDimensionType	Determines the type of dimension created based on the IsLookup property setting. You can specify one of the following values: • IsLookup property set to Yes: o Auto – Specifies that the table may contain factual as well as dimensional data. The BI Wizard will extract dimensional data and create dimensions and attributes while factual data will be extracted to create measures. One child dimension is created with attributes from the parent table. o MasterInner – Specifies an inner (full) join to create relationships with this table to the child table. Each record combination for this table and the child table are generated in the dimension.One child dimension is created with attributes from the parent table. o MasterLeftOuter – Specifies a left outer join to create relationships with this table to the child table. Dimensions will have additional attributes based on values in this table that can also be empty. One child dimension is created with attributes from the parent table. o Transaction – Specifies that the table should strictly be used to generate factual data (measures). This setting should be used when a table only contains transactional data. One child dimension is created containing only enumeration fields from the table. • IsLookup property set to No: o Auto – Specifies that the table may contain factual as well as dimensional data. The BI Wizard will extract dimensional data and create dimensions and attributes while factual data will be extracted to create measures. One parent and child dimension is created. o MasterInner – Not applicable. Same as Auto. o MasterLeftOuter – Not applicable. Same as Auto. o Transaction – Specifies that the table should strictly be used to generate factual data (measures). This setting should be used when a table only contains transactional data. One child dimension is created containing only enumeration values from the table.
SingularLabel	Specifies the caption for the dimension generated for the table. If you do not specify a value for the SingularLabel property, the Label property setting is used.

 

As well as tables, OLAP properties are found on table fields. On most table fields, you will see the following properties:

• AnalysisVisibility

• AnalysisTotaling

•AnalysisLabel

•AnalysisDefaultTotal

•AnalysisUsage

These are all OLAP related properties for the table field. Here are further descriptions of some of these properties:

Property	Description
AnalysisLabel	Specifies the label for the field when it is used as a dimension attribute or measure. Only specify a label for this property when the label supplied for the Label property is not appropriate.
AnalysisUsage	Identifies the role of the field in the cube. You can specify one of the following values. Attribute – The field is a dimension attribute. Measure – The field is a measure. Both – The field is both a dimension attribute and a measure. None – The field is not a dimension attribute and not a measure. Auto – The value of the AnalysisUsage property for the extended data type or enumeration that the field is based on is to be used.
AnalysisDefaultTotal	Determines the aggregate function for a measure. Use this property when AnalysisUsage is set to Measure. You can specify one of the following values. Sum – Returns the sum of all the values in a set. Count – Returns the number of non‐null items in a set. CountDistinct – Returns the number of distinct non‐null items in a set. Min – Returns the minimum value in a set. Max – Returns the maximum value in a set. None – No aggregate function is applied. Auto – Applies to derived extended data types. The value of the AnalysisUsage property for the parent extended data type is to be used.

Even deeper than table fields, the same OLAP properties can be defined on all Extended Data Types (except Enumerations). Enumerations only have one OLAP property – AnalysisUsage – which can only be defined as Attribute or None.
and now let’s create our new cube in Dynamics ax 2009 as the following:

1. First a new perspective is needed. In the AOT, navigate to Data Dictionary > Perspectives.

2. Right‐click Perspectives node and select New Perspective.

3. Open the properties window for the new perspective.

4. Give the perspective a Name (SalesAnalysis), Label (SalesAnalysis) and set its Usage to OLAP.

5. Now tables need to be added to the perspective. Open a new AOT window, and navigate to Data Dictionary > Tables node. Drag the following tables onto the perspective:

• AddressCounty
• AddressState
• CustGroup
• CustInvoiceJour
• CustInvoiceTrans
• CustTable
• InventItemGroup
• InventTable

6. Save the perspective.

 

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

1. In the AOT, expand the node for the SalesAnalysis perspective, and then expand the Tables node.

2. Select the AddressCounty table.

3. In the Properties sheet, specify the following values.

Property	Value
IsLookup	Yes
AnalysisIdentifier	Name

 

4. In the AOT, expand the node for the AddressCounty table, expand the Fields node, and then select the Name field.

5. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	County
AnalysisUsage	Attribute

To set BI properties on the AddressState table

1. In the AOT, select the AddressState table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
IsLookup	Yes
AnalysisIdentifier	Name

3. In the AOT, expand the node for the AddressState table, expand the Fields node, and then select the Name field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	State
AnalysisUsage	Attribute

To set BI properties on the CustGroup table

1. In the AOT, select the CustGroup table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
SingularLabel	Customer group
IsLookup	Yes
AnalysisIdentifier	Name

3. In the AOT, expand the node for the CustGroup table, expand the Fields node, and then select the Name field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Name
AnalysisUsage	Attribute

To set BI properties on the CustInvoiceJour table

1. In the AOT, select the CustInvoiceJour table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
SingularLabel	Customer invoice
IsLookup	No
AnalysisDimensionType	Transaction

3. In the AOT, expand the node for the CustInvoiceJour table, expand the Fields node, and then select the DueDate field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Due date
AnalysisUsage	Attribute

5. In the AOT, select the InvoiceAmount field.

6. In the Properties sheet, specify the following values.

Property	Value
AnalysisUsage	Measure
AnalysisDefaultTotal	Sum

To set BI properties on the CustInvoiceTrans table

1. In the AOT, select the node for the CustInvoiceTrans table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
SingularLabel	Customer invoice transaction
IsLookup	No
AnalysisDimensionType	Transaction

3. In the AOT, expand the node for the CustInvoiceTrans table, expand the Fields node, and then select the InvoiceDate field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Invoice date
AnalysisUsage	Attribute

5. In the AOT, select the LineAmount field.

6. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Revenue
AnalysisUsage	Measure
AnalysisDefaultTotal	Sum

7. In the AOT, select the Qty field.

8. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Quantity
AnalysisUsage	Measure
AnalysisDefaultTotal	Sum

9. In the AOT, select the Remain field.

10. 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

1. In the AOT, select the CustTable table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
IsLookup	No
AnalysisIdentifier	Name

3. In the AOT, expand the node for the CustTable table, expand the Fields node, and then select the Name field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Customer
AnalysisUsage	Attribute

To set BI properties on the InventItemGroup table

1. In the AOT, select the node for the InventItemGroup table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
IsLookup	Yes
AnalysisIdentifier	Name

3. In the AOT, expand the node for the InventItemGroup table, expand the Fields node, and then select the Name field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Item group
AnalysisUsage	Attribute

To set BI properties on the InventTable table

1. In the AOT, select the InventTable table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
IsLookup	No
AnalysisIdentifier	ItemName

3. In the AOT, expand the node for the InventTable table, expand the Fields node, and then select the ItemName field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Item
AnalysisUsage	Attribute

Generating a BI Project

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

1. 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.

2. Click the General tab.

3. In the Datasource type field, specify the type of database you are using.

4. Select the Enable logging check box, and then specify a path and file name for the log file.

5. Click the Time Dimensions tab.

6. 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.

7. Select the following check boxes in the Time periods list for the standard calendar.

o Days

o Year

o Quarter

o Month

8. Click the Translations tab.

9. Select the Create metadata translations check box, and then select the check box next to the following languages.

o English (United States)

o French (Standard)

10. Click the Generate BI project button. The Generate a Business Intelligence project form is displayed.

11. In the Folder field, specify a location for the project. You can click the folder icon to browse and select a folder.

12. In the Project name field, type SalesAnalysis.

13. 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.

14. Select the SalesAnalysis perspective. Be sure that this is the only perspective selected.

15. Click OK. This generates a BI project and opens the BI project in BIDS.

To view cube objects in the generated project

1. In Visual Studio, open Solution Explorer.

2. 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

3. 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.

Note: You should verify that the OLTP connection is valid.

4. 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. TheSalesAnalysis cube has two translations, English (United States) and French (France).

5. In Solution Explorer, expand the Dimensions node to view the dimensions for the cube.

6. 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.

7. 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.

8. 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.

9. Review the remaining dimensions for the cube.

The Company, Cost Center, Department, and Purpose system dimensions are automatically added to the generated cube.

Deploying a Cube in a BI Project

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.

Browsing Cube Data

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

1. In Visual Studio, open the BI project that you want to browse.

2. In Solution Explorer, double-click SalesAnalysis.cube.

3. Click the Browser tab.

4. Expand the Measures node, expand Customer invoice node, right-click Invoice amount, and then click Add to Data Area.

5. Expand the Time node, right-click the Year - Quarter - Month - Days hierarchy, and then click Add to Column Area.

6. Expand the Customers - Invoice account node, right-click the Customers - Invoice account.Customer groups - Customers hierarchy node, and then click Add to Row Area.

7. 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.

Creating the Master Company Reporting Currency Dimension

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

1. In Cube Designer, click the Dimension Usage tab.

2. 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.

3. For the Select relationship type field, select Regular.

4. For the Granularity attribute field, select Days.

5. In the relationship table, select DATEKEY in the Measure Group Columns column.

6. Click OK.

To create the Master Company Reporting Currency named query

1. In Solution Explorer, double-click SalesAnalysis located in the Data Source Views folder.

2. Click the New Named Query button.

3. In the Name field, type Master Company Reporting Currency.

4. 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

5. Click OK.

To add the Master Company Reporting Currency dimension to the cube

1. In Solution Explorer, right-click Dimension and then click New Dimension. Click Next.

2. 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.

3. In Solution Explorer, double-click Master Company Reporting Currency.dim located in the Dimensions folder.

4. Click the Dimension Structure tab if it is not already displayed.

5. In the Attributes pane, select the Master Company Reporting Currency dimension.

6. Set the ErrorConfiguration property to (custom).

7. Expand the ErrorConfiguration node, and then set the KeyDuplicate, KeyNotFound, and NullKeyNotAllowed properties to IgnoreError.

8. Set the UnknownMember property to Visible, and then save your changes.

To add the Master Company Reporting Currency dimension

1. In Solution Explorer, double-click SalesAnalysis.cube.

2. Click the Dimension Usage tab.

3. On the Dimension Usage toolbar, click Add Cube Dimension.

4. 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.