Analytics in Microsoft Dynamics AX

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

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

Microsoft Dynamics AX provides online analytical processing (OLAP) functionality through the use of cubes. These cubes, built on the Microsoft SQL Server Analysis Services platform, enable you to analyze large amounts of data and identify trends that you might not otherwise discover when viewing data on traditional reports.

Microsoft Dynamics AX includes default cubes that you can use in your Microsoft Dynamics AX implementation. This article guides you through the process of installing, deploying, and using these cubes. This article also provides information about how to customize the default cubes and create new ones.

Lifecycle

Get started

Install and deploy

Configure

Extend

Use

Troubleshoot

View technical reference

Resources

Microsoft Dynamics AX Business Intelligence (blog)

Microsoft Dynamics AX BI videos channel (videos)

Get started

A cube is a multidimensional structure that contains dimensions and measures. Dimensions define the structure of the cube, while measures provide the numerical values of interest to the end user. Cell positions in the cube are defined by the intersection of dimension members, and the measure values are aggregated to provide the values in the cells.

The following table lists tasks you can complete to learn more about cubes and how you can use them in your Microsoft Dynamics AX implementation.

Task

Details

Resources

Learn what’s new

Several features have been added to Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 R2 to help you install, deploy, and use cubes.

For example, the Analysis Services project wizard, introduced in Microsoft Dynamics AX 2012, makes it easier to create, update, configure, and deploy Analysis Services projects. The wizard performs many of the tasks that you would otherwise need to perform manually.

What's new: Analytics

Learn about cubes

You can use cubes to analyze data in Microsoft Dynamics AX that is otherwise too complex.

A cube consists of a set of measures and dimension attributes. For Microsoft Dynamics AX analysis cubes, measures and dimensions are defined in the Application Object Tree (AOT). A perspective is used to identify the tables and views that contain the measures and dimensions.

A cube contains a snapshot of data that is refreshed (or processed) based on your specifications. After cubes are deployed and processed, you can access the data and display it in reports and key performance indicators (KPIs).

Cube overview

Analytics architecture

Plan your implementation

Before you implement cubes in your Microsoft Dynamics AX environment, there are many things you should consider. For example, you must determine how Analysis Services will fit within your system topology, decide which roles should have access to each cube, and select the tools you want to use to access and display data from the cubes.

Analytics architecture

Planning considerations for analytics

Tools used for analytics

Business Intelligence Capabilities and Tools white paper

Back to top

Install and deploy

Complete the tasks in the following table to integrate Microsoft Dynamics AX and Analysis Services and to deploy the cubes that are included with Microsoft Dynamics AX.

Task

Details

Resources

Verify permissions

To integrate Microsoft Dynamics AX and Analysis Services, you must run the Microsoft Dynamics AX Setup wizard and select the Analysis Services configuration option. Verify that you have the required permissions to run the wizard.

Verify that you have the required permissions for installation

Create a domain account

Create a domain account to run the Analysis Services service.

Create service accounts

Assign the Business Connector proxy account to the Analysis Services server administrator role

The proxy account for Business Connector must be assigned to the server administrator role in Analysis Services. To do this, follow these steps:

  1. Open Microsoft SQL Server Management Studio and connect to your Analysis Services instance.

  2. In the tree view, right-click the Analysis Services instance, and then click Properties. The Analysis Services Properties window is displayed.

  3. In the Select a page area, click Security.

  4. Click Add. The Select Users or Groups form is displayed.

  5. Enter the Business Connector proxy account in the following format: [DomainName]\[UserName]. Click OK.

Install prerequisites

On the computer where Analysis Services is installed, or where you plan to install Analysis Services, run the Microsoft Dynamics AX prerequisite validation utility to verify that system requirements have been met.

Check prerequisites

System requirements

Run the Microsoft Dynamics AX Setup wizard

Run the Microsoft Dynamics AX Setup wizard and select the Analysis Services configuration option. This option configures Analysis Services so that it can be used with Microsoft Dynamics AX.

