OLAP in Dynamics AX 4

Hi All,

I wanted to introduce you to my first blog… Think it’s that stage in my career, where I needed to offload some of the nifty information that’s filled in my head… … its probably out of guilt that I wasn’t able to contribute enough to the AX community…

Well I guess, it had to start some time… So here goes:

Please feel free to ping me if you have any questions.

Regards,

Mohammed Rasheed

www.dynamic-ax.co.uk

http://dynamic-ax.spaces.live.com/feed.rss

OLAP in Dynamics AX 4 – Installing and Configuring Analysis ServicesOLAP (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