OLAP in Dynamics AX 4 – Installing and Configuring Analysis Services
PDF Version of this Article (with pcitures)
OLAP (On-Line Analytical Processing) is a quick and simple way of analysing information, which would play a critical role in defining business strategies. I must stress on the words quick and simple. Some groups claim that olap is almost 100 times faster than conventional oltp operations (i.e the standard ax reports). Apart from the much obvious improvement is the speed of report processing, Dynamics AX olap report viewer (a MS Office web component), give end user the ability to rearrange their view of data, this feature is also known as ‘slice n dice’.
“Rearranging the view to information?? Surely this can be done in conventional reports as well, and certainly can be done in ssrs”.... well yes, what I am really referring to is a tool that enables the end user to perform operations, which could potentially take a developer a few hours (if not days) to develop on a conventional report.
Let’s take one of the most used reports in AX and compare it with its OLAP counterpart. .the ‘Gross Margin By Customer’ report, is designed to give a list of items bought by a customer, the quantity purchased, extended price, extended, cost, gross profit and GP%... I must admit, this is a very useful report. However, the end user does not have the ability to roll up the sale by item group (nether can the report be grouped by item group), neither can the user compare/analyse sales over a period of time (i.e sales this year compared to last year, or this quarter compared to last quarter). I am aware that the report can be executed for a defined period of time (i.e. the user can provide a date range as a selection criteria), however, to actually view the results, the report would have to be re executed for every date range, so for example, if I want to view sale margin per week for the entire year, then I would have to run the report 52 times!! ... However with OLAP, this can be done with a few clicks of the mouse and in real time (i.e when the end user is actually viewing the report).
People often confuse OLAP with Data Warehousing, though both of them are most powerful when used together, they are entirely different processes.
In layman’s terms Data Warehousing is the processing of storing tactical information.... hence it gives organizations the ability to answer the ‘what’s??who’s ?? and when’s? ...... However OLAP is designed to answer the ‘What If’s?’ and the ‘Why’s??.
Olap cubes are viewed using pivot tables (which is a more interactive and informative version of a normal table). In dynamics Ax, pivot tables are opened using Microsoft Office Web Components, which not only gives a user the ability to slice and dice information within an AX session, but also the ability to export the view (to a cube) on to excel, all with a click of a button..... This is not the same as exporting data to excel using the standard dynamics ax excel template wizard. This is a much simpler, Faster (with a capital F!) and interactive way of interfacing with the MS office stack.
Out of the box, ax 4 comes with about 16 olap cubes.
They are:
Cube Name Description Path Query
Commission Commission, per item/group, sales group, customer group Sales Ledger > Customers > Inquiry OLAPCommission
SalesAnalysis Revenue per item/group, customer/group, dimensions Sales Ledger > Customers > Inquiry OLAPSalesAnalys
SalesMargin Cost, sales amount, sales margin, per customer/group Sales Ledger > Customers > Inquiry OLAPSalesMargin
TrialBalance Net total difference, credit, debit per account/type General ledger > Chart of accounts > Inquiry OLAPTrialBalance
CustRevenue Sales balance, revenue per customer/customer group Sales Ledger > Customers > Inquiry OLAPCustRevenue
VendExpences Amount expensed per vendor/group Purchase Ledger > Vendors > Inquiry OLAPVendExpences
InventReceip Cost adjustment, cost amount, qty, average cost per item, per dimension Stock management > Items > Inquiries OLAPInventReceipts
InventTrans Difference, quantity per item/group per dimension Stock management > Items > Inquiries OLAPInventTransaction
WMSLocTrans Cost,qty poster per item per location Stock management > Setup > Stock breakdown > Locations > Inquiries OLAPWMSLocationTransaction
WMSPickLines Expedition Time, Expected expedition time, Efficiency, per employee. Stock management > Setup > Stock breakdown > Locations > Inquiries OLAPWMSPickingLines
LeadTime Production LeadTime Stock management > Items > Inquiries OLAPLeadtime
LedgerPeriod Amounts, per accounts, per period General ledger > Chart of accounts > Inquiry OLAPLedgerTrans
LedgerTrans General ledger > Chart of accounts > Inquiry OLAPLedgerTrans
ProjectHour Project > Projects > Inquiry OLAPProjTrans
ProjectTrans Project > Projects > Inquiry OLAPProjTransView
SysUserLog Administration > Inquiries > User log OLAPSysUserLog
The following is a screen shots from the sales Margin pivot table
Creating and Configuring an OLAP Server in Dynamics AX.
Dynamics AX uses, SQL Server Analysis Services for all OLAP operations, hence it’s mandatory that SSAS is installed and configured.
Perquisites:
1. Microsoft Core XML Services (MSXML).
2. Microsoft SQL Server 2005 Analysis Services.
3. Microsoft SQL Server Native Client.
4. Microsoft SQL Server 2005 Analysis Management Objects (AMO) Collection (optional).
5. Microsoft Office Web Components 11 (OWC11).
SSAS can be installed on the same server as the database, but in a production environment it’s advisable to perform analysis on a separate machine.
Before you begin: Points to take in to consideration
• If you are running an Intercompany (Multi Company) system, then create separate Analysis Database for every company, as data from one companies cube can overwrite data on to a different companies cube.
• Applications such as Excel can link directly to OLAP cubes in Analysis Services. Even if you have restricted access to cube data in Microsoft Dynamics AX, end users might still be able to access cube data in SSAS, if you have not properly configured security roles in Analysis Services. Security is controlled in SSAS using roles. Refer to documentation on SSAS for more information on how to create/manage roles in SSAS[5].
• Record level security (RLS) is not integrated with OLAP, hence any restrictions placed using RLS are not propagated to OLAP cubes. To enforce data-level restrictions in OLAP cubes, use Analysis Services mining models and custom roles
• Make sure that only administrators or power users have access to the OLAP Parameters form
• All cube security settings (roles and permissions) are lost and must be recreated using SQL Server Analysis Services administration, when a cube is modified and transfer to SSAS.
Step 1: Creating a Analysis Database
Create a new sql server analysis database, by logging in to Analysis services, using sql server management studios.
Right click on Databases and select new database. On the form that open up, type in the name of you Analysis Database (its recommended to follow a naming convention for all AX components)
In this example, I named the analysis Database AX4OLAP.
Note : The analysis Database is not the same as the AX database. At this stage in the configuration process the Analysis database (ax4OLAP) is not related to the AX database in any way.
Please make sure that the database name ONLY contains alphabets and numbers. Or there could be issues synchronizing cubes from AX to SSAS.
If you choose to impersonate a domain account (recommended) then the account needs to have read access to the AX database.
Step 2: Configuring the OLAP Database in Dynamics AX
Open the AX client and browse down to Administration > Setup > Business analysis > OLAP > OLAP servers.
Type in a serverID and the server name (of the server running SSAS), do NOT check the ‘Valid’ check box, as this will happen automatically when you synchronize the cubes defined in AX on to the ssas server.
Note: the full server name must be entered, irrespective of where the server resides. Networking keywords such as ‘local’, will not work.
Now, click on the ‘OLAP Databases’ button
Now type in the database name of the SSAS database you created in step1. The Field Database Id, should automatically be populated with a number, if it’s blank then type in a number manually.
Hit the save (crtl + s) button to save changes.
Note: Click on the General tab to see how the current AX database is selected as the data source in the Decision support Objects section.
Now hit the Synchronise Cubes button (or close the OLAP Database form and hit Synchronize on the OLAP Server form).
Hit Save again to commit changes
Note: if you have any errors at this stage please refer to the Dynamics AX installation Guide.
Step 3: Setting up OLAP Parameters in Dynamics AX
Browse down to the OLAP parameter form (Basic > Setup > Business analysis -> Olap Parameter) and make sure that the database Id you created in Step2 is selected as the default database.
Enter the Year start and Year end values. By entering these values here, you can save time later by not having to set these values for each cube you create. This defines the year range you would like SSRS to collect data over.
Step 4: working with OLAP cubes
Creating cubes is quite a challenge, not because it’s technically complex (it isn’t complex at all), but because Users (or humans in general), don’t know what they want!! (its even worse when they absolutely want something they done need)..... Well the challenge really comes from the fact that there are so many possible ways a user can look at the same data...(don’t worry, you would understand what I mean when you finish creating your first cube).
Before you start creating new cubes, it would be worthwhile going though the standard cubes that come with AX. To import the standard cubes, browse down to the Cube Definition Manager (Administration > Setup > Business analysis > OLAP > Cube Definition Manager), click on the menu Import/Export and then select ‘Import Cube Definitions’.
The cube definitions are normally stored in the client installation directory, under the folder ‘Data’ . so possibly a path that looks like this at the end ‘Microsoft Dynamics AX client\data\OLAPSampleData.def ‘
Note: if you did not change the standard installation path while installing the Ax Client, then the directory path to the cube definitions should already be populated when you click on ‘Import Cube Definitions’.
This is what the form would look like after all the cubes have been imported:
For More information on the various fields on this form, please view the section on Cube Definition Manager (OLAP in Dynamics AX – Creating and processing new cubes)
Step 5: Transferring Cubes to SSAS
Before you can start processing a cube, you must copy the cube and transfer it to the SSAS server. A copy of a cube is called a cube instance (and as the name implies, one can create multiple version of the same cube).
Browse to the ‘Cube Instances’ form (Basic > Setup > Business analysis > Cube instances), this form is used to manage all cubes instances created in Dynamics AX.
Note:
• On the general tab, the dates are defaulted in from the OLAP parameters. One can choose to override these fields if required.
• No new records can be created on this form.
Click on the ‘Create Instance’, this would create an instance of all the cubes found in the list.
Next, select the cube you want to transfer to the SSAS server and click on the ‘Transfer Cube’ button.
To enable the cube to be used from the standard AX forms, click on the Process button
One can view a list of all the cubes proceed from the ‘Analysis View’ form (Basic> Inquires > Analysis View), one can also use this form to analyse data at a Company level, rather thena record or a group level.. for example, a user might want to view the sales margin for all the customers.
Step 6: Test
Before going any further, please ensure that you have transferred and processed, either the Sales Margin or Customer Revenue cube.
Open the customers form (Sales Ledger > Customers), and select any customer. Now click on the Inquires button (view: Advance) and select the Pivot Table option.
Depending on the number of cubes you have processed, you would either get a list of cubes to choose from:
Or a Pivot Table would pop open.
References:
1. http://msdn.microsoft.com/en-us/library/aa550735.aspx
2. http://www.olapcouncil.org/research/resrchly.htm
3. http://msdn.microsoft.com/en-us/library/ms175367.aspx
4. http://www.olapreport.com/Architectures.htm
5. Dynamics AX 4 Implementation Guide
6. Dynamics AX Reporting and Business Analysis Manual -> Working with Olap and Business Analysis Section.