Creating Cubes [AX 2012]

This section discusses how to create new cubes and reports by using tools built into Microsoft
Dynamics AX 2012, Figure below shows the four-step process for creating a new cube

Identify requirements

Often, when a user asks for additional information, you get a request for a new report (or two or
three). For example, you might get a requirement request for a report like the one shown in
Figure below from someone in the Sales department.

This report shows sales revenue trends by sales channel. More formally stated, this report shows sales revenue by sales channel by calendar month.
The request for this report might be followed by requests for “a few additional reports.” Some of
the typical follow up questions would be:
■ What about quarterly trends? Is there seasonality?
■ Are some regions doing better than others?
■ Can we see the number of units sold instead of revenue?
■ Can we see the average unit price? Are steep discounts being given?

If you were to build a PivotTable to answer these questions (which is probably a good idea,
because this would let the users slice the data, thus saving you from the effort of building all of those reports), you could construct a PivotTable like the one shown in Figure below.

In this case, you have identified the measures (the numbers you are interested in) and the
dimensions (the pivots for the data).
The following sections show how to build a cube to meet these requirements.

Define metadata

The next step is to determine which Microsoft Dynamics AX tables or views contain this information. For the purpose of this example, assume the following:
■ The CUSTTRANSTOTALSALES view contains sales invoice details.
■ The CUSTTABLECUBE view contains master data about customers.
■ The CUSTPAYMMODETABLE table contains payment mode information.

Define perspectives

Next, you need to define the metadata that is required to generate the cube in the AOT. As you might recall from Microsoft Dynamics AX 2009, you define the metadata required to generate cubes in the Data Dictionary\Perspectives node of the AOT.
Each perspective corresponds to a cube. Tables or views that are contained in a perspective node generate measures or dimensions. Depending on table relationships (and inferred view relationships), measures are associated with dimensions within the generated project.

If you want to designate a perspective node that contains only dimensions, Microsoft Dynamics
AX 2012, includes a property at the perspective level specifically for this purpose: SharedDimensionContainer. If you designate a perspective as a shared dimension container, tables and views within that perspective will be used only to create dimensions. Moreover, all of the dimensions will be associated with all of the measures; that is, they are truly shared dimensions, provided that they are related in Microsoft Dynamics AX.

Follow these steps to create the new perspective for this example:
1. In the AOT, expand the Data Dictionary\Perspectives node.
2. Create a new perspective node, and name it MyCustomers. The new node contains two subnodes: Tables and Views.
3. Set the Usage property of the node to OLAP to designate that this perspective will be used to
generate a cube.
If you are familiar with Microsoft Dynamics AX 2009, you may notice that the Ad-Hoc
Reporting option for the Usage property is missing in Microsoft Dynamics AX 2012. You
can select only OLAP or None. It is no longer possible to generate report models by using
perspectives in Microsoft Dynamics AX 2012.
4. Drag the tables and views listed in the previous section into the newly created perspective.
For more information, see ”How to: Create a Perspective for a Cube” at http://msdn.microsoft.com/en-us/library/cc617589.aspx.

Define table-level properties

Strictly speaking, table-level properties (see Figure below) are optional. However, if you do use them, cubes will perform better.

You can also specify custom labels to give specific names to generated measure groups and
dimensions. AnalysisDimensionLabel, AnalysisKeyAttributeLabel, and AnalysisMeasureGroupLabel are new properties introduced in Microsoft Dynamics AX 2012. Instead of providing English text, you can provide Microsoft Dynamics AX labels so that dimension names are translated into other languages.

The AnalysisIdentifier property defines the field that provides the name for a dimension key. If you look at the Name field for this property in Figure 10-22, you will notice that the Methods Of Payment dimension is keyed by the Name field.

If you are a fan of the semantics introduced with the IsLookUp property in Microsoft Dynamics
AX 2009, you will be pleased to know that views in Microsoft Dynamics AX 2012 provide this
functionality. However, the IsLookUp property will be deprecated in future releases, so it is
recommended that you do not use this property.

Define field-level properties

Defining field-level properties is the key step in defining metadata. You need to identify individual
measures and attributes that are necessary in the cube.
First, expand the CUSTTRANSTOTALSALES view, and set the field properties as shown in Table below

The AmountMST field will generate a measure that is summed when it is aggregated.
ExchangeRateDateField is a new attribute added in Microsoft Dynamics AX 2012 for currency
conversion. In this example, the OLAP framework should convert the AmountMST measure to all available currencies, so that users can analyze transactions (possibly conducted in different currencies) across a common currency. The TransDate field contains the date on which the measure will be converted into other currencies with Microsoft Dynamics AX exchange rates.

Users need to be able to slice the data by TransType and TransDate, so these fields are designated as attributes.
Next, open the CUSTTABLECUBE view, and set the field-level properties as shown in Table below

Finally, expand the CUSTPAYMODE table, and set the field-level properties as shown in Table below

Generate and deploy the cube

After you define the necessary metadata, you can generate an SSAS project by using the SQL Server Analysis Services Project Wizard. You can deploy and process the project directly from the wizard, or you can open the project in BI Development Studio and extend it by using SQL Server functionality.
Define the project
In the wizard, select the Create option, because you are creating a new project, and provide a name. Alternatively, if you want to include the new cube in the prebuilt SSAS project, you can select the Update option.
On the next page, select the perspectives that are used to generate cubes and dimensions within the project. For this example, you would select the MyCustomers perspective. You can include one or more perspectives within the same project.
You can also include Microsoft Dynamics AX financial dimensions, in addition to Microsoft
Dynamics AX calendars and Microsoft Dynamics AX languages, as discussed earlier in this chapter.
Add currency conversion logic
Next, the wizard lets you add currency conversion logic to the project.
As you may recall, while defining field-level properties for the perspective, AmountMST was
identified as a measure that needs to be converted to other currencies. The AmountMST field contains an amount that is recorded in the accounting currency of the company. Because

Microsoft Dynamics AX might contain multiple companies that have different accounting currencies, transactions might be recorded in different accounting currencies.
For example, the CEU company’s accounting currency is GBP, whereas the CEUE company’s
accounting currency is USD. In the AmountMST field, sales for CEU are recorded in GBP, whereas those for CEUE are recorded in USD.

Because a cube aggregates data across companies, a user browsing the cube could inadvertently add GBP values to USD values unless something is done to differentiate the two amounts. The Microsoft Dynamics AX 2012 OLAP framework builds this mechanism for you in the form of currency conversion support.

Microsoft Dynamics AX 2012 cubes contain two system dimensions: Currency and Analysis
Currency. If the user uses the Currency dimension to split the measures that are shown, Microsoft Dynamics AX displays amounts only in the chosen currency. If the user uses the Analysis Currency dimension to split the measures that are shown, all amounts are shown, but the resulting values are converted to the chosen analysis currency by using Microsoft Dynamics AX exchange rates. This happens through currency conversion.

Here is an example: assume that the transactions shown in Figure below are included in the
CUSTTRANSTOTALSALES view. (Note that two columns have been added, Accounting Currency and AmountCur, to clarify that each company has a different accounting currency.)

If a user creates a PivotTable and displays the total AmountMST value split by the Analysis Currency dimension, the result is as shown in figure below

To get the value of AmountMST in USD, the system calculated the USD equivalent of each of the amounts, as shown in Figure below.

Save the project
After you specify currency conversion options, the system will generate the project and prompt you for a destination to which to save the project.

You can save the project in the AOT or on disk. This gives you the flexibility to maintain SSAS
projects in the development environment of your choice. OLAP framework tools, such as the SQL Server Analysis Services Project Wizard, will work with projects whether they are on disk or in the AOT.

If you save the project in the AOT, the project will be saved in your layer.

Deploy and process the project

You can deploy the project directly to the Analysis Services server at this stage. It’s important to note that the wizard calls the Analysis Services deployment functionality behind the scenes. If you do not have the Microsoft Dynamics AX Development Workspace (including Business Intelligence Development Studio) installed on your computer, this step may fail.

Source Inside Microsoft Dynamics AX 2012 Book