Configure Analysis Services by running Setup

Install SQL Server Shared Management Objects

You must install the Shared Management Objects for Microsoft SQL Server on Microsoft Dynamics AX client computers that you plan to use to complete the following tasks:

  • Run the SQL Server Analysis Services project wizard.

  • Use the Analysis servers form.

Download the Shared Management Objects for the version of SQL Server that you are using. You can download the Shared Management Objects from one of the following web pages:

Select a system currency and an exchange rate type

When Analysis Services generates monetary amounts in a cube, the amounts are calculated by using the system currency and exchange rate type. Follow these steps to select a system currency and an exchange rate type.

  1. Open the Microsoft Dynamics AX client.

  2. Click System administration > Setup > System parameters.

  3. From the System currency list, select a currency.

  4. From the System exchange rate type list, select an exchange rate type.

Deploy default cubes

Use the Analysis Services project wizard or Windows PowerShell to deploy the Analysis Services project that contains the default cubes. You must deploy and process these cubes to use the reports and KPIs that are based on them.

Deploy the default cubes

Upgrade cubes

When you upgrade from one version of Microsoft Dynamics AX to another, sometimes you can upgrade your cubes by using the Analysis Services project wizard. Other times, you must create new ones.

How to: Upgrade Microsoft Dynamics AX cubes

Back to top

Configure

After you have deployed cubes, complete the tasks, listed in the following table, to configure them for use.

Task

Details

Resources

Update the OLAP data source

Note

If you are using Microsoft Dynamics AX 2012 R2 or later, this task does not apply to you.

By default, the cubes that are provided with Microsoft Dynamics AX are stored in an Analysis Services database named Dynamics AX. If you entered a custom name for this database when you deployed the cubes, you must update the data source that is used to connect to that database.

Update the OLAP data source

Configure the default cubes

Configure the cubes based on the changes that you have made to the Microsoft Dynamics AX configuration or license keys.

Configure an Existing SQL Server Analysis Services Project

Configure security

Security for analysis cubes is set up independently from security for Microsoft Dynamics AX. To grant users access to cubes, you must assign the users to database roles in Analysis Services.

The members of an Analysis Services role have permission to view all data in the cubes that the role has access to. To help restrict a role’s access to specific dimensions and cells in a cube, you can perform customizations.

Security and protection for analytics

Grant users access to cubes

Default Analysis Services roles

Scenario: Help prevent employees of one company from viewing cube data for another company

Scenario: Help secure cube data so that managers see only the data for their own team

Scenario: Mapping security in Microsoft Dynamics AX to Analysis Services

Automate the processing of cubes

When a cube is processed, the data in the cube is updated with data from the online transaction processing (OLTP) database. Analysis Services provides several options that you can use to automate the processing of cubes.

Automate the processing of cubes

Create new date dimensions for cubes

Microsoft Dynamics AX provides a default calendar definition called the Date dimension. You can modify this calendar definition, but changes you make to the Date dimension will affect all cubes that use the Date dimension. Instead you may want to create a new date dimension.

How to: Create a Date Dimension for a Cube

Add financial dimensions to cubes

Financial dimensions in Microsoft Dynamics AX help you analyze finance data. You can add financial dimensions to an Analysis Services project. The MainAccount financial dimension is included with Microsoft Dynamics AX, but you can include other financial dimensions in your Analysis Services project if you define additional financial dimensions.

How to: Add a Financial Dimension to a Cube

Back to top

Extend

The Business Intelligence infrastructure enables you to generate Unified Dimensional Models (UDMs) by using metadata from the Microsoft Dynamics AX relational data model. You can specify measures and dimensions in the AOT to define an analysis cube. You can create, update, deploy, and configure Analysis Services projects by using the Analysis Services project wizard. You can browse cubes, dimensions, and data source views in SQL Server Business Intelligence Development Studio (BIDS). You can also create or modify dimension usage, key performance indicators (KPIs), and organization hierarchies in BIDS.

Model analysis cubes

Task

Details

Resources

