Welcome back to my blog on IBM Planning Analytics. Today we will concentrate on automation when it comes to the creation of the newest type of views, MDX views. Specifically, our exercise is as follows. So you just created that brand new cube and loaded data and/or created custom business rules to calculate additional results. Now the question is: do your figures reconcile with the data source(s) - be it relational databases, flat files, ERP software, etc. You would be embarassed to show the results in front of the customer to then find out that you lost actuals data or overstated the budget of next year !
Imagine a case with a 7-dimensional cube containing alternate hierarchies (in Planning Analytics speak), many rollups, many leaf level elements... this is not an easy task. Consider creating views, 1 view for any of the dimensions/hierarchies leaving the other dimensions at the top level in the context area of the view. Clearly this is not a 5 minute job. We can automate all of this with a custom process as outlined below (more details in the process code).
Before jumping to the process code, let's make it real and tangible. We have taken the "Compensation Reporting" cube that is present in the 24Retail TM1 model. Its 7 dimensions are: Employee Name, organization, EmployeeList, Month, Year, Version, Compensation (the measures dimension). FTE is one of the measures that has our interest, just as '6000' which has the Salary as a descriptive alias.
Wouldn't it be nice to push a button and TM1 creates for us 5 views on the fly:
- The total Salary Budget by organization leaf element, including the dimension total and an MDX total. Other dimensions go in the context area.
- The total Salary Budget by Month leaf element, including the dimension total and an MDX total. Other dimensions go in the context area.
- The total Salary Budget by EmployeeList leaf element, including the dimension total and an MDX total. Other dimensions go in the context area.
- The total Salary Budget by Employee Name leaf element, including the dimension total and an MDX total. Other dimensions go in the context area.
- The total Salary Budget by Year leaf element, including the dimension total and an MDX total. Other dimensions go in the context area.
It's like a Rubik's cube that you pivot in every angle but without doing it by hand. Looking at the screenshot below you can imagine that a manual task will take precious time. Do not forget that the organization dimension contains several alternate hierarchies: it increases the number of views to be made and reconcile. The process below does not add the views for all alternate hierarchies, but I encourage the reader to do that task - it's well worth your time and you learn a thing or two.
The end result after running the code is as follows in the pictures (notice that Budget is an alias on 'Version 1' in the 24Retail model).
The 3 steps to success with this process are (after downloading and adding to your model):
- step 1: run the process with pCube a good cube, and pMode = '1'. This will create a text file to help you for step 3.
- step 2: enter the fixed selections after an easy copy/paste from step 1. Overwrite what is now there in the section.
- step 3: run the process with pCube a good cube, and pMode <> '1' and Y or N to add a Leaves total or not. You are done and have a look at the generated views !
If you want, apply an alias or change the decimals of the Leaves total, or... it's up to you to explore the many possibilities.
Have a look at George Tonkin's excellent blog series on MDX views to accelerate your work even more.
PROCESS CODE: https://www.wimgielis.com/WG_CUBE_create_reconciliation_views.pro
#IBMChampion