Dimension change - distribute values for one old element to 3 new elements

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Dimension change - distribute values for one old element to 3 new elements

      We have quite a large cube with historical sales data for the past years.

      ​Now, one of the sales representatives quits the company and his sales area is divided and distributed to his 3 successors.

      ​To be able to do past-based planning, his complete data inside the cube shall also be evenly distributed to those 3 new sales reps.

      ​I think, if it were just one new sales rep, the element could just have been renamed, but how can I divide the element?

      ​Has anybody done something like that before?

    • Depends on complexity of the model. Easiest way is to use rule something like.
      ['SalesRep':{'NewRep1','NewRep2'.'NewRep3'}] = ['SalesRep':'OldRep']/3 + continue()
      or
      ['SalesRep':{'NewRep1','NewRep2'.'NewRep3'}] = ['SalesRep':'OldRep']/3 + []
      Target of the rule can be extended based on what you want to do - plan based on previous Actual data, and in time - or you can do it dynamically for time periods after the organizations change.
      Rule can be restricted to Base cells ([...] = B: ...) - depends on data character
      in this case OLAP will distribute OldRep values equally to all new SalesRep at runtime. If perfromance is not sufficient or if you are loading data from OldRep periodically you would have to adjust etl project.

    • Hi jjunek, thanks for your support. But I´m afraid I don´t quite get it. I have used rules before, but only to calculate 'pseudo' measures from existing measures.

      The point is, the old SalesRep (an element in the SalesRep dimension) shall be discarded, and all his existing data in the cube shall now be splitted and spread to the 3 other SalesReps that have been added to the dimension.

      Since sales representatives tend to leave the company sooner or later, I´m looking for a generic way to handle this problem, for it´s likely to happen on a regular base.

    • There is no built-in support for such operation.
      I don't know details, but extracting actual data of the oldSalesRep - and loading them in 3 load jobs back to cube with altered element (new SelesReps) with value divided by 3 in your case. Then you can delete the oldSalesRep element. Suppose there are no other references to it - e.g. in rules.
      Other option is to keep all SalesRep and just shift data from reps who left to others using rules in time periods after oldSalesReps left.
      It is your choice, it all depends on other factors - other rules, subsets, saved reports etd.

    • Hello martino.
      I understand your problem well and solved this situation for my client. It is question of the approach and not function, I think. I have created "competence table of business managers" and with small change in this excel or text file leads trought regular daily etl process to the actual content of the cube without changes of its structure. All sales transaction from whole history are processed in a few minutes every night. It is independent on changes in the organisatonal things. Maybe it can help you.

    • Hi Idivis,

      thanks for your answer. I think it has to be an ETL approach indeed. So I think the following steps are necessary:

      - extract cube slice with sales rep´s data
      - process/split the data in a RDBMS
      - load it back into the cube

      Does that generally match your solution, or do you have a better way?