Create a perspective

You model a perspective in the AOT. You use a perspective to identify the tables and views that contain the dimension attributes and measures for a cube.

Create a perspective for a cube

Business Intelligence Properties

Walkthrough: Creating a cube

Specify measures

Measures are numeric values like ‘sales’.

Specify measures at different levels and on different elements using the Business Intelligence properties that appear in the Properties sheet.

How to: Specify Measures for a Cube

Create dimensions

Dimensions are the context that help the consumer of measures understand the meaning of those measures, like customer, product, or date.

Specify dimensions at different levels and on different elements using the Business Intelligence properties that appear in the Properties sheet.

How to: Specify Dimensions and Attributes for a Cube

Create a hierarchy

You can define an organization hierarchy to establish a relationship between legal entities. You can use organization hierarchies in cubes to analyze data across virtual companies. If a change is made to the organizational hierarchy, the changes are reflected automatically when cubes are processed.

How to: Include a Hierarchy in a Cube

Back to top

Work with Analysis Services projects

Task

Details

Resources

Deploy a project

Deploying an Analysis Services project creates the defined objects in an instance of Analysis Services. Processing the objects in an instance of Analysis Services extracts and then maps data from the data sources defined in the project into the cube objects.

After the cube and dimensions are processed, you can view the data for the objects in the project.

Deploy an Existing SQL Server Analysis Services Project

Configure a project

If you deploy the default cubes before you modify your license configuration in Microsoft Dynamics AX, you must update the cubes that you deployed by using the Analysis Services project wizard.

Configure an Existing SQL Server Analysis Services Project

Configure cubes (video)

Update a project

You can update an Analysis Services project without overwriting the core dimensional model object definitions, such as dimensions, dimension attributes, measure groups, measures, and cubes. For example, you can update an Analysis Services project to add new language translations for tables and fields that are referenced by a deployed cube or to update the cube enumeration list.

You can also add new attributes and measures to a perspective and then update a deployed cube by using the Analysis Services project wizard.

Update an Existing SQL Server Analysis Services Project

Walkthrough: Customizing a Default Cube

Update a cube (video)

Update default cubes (video)

Create a new project

After you create perspectives that identify the tables and views that contain the measures and dimensions for your cubes, you can generate an Analysis Services project so that you can work with the cubes in SQL Server Business Intelligence Development Studio (BIDS).

Create a New SQL Server Analysis Services Project

Walkthrough: Creating a cube

Create a cube (video)

Import a project into the AOT

If you have multiple partitions in Microsoft Dynamics AX, you may want to use Power View to analyze cube data in multiple partitions. Because financial dimensions and calendars may vary between different partitions, in each partition you must provide cubes that contain financial dimensions that are specific to that partition. You can then modify and deploy each partition-specific Analysis Services project.

Import a SQL Server Analysis Services Project into the AOT

Back to top

Use

The following tables provide information about how you can use the data from cubes.

Access cube data in reports

You can use Visual Studio or SQL Server Report Builder to create a report that uses an Analysis Services cube as a data source.

Task

Details

Resources

Display cube data in an SSRS report

You can use a cube as the data source in an SSRS report. Create a reporting project, define an Analysis Services data source, and then create a report that uses that data source.

Walkthrough: Displaying Cube Data in a Report

Display cube data in a chart control

A chart control is a user control option to display chart data in Enterprise Portal. A chart control can display data from a report data provider (RDP) class or from a cube. You can use the features in Microsoft Dynamics AX to create and deploy a chart control for Enterprise Portal that displays cube data.

Walkthrough: Creating a Chart Control with Data from an Analysis Services Cube

Create a report by using SQL Server Report Builder

You can use Report Builder, which is a component of Reporting Services, to create a custom report that uses a cube as a data source.

Create a report by using SQL Server Report Builder to connect to a cube

Back to top

Access cube data by using KPIs

A key performance indicator (KPI) is a collection of calculations used to measure business success. KPIs are used as dashboard metrics that can be displayed in Business Overview web parts in Role Centers to give you quick high-level insight.

