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.
.png "Lifecycle")
Get started Install and deploy Configure Extend Use Troubleshoot View technical reference |
.png "Resources")
|
.png "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. |
|
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). |
|
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. |
Back to top
.png "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. |
|
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:
|
|
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. |
|
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. |
|
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:
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.
|
|
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. |
|
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. |
Back to top
.png "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. |
|
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 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 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. |
|
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. |
|
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. |
Back to top
.png "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 |
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. |
|
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. |
|
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. |
|
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. |
|
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 |
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). |
|
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. |
Back to top
.png "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. |
|
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. |
|
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 |
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 |
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. |
|
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 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. |
|
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. |
Back to top
.png "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
.png "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