If your organization uses Microsoft Dynamics AX, you have probably wondered about the differences between using standard AX OLAP cubes and timeXtender as your Business Intelligence platform. While standard OLAP cubes in Dynamics AX 2009 come with functionality that addresses some of the challenges of former versions a BI solution based only on standard AX OLAP cubes does present a number of limitations. That is why we will explore how it is possible with timeXtender to create a BI platform for your ERP system that is broader in scope and more easily modified.
Standard OLAP Cubes with Microsoft Dynamics AX 2009
OLAP Cubes are a part of the role-based interface introduced in Dynamics AX 2009, and come with a set of predefined OLAP Cubes and Key Performance Indicators (KPIs). These OLAP Cubes and KPIs are administered from the Dynamics AX 2009 application and integrated with the different role-based screens.
The technology used behind the scenes is Microsoft SQL Server 2005 and Microsoft SQL Server 2008 Analysis Services (SSAS). Therefore, such a solution can be applied to any front-end that is compatible with Microsoft SQL Server.
Maintenance of the OLAP cubes from within Dynamics AX 2009 has been possible since Axapta version 2.5, and though the functionality has been improved in Dynamics AX 2009 and provides support for virtual companies, enum values, and so on, the functionality is still limited in scope compared to a full-blown BI solution.
By using the built-in Cube functionality, information that is part of the core Dynamics AX 2009 application is available for use in the cubes, which is not the case if you connect on the database level. As a result, when built-in cubes are used, information about relations, application names on fields, enum labels, and the setup of virtual companies is handled by Dynamics AX 2009. This is a great advantage if you have little experience with Dynamics AX.
Limitations of Standard AX 2009 OLAP Cubes
When you set up OLAP Cubes in Dynamics AX 2009, the system will implement the SSAS in SQL Server, and load the cubes with data, including a schedule for loading data regularly. However, the functionality in Dynamics AX for setting up and defining dimensions and measures is limited as illustrated in the following list.
- No support for history
- No support for incremental updates of data
- No support for data quality issues
- No support for slowly changing dimensions
- No support for other data sources than Dynamics AX 2009
- No way of manipulating data, since the fact table is the raw table from Dynamics AX 2009
- In addition, it is not possible to change the BI solution directly from within Dynamics AX 2009. You are required to use the BI Studio developer tools in SQL Server. However, while you can edit the solution in SQL Server, the changes
will be overwritten if you subsequently make changes to the data model in Dynamics AX 2009. Your therefore have to carefully consider any data model changes.
- Furthermore, while the solution covers all major modules of Dynamics AX 2009, it appears that some obvious dimensional relations are missing – in particular relations having to do with inventory and production.
- As a result, if your BI solution is based on standard cubes in Dynamics AX, you become dependent on highly skilled Dynamics AX and SQL Server developers to implement changes – and they are a scarce resource.
- To access cube data, users must be assigned appropriate roles or specific user access within Dynamics AX 2009 which directly affects licensing costs. Alternatively, a manual work-around is required to grant direct access to cube data on Analysis Services every time redeployment from Dynamics AX 2009 overwrites the cubes.
- According to Microsoft, full license keys are required to modify the built-in cubes, and if elements such as fields, measures, and dimensions are added or removed, the cubes will have to be redeployed. “The predefined OLAP cubes in Dynamics AX 2009 require full license keys. If any configuration keys are disabled, related tables and fields are automatically removed when the Microsoft Dynamics AX database is synchronized.Similarly, when the OLAP database is synchronized with the Microsoft Dynamics AX database, some related cube components are removed. In this case, you must manually remove those elements of the cube, such as key performance indicators (KPIs), calculated measures, and dimensions based on named queries, before you can successfully process the cubes. Use the error messages that appear in the synchronization log as your guide to the elements that must be removed.”**Microsoft Dynamics AX 2009 Business Intelligence Cube Reference Guide, p.7
Why use timeXtender as BI Platform?
- For a the information in your reports to be useful and actionable, the quality and consistency of the data have to be verified, and all relevant data sources have to be included in you BI solution. This is why the data warehouse and the extract, transform and load (ETL) process is central to creating a trustworthy platform for your Dynamics AX BI solution.
Extracting, Transforming and Loading Data
- Data that is extracted from Dynamics AX 2009 system typically has to go through a cleansing process to ensure that there is no missing information, records with identical information but different IDs, and so on. This data cleansing process is often a surprisingly time-consuming and expensive process that may overwhelm the customer. However, timeXtender provides functionality that can handle any data quality issue that may arise during the creation of the cubes. This functionality greatly simplifies the process, and ensures that the final reports are based on consistent and trustworthy data.
- In timeXtender the entire process of extracting, transforming and loading data (ETL), the design of a data warehouse, and the building of cubes takes place in metadata, while the underlying SQL code is auto-generated. This greatly reduces both implementation time and costs.
Dynamics AX Adapter
- timeXtender comes with an adapter that is specially designed for Dynamics AX 2009. Using this adapter timeXtender extracts data from the Dynamics AX 2009 production systems into the data warehouse where the data can be queried and used for building OLAP cubes without affecting the production system.
- The adapter provides access to all the information in the Dynamics AX 2009 database including hidden information such as help text on each field.
- timeXtender furthermore contains an embedded spreadsheet formula and a business function library that allows business specialists to add advanced calculations to their data cubes without having to learn or know the complicated MDX language. The calculation formulas are automatically translated into the MDX language needed by Analysis Services.
- While standard AX cubes only allows one data source, namely Dynamics AX, timeXtender allows multiple data sources. Customers typically store their information in more than one place, so in addition Dynamics AX system, you may have Excel spreadsheets, information stored in other data bases or text files that you want to include in the BI solution to gain the complete picture of the state of your business. Therefore, timeXtender enables customers to capture data from a string of data sources such as Excel, text files, SAP R/3 and ODBC in the same project.
- timeXtender follows the industry de facto standards for building staging data bases, data warehouses, and OLAP architecture, and it supports enterprise data warehouses and data marts as well as multiple servers. The data warehouse and the OLAP cubes are 100% compatible with the role-centers in Dynamics AX 2009.
Overview of Differences
- The following tables give you an overview of the main differences in approach and technical differences between a solution based purely on standard AX cubes and a solution based on timeXtender.
Differences in Approach