Monday, July 24, 2017

Dimension vs Smart lists - Part 1 - MRC approach

It has been really a while that I posted anything. It was either work or arrival of a sweet angel into our lives, which kept me busy throughout this time. There have been many topics which I want to write about and probably my schedule will allow me to get back into motion.
Let me give you a brief background about of this post. We were implementing a workforce and OPEX solution. The workforce module was an employee based model calculating salary, benefits, etc. and OPEX module had all expenses rolled up to give us P&L. The solution seems to be a usual one but things turned complex while we were almost half way through our implementation when the client brought in a new set of requirements. OPEX cube uses Entity and Location dimensions to track expenses while Workforce cube didn’t use Location dimension. It was after a while that client came back asking for the ability to track workforce expenses by locations.
Being almost half way through the implementation, we had two options –

Either enable location dimension for workforce cube and rework on all the artifacts created so far
Use location dimension as smart list and give planners an ability to tag base location to each employee

With some further brainstorming with the client, we figured out that they just wanted to tag the locations to workforce expenses to carry out allocations down the line and wanted to have workforce expenses tracked by locations. Thus it didn’t make sense to add a whole new dimension, update all artifacts and re-engineer the calculation logic.

Comparing the two concepts – new dimension vs smart lists, a standard dimension would allow easy roll up, slice and dice whereas smart list can be used as data for reporting. Since in our case we wanted to just tag location to employees, it made more sense to use smart lists rather than standard dimension.

For this post, I have come up with a small application to demonstrate what can be achieved with the Smart list in lieu of standard dimension. Here is the dimensionality of the application

I created a smart list which is replica of location dimension and tagged it to account Employee Base

In order to move data between two cubes within an application, we can adopt either of the below approaches:

Map Reporting Cube (MRC)
  • This allows mapping a smart list from source cube to a dimension in the reporting cube
  • Ideal when Smart list entries can change over time. With changing Smart list entries, sync between the source entries and target dimension members is easy
  • Absence of dimension at source or target can be defined and mapping is managed internally by planning system
  • Does a member to member mapping and require source members to be available on target
  • Can be BSO to BSO or BSO to ASO
  • Source and target members should be same
Business rule with @XWRITE
  • Custom rule gives the flexibility to map any source member to any target member
  • Ability to perform calculations or transform data while moving between cubes
  • Can be BSO to BSO ONLY
  • Ideal when Smart list members are fixed and would not change over time. Changing Smart list entries increases overhead to maintain all options in business rule as well as smart list entries
  • Many to one or many to many mapping possible and needs to be defined in business logic

Considering that location dimension would change over time and smart list entries would need an update, it makes more sense to go with MRC option in this scenario. I will also take an example of the @XWRITE approach in future posts.

We designed a form on workforce cube to accept workforce related inputs. This form is to mimic the process where users plan for workforce expenses and tag base location to each employee using smart list. Considering a hypothetical situation, both the employees for Entity 2 are tagged to Location 1  while for Entity 1 each is tagged to a different location.


An MRC is defined to map source members from WKF cube to OPEX cube with following mapping and POV.



After saving data in the employee form, MRC is executed to move data from WKF to OPEX cube. Data gets copied over. Since Entity 2 had both the employee tagged to Location 1,  the sum of both the employees goes to Location 1 and other locations have nothing.


This form is pointed to OPEX cube which does not have employee dimension but has workforce accounts. This is required as MRC does a member to member mapping and requires source accounts to be available on target else the data movement would fail.

This is just an example how the cube size and potential blocks can be reduced by the use of Smart lists. The use of smart lists in lieu of dimensions requires a thorough evaluation of requirements and does not always be a correct choice.

Hope this helps !!!