Task

Details

Resources

Create a KPI

After you generate a cube, you can use Cube Designer in BIDS (SQL Server Data Tools) to create KPIs.

The calculations that make up a KPI are a combination of Multidimensional Expressions (MDX) and calculated members that are added using Cube Designer. A KPI generally consists of the value achieved, a goal, a status value, and the trend.

Walkthrough: Defining KPIs for a Cube

Modify a KPI in a default cube (video)

Display a KPI in a Role Center

You can display KPIs in reports or in a web part on your Role Center page in Microsoft Dynamics AX or Enterprise Portal.

You can access KPIs that are in the default Analysis Services database from Enterprise Portal. If you have more than one Analysis Services database, you must create an ODC file to access KPIs that are in additional Analysis Services databases from Enterprise Portal.

Walkthrough: Displaying KPIs in a Role Center

How to: Create an ODC file for a Business Overview Web Part

Manage KPIs

Back to top

Access data by using Microsoft Excel and Power BI for Office 365

You can use Excel to import cube data and create a pivot table.

Power BI for Office 365 is a self-service BI solution delivered through Excel and Office 365 that provides data discovery, analysis, and visualization capabilities to offer business insights from data in Excel. You can collaborate with others by sharing reports and data sets in Office 365.

Task

Details

Resources

Create a report by using Excel

You can use Microsoft Excel to connect to a cube and create pivot tables and pivot charts.

Create a report by using the Excel data connection wizard to connect to a cube

Walkthrough: Analyzing Cube Data in Excel

View cube data in Excel (video)

Create a PowerPivot data mash-up

You can use Microsoft Dynamics AX 2012 R2 or later and Microsoft Office Excel with PowerPivot to combine data from multiple sources for analysis.

Walkthrough: Creating a PowerPivot Data Mash-up

Create a report by using Power View

Power View is a tool that you can use to create highly-interactive, ad-hoc reports that can use cubes as a data source. To create the report, you create a data source to connect to a cube. Then you can create the Power View report by selecting the fields that you want to display on the report.

Create a report by using Power View to connect to a cube

Create an Analyze Data button on a list page to analyze cube data

You can use Power View to create interactive ad-hoc reports in Microsoft Dynamics AX. Several list pages have an Analyze Data button that launches the Power View report designer. The Power View report designer connects to a cube and allows you to drag and drop fields for quick analysis.

Walkthrough: Creating an Analyze Data Button on a List Page

How to: Create a Role Center

Create a data mash-up by using Power Query and share it to a BI sites portal

Power Query provides a familiar and intuitive experience for finding and connect to data from within Excel. You can use it to combine and transform data so that it can be analyzed and visualized for deeper insight. Power Query supports a wide variety of data sources including relational, structured and semi-structured, OData, from the Web, Hadoop, as well as Data Search capabilities that provide users with a search experience for data, returning relevant data sets from across the enterprise and from external sources.

In this walkthrough, you create an OData feed to import Microsoft Dynamics AX data and then combine that data with information from Wikipedia.

Walkthrough: Creating a data mash-up by using Power Query

Back to top

Troubleshoot

Complete the follow tasks to troubleshoot issues that you may encounter when you work with cubes.

Task

Details

Resources

Troubleshoot setup

Find possible solutions to common issues that may occur when you run the Microsoft Dynamics AX Setup wizard to configure Analysis Services.

Troubleshoot issues when running the Setup wizard to configure Analysis Services

Troubleshoot cubes and KPIs

Find possible solutions to common issues that may occur when you work with cubes and KPIs.

Troubleshoot issues with cubes and key performance indicators (KPIs)

Back to top

View technical reference

The following table provides links to technical reference information for cubes.

Task

Details

Resources

View cube and KPI reference content

View detailed information about the measures, associated dimensions, and KPIs in each cube.

Cube and KPI reference for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack

Cube and KPI reference for Microsoft Dynamics AX 2012 R2 and R3

Back to top