Export (0) Print
Expand All

Walkthrough: Creating a cube [AX 2012]

Updated: November 26, 2013

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

In this walkthrough, you will create a cube that enables you to analyze data from Microsoft Dynamics AX. You will use the Business Intelligence (BI) properties in Microsoft Dynamics AX to specify measures and attributes for the cube, and then generate an Analysis Services project so that you can work with the cube in SQL Server Business Intelligence Development Studio (BIDS).

In this walkthrough you will create an analysis cube called Sales analysis that will allow you to analyze the following data for the Contoso company:

  • Sales revenue by quarter, months, and weeks

  • Sales revenue by payment mode

  • Average sales by customer group

Before you can complete the procedures in this topic, you must make sure that the following prerequisites are in place.

Category

Prerequisite

For more information

Required software

Microsoft Dynamics AX with sample data and Analysis Services configured

SQL Server Business Intelligence Development Studio or SQL Server Data Tools

System administrators

Analytics in Microsoft Dynamics AX

A cube is defined by its measures and dimensions. A measure is quantifiable, like sales, receivables, or item quantities. Dimensions are used to slice measures. For example, sales by quarter, sales by payment group, or sales by customer group. You can specify measures and dimensions to model a cube in the Application Object Tree (AOT). A perspective is a container for the tables and views that contain the measures and dimensions for a cube. For this walkthrough, you will create a perspective, add Microsoft Dynamics AX tables and views to the perspective, and then define measures and dimensions by using the views and table in the perspective.

The following procedures explain how to create a perspective for the cube and how to add views and tables to the perspective.

  1. In the AOT, expand the Data Dictionary node. Right-click the Perspectives node, and then click New Perspective.

  2. Select the node of the perspective. In the Properties sheet, specify the following property values:

    Property

    Value

    Name

    SalesAnalysis

    Label

    Sales analysis

    Usage

    OLAP

    Setting the Usage property to OLAP indicates that the perspective will be used to generate an Analysis Services project.

  1. In the AOT, right-click the Data Dictionary node, and then click Open New Window.

  2. In the new window, expand the Views node. Drag the following views onto the Views node of the SalesAnalysis perspective:

    View

    Description

    CustTransTotalSales

    Contains sales invoice details.

    CustTableCube

    Contains master customer data.

  3. In the AOT, right-click the SalesAnalysis perspective, and then click Save.

  1. In the second AOT, expand the Tables node. Drag the following table onto the Tables node of the SalesAnalysis perspective:

    View

    Description

    CustPaymModeTable

    Contains payment mode information.

  2. In the AOT, right-click the SalesAnalysis perspective, and then click Save.

You must specify measures and attributes to analyze the desired sales information. To do this, you need to set properties on each view and table in the SalesAnalysis perspective.

The following procedures explain how to set BI properties on the views and table.

  1. In the SalesAnalysis perspective, select the CustTransTotalSales view.

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

    Property

    Value

    AnalysisDimensionType

    Transaction

    AnalysisDimensionLabel

    Sales type

  3. In the Properties sheet, set the following values for each field on the CustTransTotalSales view in the SalesAnalysis perspective.

    Field

    AnalysisUsage

    AnalysisDefaultTotal

    ExchangeRateDateField

    AmountMST

    Measure

    Sum

    TransDate

    NoteNote

    The AmountMST value is in the accounting currency of the company. Microsoft Dynamics AX converts that amount to the analysis currency using Microsoft Dynamics AX exchange rates.

    TransType

    Attribute

    Auto

    TransDate

    Attribute

    Auto

    All other fields

    Auto

    Auto

  1. In the SalesAnalysis perspective, select the CustTableCube view.

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

    Property

    Value

    AnalysisDimensionType

    Auto

    AnalysisDimensionLabel

    Customer information

    AnalysisMeasureGroupLabel

    Customer details

  3. In the Properties sheet, set the following values for each field on the CustTableCube view in the SalesAnalysis perspective.

    TipTip

    You can select several fields and set their property values at the same time.

    Field

    AnalysisUsage

    AnalysisDefaultTotal

    AccountNum

    Measure

    Count

    Blocked

    Attribute

    Auto

    GroupName

    Attribute

    Auto

    City

    Attribute

    Auto

    County

    Attribute

    Auto

    Name

    Attribute

    Auto

    State

    Attribute

    Auto

    MainContactWorker

    Attribute

    Auto

    All other fields

    Auto

    Auto

  1. In the SalesAnalysis perspective, select the CustPaymModeTable table.

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

    Property

    Value

    IsLookup

    No

    AnalysisDimensionType

    Auto

  3. In the Properties sheet, set the following values for each field on the CustPaymModeTable table in the SalesAnalysis perspective.

    Field

    AnalysisUsage

    AnalysisDefaultTotal

    Name

    Attribute

    Auto

    PaymMode

    Attribute

    Auto

    TypeOfDraft

    Attribute

    Auto

    AccountType

    Attribute

    Auto

    All other fields

    Auto

    Auto

