How to save changes in hierarchies?
I need to store and navigate the changes i have in my Customer hierarchy.
In our business, each Customer is assigned a Salesperson, and this is replicated into our BOARD data model where the entity "Salesrep" is directly linked to the entity Customer.
Over time, this relationship changes and we need to keep track of this change, instead of seeing the latest situation: all Customers' sales units and amounts will be linked to the most recent assignment.
How would you address this situation?
Accepted Answer
-
Hello fellow BOARDies,
The concept you are describing, Andrea, is usually known as Slowly Changing (or Slowly Moving) Dimension, a common database concept, where you need to keep track of changes over time on a hierarchical relation that is supposedly 1 to 1.
Consider that in order to be able to do this, the data source you are loading data from, must be accordingly structured and support the change suggested by Gabriele. In the solution proposed, you would need to have your facts or information to be always detailed by Date, Customer ID and Sales Rep ID (apart from any additional dimension), so that you can correctly connect the amounts to the appropriate and correct Sales rep/Customer/Date combination.
What Gabriele describes is a common solution, keeping one Sales Rep linked to Customer, and another as separated SCD, is usually the best approach.
It might also be the case that you have a sales rep that works in a different role on top of the customer dimension, e.g. account manager.
In that case, using SCD concept you are able for example to execute specific calculations that "link" what is the actual account manager vs the sales reps, through the customer, like Sales Commissions that needs to be recognized from Sales Rep to Account Manager (or anything of that sort).
If there are multiple elements that need to be considered as a slowly changing dimension, if might be worth to concatenate them in a unique SCD key that then has the separate single dimensions as fathers.
Hope this helps!
11
Answers
-
Hi Andrea Masiero,
the easiest way to achieve such kind of reporting and save the changes you have in the hierarchy, is to create another entity "Sales Rep" as part of your BOARD data model.
This entity should then be added to all of the information (infocubes) which might benefit from this "additional" navigation (i am assuming the infocubes are also structured by a time entity).
In this way, navigating the infocubes by "Sales Rep" (standalone entity) you would trace the different amounts or units sold by each sales rep to each customer.
Navigating, selecting or drilling by "Sales Rep" (the entity linked to the Customer) you would always have the latest situation, as per the hierarchy.
Michele Roscelli has implemented many BOARD solutions and might add to this
Hope this helps
8 -
Hi Michele Roscelli,
We have this kind of challenges in one of our databases, and we are indeed using the "SCD key" concept that you mentioned. However, since our cubes are already several Gigabytes and between 10 to 14 dimensions, adding a SCD key dimension (and sometimes up to 5 different SCD keys corresponding to 5 slow moving hierarchies in the cube) can be very heavy.
Therefore a simple question (I think we asked this question to Andrea Masiero 30 times), does Board plan to implement a time-dependent hierarchy concept in the tool or is that out of question ?
Thanks,
Etienne7
