Category Archives: Dynamics ax BI

Walkthrough: Defining KPIs for a Cube [AX 2012]


To complete this walkthrough, you will need:

  • Microsoft Dynamics AX with sample data
  • The MyCustomers cube from  Creating Cubes [AX 2012] Post.
  • SQL Server Business Intelligence Development Studio (BIDS) or SQL Server Data Tools
  • Configure Analysis Services by running the Microsoft Dynamics AX Setup wizard

Defining a KPI for Inventory Quantity

To display your KPI in a Business Overview web part on a role center, you must define value, goal, status, and trend expressions for the KPI. If you exclude one of the expressions, the KPI will not display properly.

To create a KPI for Inventory Quantity

  1. In BIDS, open the Analysis Services project that contains the Sales analysis cube, and then double-click MyCustomers .cube.
  2. Click the KPIs tab.
  3. On the toolbar for the KPIs tab, click New KPI. A form displays that allows you to define the KPI.
  4. For the Name field, type Customer sales.
  5. For the Value Expression field, type the following expression.

[Measures].[Total customer sales Count]

This expression retrieves the quantity in inventory units.


You can drag measures from the Metadata tab of the Calculation Tools pane to help you create expressions.

  1. For the Goal Expression field, type the following expression.


This expression identifies the goal for the inventory quantity.

  1. For the Status indicator field, select Gauge from the drop-down list.
  2. For the Status expression field, type the following expression.
[Measures].[Total customer sales Count] >= 7500
Then 1
[Measures].[Total customer sales Count] < 7500
[Measures].[Total customer sales Count] > 6500
Then 0


This expression provides a basis to evaluate progress toward meeting the goal. The graphic that displays for status of the KPI depends on what value this expression evaluates to.

  1. For the Trend indicator field, select Standard arrow from the drop-down list.
  2. For the Trend expression field, type the following expression.
[Measures].[Total customer sales Count] >= 3000
Then 1
[Measures].[Total customer sales Count] < 3000

Then -1

This expression provides a basis to evaluate the status in some historical context. The graphic that displays for the trend of the KPI depends on what value this expression evaluates to. Typically, the trend expression would evaluate the current status relative to a previous status.

To view the KPI

  1. On the Build menu, click Deploy Dynamics AX MyCustomers .

clip_image001[2]Note: The name of the database may vary.

  1. On the toolbar for the KPIs tab, click Browser View. The KPI displays in the list.

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

Microsoft Dynamics AX Demo — Business Intelligence Video

Microsoft Dynamics AX Demo — Business Intelligence Features (Part 1 of 3)

Microsoft Dynamics AX Demo — Business Intelligence Features (Part 2 of 3)

Microsoft Dynamics AX Demo — Business Intelligence Features (Part 3 of 3)

Build a KPI Dashboard in 5 minutes


Integrate Microsoft Dynamics AX analytic components with external data sources

Data warehouses are a popular solution for providing analytic capabilities to users. Until recently, data warehouses were the only reasonable solution for building robust analytic capability. However, as applications become easily interoperable and as technologies such as in-memory databases and OLAP become cost-effective and simpler to use, building a data warehouse is not the only solution to meet analytic requirements.
Table below presents several architecture options for integrating external data with the prebuilt
analytic solution; a data warehouse is just one of the options. The columns represent architecture options, whereas the rows represent the benefits and cost implications of each option.


When most data is in Microsoft Dynamics AX (assuming that Microsoft Dynamics AX is the
predominant  source of data in the organization), you have two options.

The data mash-up option is best suited to an environment where capable users author and publish analyses for the use of others. This option relies on client tools such as Excel PowerPivot. Microsoft Dynamics AX 2012 enables Microsoft Dynamics AX queries to be published to data mash-up tools through OData feeds, or as data exports to Excel.

You can bring external data into Microsoft Dynamics AX either through services (data services consumed by means of inbound ports) or as batch jobs that are executed periodically to import data into tables. With this approach, external data is represented as read-only data within Microsoft Dynamics AX. The benefit to this approach is that external data appears as native Microsoft Dynamics AX data to Microsoft Dynamics AX tools. You can create analytics, reports, and inquiry forms that use the combined data.

A more complex approach involves integrating external data directly into the prebuilt BI solution.
With this option, a BI developer adds another data source to the prebuilt BI solution by using Business Intelligence Development Studio. Additional data tables are brought into the DSV by using the new data connection. It is possible to create dimensions and measures by using the new tables in the DSV.

The traditional ETL-based data warehouse option is suited to scenarios that require complex
transformations or large volumes of data. Although this option is more flexible in terms of capabilities, it is also the most expensive to implement and manage.

You might want to build a data warehouse to implement the following scenarios:

■ Integrate external data sources with Microsoft Dynamics AX data In this approach, the Microsoft Dynamics AX implementation serves as one of many corporate applications.
Although Microsoft Dynamics AX contains some of the corporate data, other systems contain
a considerable portion of the data. To make decisions, data must be combined across systems, and the data warehouse serves that need.
■ Incorporate legacy data into Microsoft Dynamics AX analytics Most organizations
migrate recent data when implementing Microsoft Dynamics AX. Legacy data is still
maintained in read-only instances of legacy applications. Although legacy data is no longer
used for operational purposes, it is required for historical trend analysis. A data warehouse
serves as the repository where legacy data is combined with current data.

Although Microsoft Dynamics AX 2012 does not directly support the creation of a data warehouse schema, the following artifacts generated in Microsoft Dynamics AX 2012 can be used to build a data warehouse:

■ The DSV generated as part of the prebuilt analytic solution can be used within SQL Server
Integration Services when an ETL package is developed to extract data from Microsoft
Dynamics AX.
■ Microsoft Dynamics AX document services can be consumed as data sources based on Simple Object Access Protocol (SOAP).
■ Microsoft Dynamics AX queries can be exposed as OData feeds.

Extend cubes [AX 2012]

you can customize the prebuilt analysis project relatively easily by using the SQL Server Analysis Services Project Wizard. But in some cases, you may want to make
deeper customizations. For example, you might want to:
■ Create a rich hierarchy, such as a parent/child hierarchy to model organizational units.
■ Add new KPIs.
■ Bring external data into the analysis project and create a custom dimension.
You can use Business Intelligence Development Studio to make these types of changes.

Because the prebuilt BI components are included in the AOT as an SSAS project, you can modify the project. To modify the prebuilt Analysis Services project, do the following:
1. In the AOT, expand the Visual StudioAnalysis Services Projects node.
2. Right-click the project that you want to modify, and then click Edit.
An Infolog message appears, stating that a copy of the SSAS project has been created and saved, as shown in Figure below


If SQL Server Business Intelligence Studio is installed, it will start and open the copy of the project.
Changes that you make to the project are not automatically saved to the AOT. You need to save the project and import it back into the AOT.

The DSV contains the table and view definitions that are used by analytic artifacts. Notice that the OLAP framework has implemented several query definition patterns in the DSV:
■ Financial dimensions that the wizard has added appear as custom query definitions in the DSV.
■ The OLAP framework has created query definitions corresponding to Microsoft Dynamics AX views.
■ The OLAP framework has added a reference relationship to resolve virtual companies, if your Microsoft Dynamics AX installation has virtual company definitions.
■ The OLAP framework has created views that make Microsoft Dynamics AX enumerations accessible

in all of the languages that have been added to the project.
Avoid modifying any of the framework-generated objects in the DSV. Any changes that you make to these objects are overwritten without warning the next time you update the project.
You may add your own objects to the DSV (for example, new query definitions. The Project Update  option will preserve these objects.
In Microsoft Dynamics AX 2012 R2, do not implement any partition-specific logic in any of the query definitions. Otherwise, when the project is deployed to multiple partitions, the system may
generate processing errors. (Because the framework adds partition-specific logic to the DSV at deployment time, it may not apply the changes accurately to your query definitions.)

Data source
A data source has been created that points to the Microsoft Dynamics AX OLTP database.

Dimensions, measures, and measure groups

In Figure below , notice the dimensions that are included with the Microsoft Dynamics AX 2012 prebuilt BI solution, as well as the measures and measure groups.
For a list of measures and dimensions, see “Cube and KPI reference for Microsoft Dynamics AX 2012” at image

KPIs and calculations
The SSAS project contains prebuilt KPIs and calculations. Microsoft Dynamics AX 2012 does not provide the capability to model KPIs and calculations in the AOT. You can modify these definitions or
add new ones directly in Business Intelligence Development Studio.


Get every new post on this blog delivered to your Inbox.

Join other followers:

error: Content is protected !!