Now that you have created a perspective and specified the measures and attributes for the cube, generate an Analysis Services project so that you can work with the analysis cube in BIDS (or SQL Server Data Tools).

  1. Click Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard. The SQL Server Analysis Services project wizard form appears.

  2. Click Next.

  3. Select Create. In the Project name field, type Dynamics AX SalesAnalysis. Click Next.

  4. Select Sales analysis from the Available list and move it to the Selected list and then click Next.

  5. On the Select Microsoft Dynamics AX dimensions page, click Next.

  6. Select Date from the Available list and move it to the Selected list and then click Next.

  7. Select Arabic, Chinese (Simplified), and English (United States) from the Available list and move them to the Selected list and then click Next.

  8. Select Add foreign currency support to the Analysis Services project and then click Next. After the project is generated, click Next again.

  9. Select Save to AOT. Select [New] from the list to create a new AOT node. Select Save to disk, type the location to which to save the project, and then click Next.

  10. On the Deployment options page, Select Deploy the project. Specify the server name. For example, localhost. Select Create new database. Type Dynamics AX SalesAnalysis. Select Process the project after it is successfully deployed and then click Next. Click Next and then click Finish.

    NoteNote

    If you are using Microsoft Dynamics AX 2012 R2, select the database to deploy, select Deploy, select Process the project after it is successfully deployed, and then click Next. After the project deploys, click Next and then Finish.

Now that the SalesAnalysis cube has been deployed and processed, you can browse the cube data within the Analysis Services project. The following procedure explains how to browse the cube data.

  1. In BIDS, open the Dynamics AX SalesAnalysis Analysis Services project. If you are using Microsoft Dynamics AX 2012 R2, the project is called Dynamics AX SalesAnalysis initial.

    NoteNote

    You can open the project from the AOT. To open the project from the AOT, right-click Visual Studio Projects > Analysis Services Projects > Dynamics AX SalesAnalysis and then click Edit. You must deploy and process the project before you can browse the cube data.

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

  3. Click the Browser tab.

  4. Expand the Measures node, expand the Total customer sales node, and then drag Amount onto the data area..

  5. Expand the Customers node, and then drag State onto the row area.

  6. Expand the Customers node, and then drag Customers – Name onto the row area.

  7. Browse the data. You can modify the rows and columns that display in the table or add other dimensions to further slice the data.

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

  2. Expand the Data Source Views node, and then double-click Dynamics AX SalesAnalysis.

    A data source view provides a unified view of the tables and their relationships.

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

    NoteNote

    Notice that the relationships between the measures and dimensions have been inferred by using the relationships in Microsoft Dynamics AX.

    Click the Translations tab to view the translations that exist for the cube. The SalesAnalysis cube has three translations, Arabic (Saudi Arabia) English (United States) and Chinese (People’s Republic of China).

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

After you have defined an analysis cube and created an Analysis Services project, you can 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 or Microsoft SQL Server Management Studio. For more information, see Walkthrough: Analyzing Cube Data in Excel.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft