From MSDN
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
-
Specifying cube measure and dimensions
-
Generating a BI project
-
Deploying a cube in a BI project
-
Browsing cube data
-
Creating the master company reporting currency dimension
Creating a Perspective for the Cube
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. For this walkthrough, you will create a perspective, and then define measures and dimensions on the tables in the perspective.
The following procedures explain how to create a perspective for the cube and how to add tables to the perspective.
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.
Specifying Cube Measures and Dimensions
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.
The following procedures explain how to set BI properties on each table.
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
|
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.
The following procedure explains how to deploy the cube in the SalesAnalysis project.
To deploy the cube
Browsing Cube Data
Now that the SalesAnalysis cube has been deployed and processed, you can browse the cube data within 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.
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.
In the following procedures, you will:
-
Create a mapping for the master company exchange rate
-
Create a Reporting Currency named query
-
Edit the Reporting Currency named query
-
Add the Master Company Reporting Currency dimension
-
Edit references to Reporting Currency in the SalesAnalysis cube
-
Edit references to Reporting Currency in the Master Company Reporting Currency dimension
-
Edit the code for currency conversion
-
Deploy the SalesAnalysis cube with the changes
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 displays.
-
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 Reporting Currency named query
-
In Solution Explorer, right-click Sales Analysis.cube, and then click Add Business Intelligence. Business Intelligence Wizard displays.
-
On the Welcome page, click Next.
-
On the Choose Enhancement page, click Define currency conversion, and then click Next.
-
On the Set Currency Conversion Options page, select the Master company exchange rate measure group, select Euro as a pivot currency, select US Dollar as a sample currency, and select n Euro per 1 US Dollars. Click Next.
-
On the Select Members page, mark the checkboxes for the Invoice amount and Revenue measures in the Measures column, and then select Master end of day rate for both of those measures in the Exchange Rate Measures column. Click Next.
-
On the Select Conversion Type page, select Many-to-many, and then click Next.
-
On the Define Local Currency Reference page, select Attributes in the dimension table. In the list that displays, expand the Companynode, select Currency code, and then click Next.
-
In the Specify Reporting Currencies page, select all currencies except Unknown, and then click Next.
-
Click Finish to complete the wizard.
This creates a new named query object called Reporting Currency in the data source view.
To edit the Reporting Currency named query
-
In Solution Explorer, double-click SalesAnalysis located in the Data Source Views folder.
-
In the Tables pane, right-click Reporting Currency, and then click Edit Named Query.
-
In the Edit Named Query dialog box, replace the 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
-
In Solution Explorer, double-click 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 Reporting Currency dimension.
-
In the Properties window, type Master Company Reporting Currency for the Name property.
-
Set the ErrorConfiguration property to (custom).
-
Expand the ErrorConfiguration node, and then set the KeyNotFound and NullKeyNotAllowed properties to IgnoreError.
-
Set the UnknownMember property to Visible.
-
In the Attributes pane, select the Currency attribute.
-
In the Properties window, click the ellipsis button (…) for the DefaultMember property, click No custom default, and then click OK.
-
In the Data Source View pane within the Data Structure tab, select the ISPIVOT field from the Reporting Currency dimension and drag it to the Attributes pane.
-
Select the ISPIVOT attribute in the Attributes pane.
-
In the Properties window, set the AttributeHierarchyEnabled property to False and type Unpivot for the Name property.
-
In the Attributes pane, select the Currency attribute.
-
In the Properties window, set the OrderBy property to AttributeKey, and set the OrderByAttribute to Unpivot.
-
In Solution Explorer, right-click Reporting Currency.dim, click Rename, and then type Master Company Reporting Currency.dim.
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 edit references to Reporting Currency in the SalesAnalysis cube
-
In Solution Explorer, right-click SalesAnalysis.cube, and then click View Code.
-
Locate the following code.
<ID>Reporting Currency</ID>
<Name>Reporting Currency</Name>
-
Replace it with the following code.
<ID>Master Company Reporting Currency</ID>
<Name>Master Company Reporting Currency</Name>
-
Locate the following code.
<DimensionID>Reporting Currency</DimensionID>
-
Replace all instances with the following code.
<DimensionID>Master Company Reporting Currency</DimensionID>
-
Locate the following code.
<CubeDimensionID>Reporting Currency</CubeDimensionID>
-
Replace all instances with the following code.
<CubeDimensionID>Master Company Reporting Currency</CubeDimensionID>
-
Save the changes and then close the file.
To edit references to Reporting Currency in the Master Company Reporting Currency dimension
-
In Solution Explorer, right-click Master Company Reporting Currency.dim, and then click View Code.
-
Locate the following code.
<ID>Reporting Currency</ID>
-
Replace it with the following code.
<ID>Master Company Reporting Currency</ID>
-
Save the changes and then close the file.
To edit the code for currency conversion
-
In Solution Explorer, double-click SalesAnalysis.cube to display Cube Designer.
-
In Cube Designer, click the Calculations tab.
-
On the toolbar, click Script View. The code that was generated for currency conversion displays.
-
Replace the code for currency conversion with the following code.
// <Currency conversion>
Scope ( { Measures.[Invoice amount], Measures.[Revenue]} );
Scope( Leaves([Time]),
Except([Master Company Reporting Currency].[Currency].[Currency].Members,[Master Company Reporting Currency].[Currency].[Local]),Leaves([Company]));
Scope({ Measures.[Invoice amount], Measures.[Revenue]} );
This = [Master Company Reporting Currency].[Currency].[Local] * (Measures.[Master end of day rate], StrToMember("[Currency].[Currency].&["+[Company].[Currency code].CurrentMember.Name+"]")) / (Measures.[Master end of day rate], LinkMember([Master Company Reporting Currency].[Currency].CurrentMember, [Currency].[Currency]));
End Scope;
End Scope;
End Scope;
// </Currency conversion>
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.
The following procedure explains how to generate a B1 project.
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.
-
Select the following check boxes in the Time periods list for the standard calendar.
-
-
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 displays.
-
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.
-
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 displays.
A data source is used to source and refresh cube data.
-
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 allows you to 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 is comprised of attributes from several tables, including CustTable, CustGroup, AddressState, andAddressCounty. Hierarchies were created based on the relationships between these tables.
-
In Solution Explorer, double-click the Items dimension.
The Items dimension is comprised of attributes from several tables, including 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 exist in the Time dimension depend upon 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.
Prerequisites
To complete this walkthrough, you will need:
-
Microsoft Dynamics AX with sample data
-
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
-
Analysis Services extensions for Microsoft Dynamics AX
-
Completion of setup steps for BI (see "Business intelligence and reporting setup" in the System and Application Setup documentation)