Leverage the Out-Of-Box Cubes and BI in Dynamics AX

Introduction

Business Intelligence of Excel & SSAS is the whole point of going Microsoft. Is your Microsoft Partner doing a good job? This is a ruler to measure the configuration against the Out-Of-Box. The standard stuff should be good enough. Otherwise, Microsoft is not doing a good job. Before ML and AI really mature in the Enterprise space, this is the most value-add in BI.

Background

Cubes, Analytics, Business Intelligence, Data Marts, Data Warehouses, Entity Stores are the terminologies used to describe the tools and methods to post process the business transaction data for supporting data-based business decision making. The nature of after-fact process and the requirement of critical-mass data volume determine the unique characteristics of the tools and methods:

De-normalized data (mixed data elements from different levels)
Not real time (usually a copy of the real-time transaction data refreshed at a designated time interval)

Microsoft provides a stack of technologies in the BI space (To name a few, SSAS, Excel, VS, AX Dev Workspace, BI online services, PowerView, PowerPivot, Tabular models, Entity Stores, PowerBI). There are however 2 fundamental tools that any Microsoft Technology customer should always think of leveraging. These 2 technologies have existed for a long time and have matured in their capabilities. This is the area of the most value-add for any company in using business technologies.

Excel
Many people have not realized that this is the #1 business application
The key is to find a balancepoint where you are not over using Excel to substitute other business application, e.g. ERP modules
Excel is a great tool in doing many things – Pivoting is one of them, but not good at other things – e.g. following business rules when changing data
SQL Server Analysis Service
It’s probably one of the easiest tools in structured data storage for BI purpose
SSAS is the foundation for using the entire Microsoft BI stack
Powerful and sufficient enough to handle majority BI needs

In the context of ERP and business systems, Microsoft Dynamics AX is designed to leverage the fundamental SSAS technology. AX itself includes the meta data definitions for 20+ SSAS cubes (Perspectives on the AOT). These out-of-box meta data can be deployed to the SSAS environment of any Dynamics AX implementation to create the standard cubes.

Independent of the AX business system, the Excel’s functionality of Pivot and PowerPivot is so powerful when used together with SSAS cubes that this should be a must for AX ERP implementation.

Microsoft Dynamics AX provides some other add-ins to integrate Excel and SSAS. These include Dynamics AX add-in for Excel, SSAS PowerView component for SharePoint and Enterprise Portal, VS Development Environment for AX. But in terms of maturity, benefits and fulfilling BI requirements, Excel and SSAS are the most important technologies.

Cubes are data stores in SSAS analytic databases with pre-calculated values for different dimension values. The out-of-box cubes are based on the standard AX data structure and optimized for best practice AX implementation (e.g. the right set for main account categories). Customization can be done to change the out-of-box cubes or to create new cubes. Cubes in SSAS databases are not real-time transaction data. They need to be refreshed to include the latest business transactions.

Some of the AX standard reports use the cubes. For example, the “Top vendors by YTD purchases” report gets its data from the cubes. The report would display “There is no data available” if the cubes are not deployed. Enterprise Portal uses the cubes extensively. All those KPIs on the dash board would not provide the right data if the cubes are deployed or processed with the latest data.

This solution doc is created to describe how the default cubes for AX 2012 R3 are deployed and a few ways to use these cubes.

Solution Considerations

SSAS cubes, AX reports based on the cubes, AX client PowerView Analyze data functionality, Enterprise Portal dash boards and Excel Pivot on the cubes are all standard features of Microsoft Dynamics AX. These features need to be deployed to be available for use.

Option 1: Deploy out-of-box cubes. There are different ways to deploy the default OLAP cubes that are included with Microsoft Dynamics AX. For example, use Analysis Services Project Wizard, Windows PowerShell or even Visual Studio. Details of using the Analysis Services Project Wizard are described in this document.
Option 2: Add proper security access to the cubes. Cube security is totally independent of the AX Application security. Cube has its own roles defined at the SSAS database level. The AX cube meta data includes the standard AX roles created as cube security roles. This would work for AX client’s access to the cubes. Access to the cubes outside of AX would require different security access.
Option 3: Refresh the cubes with the latest transaction data. Cube data is not real time. They need to be refresh at a designated interval to reflect the latest transaction data.
Option 4: Use standard AX reports that need cubes. On the AX client, the standard AX reports in the Reports section of many modules utilize the cubes. If the cubes are not deployed or if they are not refreshed, these reports would not have the right data.
Option 5: Leverage the Enterprise Portal. Dash Boards on the Enterprise Portal provide some of the KPI’s and matrices which highlight some quick insight on an organization’s performance. These KPI’s and matrices use the cubes extensively.
Option 6: Integrate with the SharePoint. The Analyze data button on many forms uses the SharePoint and the cubes to create the PowerView reports.
Option 7: Use Excel to Drill Through the Cubes. Excel is an excellent tool in slicing and dicing the multi-dimensional data. The cubes stored in the SSAS database can be easily pulled into Excel sheet to analyze data in many different perspectives and facilitate decision making.

