Planning considerations for analytics [AX 2012]

Updated: August 13, 2014

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

Before you begin the planning process, make sure that the following prerequisites are in place.



More information

Required knowledge

Before you begin installing and using the analytical features of Microsoft Dynamics AX, you must be familiar with Microsoft SQL Server Analysis Services.

Analysis Services 2014

Analysis Services 2012

Analysis Services 2008 R2

Analysis Services 2008

To help plan your Microsoft Dynamics AX implementation, determine a topology that supports the needs of your organization. Consider the following information when determining your topology.

To make sure that the online transaction processing (OLTP) database for Microsoft Dynamics AX performs well, we recommend that you install the Analysis Services database on a dedicated server.

High availability is the ability to provide a service with a minimum of interruptions. You can implement Analysis Services in a highly available environment by using network load balancing (NLB) technologies, failover clustering technologies, or both.

  • Network load balancing – You can use network load balancing to improve the response time for queries as the number of end users increases. Network load balancing, which is also referred to as scale out, distributes the load among several small servers. For more information, see Scaling out an Analysis Services Solution.

  • Failover clustering – A failover cluster is a combination of one or more nodes, or servers, with two or more shared disks. A SQL Server failover cluster instance appears on the network as a single computer. However, this instance has functionality that provides failover from one node to another if the current node becomes unavailable. For more information, see Failover Clustering in Analysis Services.

SQL Server AlwaysOn is the high availability and disaster recovery solution in SQL Server 2012 and 2014. You can implement the Analysis Services database in an AlwaysOn environment to:

  • Reduce the load on the primary Microsoft Dynamics AX online transaction processing database (OLTP).

  • Reduce data latency in cubes and cube-based reports and key performance indicators (KPIs).

To implement the Analysis Services database in an AlwaysOn environment, complete the following tasks:

  1. Create a read-only copy of the Microsoft Dynamics AX OLTP database.

  2. Modify the data source for the Analysis Services database to point to the replicated database (that was created in step 1). To do so, follow these steps:

    1. In SQL Server Management Studio, connect to your Analysis Services instance.

    2. In the tree view, expand the Databases > [Database Name] > Data Sources node.

    3. Right-click the Dynamics Database data source and choose Properties.

    4. In the Connection String row, locate the text Initial Catalog=[DatabaseName].

    5. Change [DatabaseName] to the name of the replicated database that was created in step 1.

For more information about AlwaysOn, see AlwaysOn Architecture Guide in the SQL Server documentation.

Microsoft Dynamics AX provides cubes that you can use and modify. To see a list of the cubes that are included with Microsoft Dynamics AX and for details about each cube, see the Cube and KPI reference for Microsoft Dynamics AX. If you need to create a custom cube, see Walkthrough: Creating a cube.

The default cubes that are included with Microsoft Dynamics AX require that you enable specific configuration keys. If you disable a configuration key that is required for a cube, you must complete the following tasks:

  1. Run the Analysis Services Project Wizard to remove the measures, dimensions, and key performance indicators (KPIs) that are no longer available (because the configuration key was disabled). For more information, see Configure an Existing SQL Server Analysis Services Project.

  2. Modify or remove the reports that require the configuration key.

Microsoft Dynamics AX 2012 R2 and R3 enable data isolation by using data partitions. For example, an organization has several subsidiaries. If the management of the organization does not want employees of one subsidiary to have access to the data for other subsidiaries, data partitions can provide the boundaries that are required for data isolation.

If your Microsoft Dynamics AX installation has multiple data partitions, you must deploy cubes for each partition. For example, suppose you have two data partitions, Partition 1 and Partition 2. You must deploy cubes for each partition. This means you’ll have a General Ledger cube for Partiton 1, and a separate General Ledger cube for Partition 2.

For more information about data partitions, see Data partitioning architecture and Partitions, Companies, and Data Isolation in Microsoft Dynamics AX. For more information about how to deploy cubes for each partition, see Deploy the default cubes.

Security for 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.

When you deploy the cubes that are included with Microsoft Dynamics AX, default roles are created in the database where you deploy the cubes. These roles correspond to security roles in Microsoft Dynamics AX. For example, if you assign a user to the Accountant role in Microsoft Dynamics AX, you should assign that same user to the Accountant role in Analysis Services.


Keep the following information in mind when assigning users to roles in Analysis Services:

  • Role members have permission to view all data in the cubes that the role has access to. For example, if you assign a user to the Project supervisor role, that user will have access to all data in the Project accounting cube.

  • The default roles that are created in Analysis Services are not synchronized with the security roles in Microsoft Dynamics AX. For example, if you modify the permissions of the Accountant role in Microsoft Dynamics AX, you do not affect the Accountant role in Analysis Services.

For more information, see Security and protection for analytics.

A cube contains historical, or cached, data. To refresh the data in a cube, you must process the cube. Determine how often each cube should be processed. Consider that, when a cube is processed, it accesses the data in the Microsoft Dynamics AX OLTP database. Therefore, processing may affect the performance of that database. For more information about how to process cubes, see Automate the processing of cubes.

You can display cube data in many ways, such as in Microsoft SQL Server Reporting Services reports, key performance indicators (KPIs), and in Excel. For details, see the “Use” section of Analytics in Microsoft Dynamics AX.

Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).