1. Deploy Out-of-Box Cubes

As an ERP system, Microsoft Dynamics AX comes with 20+ default OLAP cubes. The definitions of these cubes are in the AOT as Perspectives and Analysis Projects. These out-of-box cubes need to be deployed to be available for use.

There are different ways to deploy the default OLAP cubes. Ranging from low to high on the technical levels, these include using Analysis Services Project Wizard, Windows PowerShell and Microsoft Visual Studio. Customization of the cubes involves development efforts which can only be done in Visual Studio. Steps of using the Analysis Services Project Wizard are described here.

Navigation:

From the AX Client > File > Tools > Business Intelligence (BI) tools > SQL Server Analysis project wizard

The deployment process creates the SSAS database on the SSAS server. The SSAS server is specified in the AX System Administration module. This needs to be in place before running the Analysis Services project wizard.

Navigation:

From the AX Client > System administration > Setup > Business Intelligence > Analysis Services > Analysis servers

When setting up the analysis server, make sure the system exchange rate information is specified. Also, since the deployment process includes all the AX companies in the build process, make sure the company DAT has all the Ledger information entered (e.g. calendar, accounting currency, COA, etc.). It might error if these information elements are not in place. For the log file, it’s recommended to use the AX Admin’s document folder to store the log. The OLAP database will be created after the first-time cube deployment. The database would bear the name of Dynamics AX + “Partition name”, e.g. , Dynamics AX initial.

There are 4 options for deploying the analysis cubes to the analysis server, namely, Deploy, Configure, Update and Create.

Deploy – Deploy a project for the first time
Configure – Deploy and update a project after there are changes to AX configuration keys (turning on/off certain features) or language translations.
Update – Update and deploy a project when there are changes to the project objects (e.g. adding dimensions, measures, etc.)
Create – Create a new project

For the first time, we will select Deploy and then click next. On the Select an existing Analysis Services project page, click Select a project from the AOT. Select Dynamics AX and click Next (Dynamics AX project needs to be deployed first before other projects). Select the Process the project after it is successfully deployed check box. Click Next to deploy the project and process the cubes that are in it (Processing the cubes will bring the transaction data over from the transaction database). On the Deploying page, click Next when the deployment is completed. Click Finish to close the wizard.

2. Add Proper Security Access to the Cubes

Cube security is completely independent of the AX Application security of roles, duties, privileges and permissions. Cube security is defined at the SSAS database level using database roles. The AX cube meta data includes the standard AX roles created as cube security roles. This would work for AX client’s access to the cubes. Access to the cubes outside of AX would require cube database role based security access. A database role called ReadOnly is created to accommodate the access from outside of AX (You need to have the database privilege to create a data role).

Navigation:

From the SQL Server Management Studio > Server type: Analysis Services > Server name: SSAS Server > Windows Authentication > Databases > Dynamics AX initial > Roles

2.1 AX Security Roles as SSAS Cube Access Roles

The deployment of the default cubes would create a set of SSAS Cube Access roles for the standard AX application security roles. These database roles ensure that AX users would have the access to the cubes through the AX client.

2.2 Custom SSAS Cube Access Role

In order for the AX users to access the cubes outside of AX (e.g. using Excel), we need to create a custom database role to grant the right access.

Right click the database roles, select new Role…Add a new role name, e.g. ReadOnly. Select the Read definition to grant the new role the database permission of ready only. Add an AD group POKEMON\Global-AX2015-GeneralUsers as the member of the database role. This ensures that all AX uses would have cube permissions defined for this role. Click the Cubes tab and make sure all the cubes are selected with Read access and DrillThrough capability. These are illustrated in the following screen shots.

3. Refresh the Cubes with the Latest Transaction Data

Data stored in cubes are static in the OLAP (On Line Analytical Processing) system. Cubes need to be refreshed to have the latest data from the OLTP (On Line Transaction Processing) system. Refreshing can be performed at both the database level and the individual cube level. It can be done manually or as a scheduled job.

3.1 Manual Processing

The manual processing of the cubes is done on the SSAS instance.

3.1.1. Database Level Refreshing (For All Cubes)

Navigation: From the SQL Server Management Studio > Server type: Analysis Services > Server name: SSAS Server (e.g. for PROD:) > Windows Authentication > Databases > Dynamics AX initial > Right click and select Process

3.1.2. Cube Level Refreshing (For Individual Cube)

Navigation: From the SQL Server Management Studio > Server type: Analysis Services > Server name: SSAS Server (e.g. for PROD:) > Windows Authentication > Databases > Dynamics AX initial > Cubes > Sales cube (for example) > Right click and select Process

3.2 Automatic Processing via Batch Job

The automatic processing of the cubes is done on the regular database instance via the SQL Server Agent.

Database Level Refreshing (For All Cubes)

Navigation: From the SQL Server Management Studio > Server type: Database Engine > Server name: SQL Server (e.g. for PROD:) > Windows Authentication > SQL Server Agent > Jobs > Right click to create a new job

There are some errors when processing the standard cubes. These errors are ignored in the current job “Refresh Cubes”. This job is scheduled to run on a nightly basis at 12:00am every day. It processes all the cubes in the SSAS database Dynamics AX initial.

4. Use Standard AX Reports That Needs Cubes

On the AX client, the standard AX reports in the Reports section of many modules utilize the cubes. If the cubes are not deployed or if they are not refreshed, these reports would not have the right data. For example, the “Top vendors by YTD purchases” report.

Navigation: AX client > POK > Procurement and sourcing >Reports > Statistics > Vendor > Top vendors by YTD purchases

Other reports under Statistics > Spend analysis would need to use the cubes as well. They only have data after the cubes have been deployed and processed.

5. Leverage the Enterprise Portal

Dash Boards on the Enterprise Portal provide some of the KPI’s and matrices which highlight some quick insight on an organization’s performance. These KPI’s and matrices use the cubes extensively. The standard cubes rely on the AX configuration to be in alignment with the way that the cubes are designed. In reality, this may not be the case. Some of the cube data may need some tweaks to get the accurate data. Presented below is a screen shot on the dash board for the CEO role center home page.

6. Integrate with the SharePoint

The Analyze data button on many forms provides a report design and publish tool to create PowerView reports and publish them to the SharePoint. This report design tool accesses all the cubes deployed in the SSAS server. The tool is very easy to use (Similar to Excel) and is powerful in drilling through the cube data. The publishing capability allows the reports to be shared in a friendly collaboration environment on SharePoint.

An example is presented here for the Customer form with an Analyze data button.

This capability does require to have a SharePoint Enterprise environment. For Pokemon AX production environment, the SharePoint deployed is the SharePoint Foundation, which is not sufficient to support the PowerView Report design functionality. Pokemon does use SharePoint extensively for other collaboration purposes. If there is a SharePoint Enterprise deployed, the AX SharePoint would be reconfigured to use the SharePoint Enterprise Edition. Only when this is done, the PowerView Report Design tool can be configured.

7. Use Excel to Drill Through the Cubes

Excel is an excellent tool in slicing and dicing the multi-dimensional data. The cubes stored in the SSAS database can be easily pulled into Excel sheet to analyze data in many different perspectives and facilitate decision making.

Here is an example of a pivot table based on the sales cube. There are a lot of measures and dimensions in a cube. A cube is like a mini database. Need to be careful in selecting the measures (calculated fields) along with the right dimensions (the fields with the sigma sign).

To create a connect to a specific cube, follow the below steps.

Click Data tab on the Excel sheet
Click From Other Sources in the field group Get External Data
Select From Analysis Services
Enter the Server name using the SSAS server name and the instance name combination
Select Use Windows Authentication for Log on credentials
Click Next
Select a desired cube and use all defaults (e.g. General ledger cube)
Click Finish and then OK
The cube is now available to slice and dice its data
Select the desired measures and dimensions

Please notice that the fields with sigma signs are calculated fields – these are the measures. The ones without sigma signs are the dimensions. The dimensions can be used as row or column labels to slice and dice the data for the measures. The dimensions can be used as report filters as well. Within a single cube, there are many measure groups. Certain dimensions are related only to certain measure groups. If you use non-related dimensions to slice and dice the measure data, you might see repeated data or incorrect presentation of the cube data. A cube is like a mini database with many tables. Presenting data from cross-tables may not be right if the relation is not there. The cube already has the functional relationships defined. If you pull the data for a meaningful functional purpose, you should have a meaningful Excel report.