Is it possible to merge several dimension elements into one single element?


    • Is it possible to merge several dimension elements into one single element?

      Hi there,

      in our Customer dimension there are thousands of elements that have made their way into the dimension due to an design error. They are actually not real customer IDs but instead IDs of the customers´ contact persons. Their numbers are identical to the respective customer ID plus a sequential numbering.

      The real customer Acme Co. is listed in the customer dimension with the base element 4711. Additionally there are several contact persons of Acme with base elements like 4711-01, 4711-02 and so on.

      We already consolidated them together by a common attribute in the dimension, but of course it would be much better to reduce the number of base elements by clipping of the consecutive numbers in any way, respectively by merging them together into one final customer element.

      Does anyone know a way to do this, without discarding the whole dimension and cube data? Any hint would be appreciated.
    • Hi OMartin,

      one possible way would be to make an ETL extract of the "wrong" base elements. Then create, if not already existent, the element 4711 and make an ETL load with a table aggregation on the extract that was done before. That way the the element 4711 should contain the sum of the values from each 4711-child element.

    • Hi Björn,

      thanks for your support, but I´m afraid, I didn´t quite get it.

      If I understand you right, I shall start with a dimension extract that selects only the "wrong" elements. Ok.
      The "real" element 4711 already exists, along with it´s wrong siblings (4711-01, 4711-02 etc). All of them have values assigned.
      What would be the aggregation criteria then and what kind of load should I start on that aggregation transform?

      I would appreciate, if you elaborate on that a little. (Gerne auch auf direktem Weg :)

    • Hi OMartin,

      I'm gonna write the bare concept over here, just in case that somebody else has the same question.

      The extract of all the elements should be clear right?
      1. Cube Extract -> Filter as much as you can, e.g. on the year or the version (actual, budget, forecast etc.) wherever there are values. I would exclude the empty cells and select only the base elements. In the end you have to save it as a file in the load process.

      Then you need a file extract for said file.
      1. Then you'll need a field transformation to cut the elements so that they all are 4711 => Groovy function with

        Source Code

        1. String cutChars = theString[0..4]…no-need-to-use-substring/
      2. Next step is a Table aggregation, where you'll put all dimensions in the target (best by order of the cube) and the measure would be the #value

      The result is that it sums up all the values for 4711. Let's say you have the dimenions period, version and customer, you would have for customer 4711 for each period and version only one value (instead of multiple due to the current naming issue 4711-xx)

      Afterwards you can go and delete all the elements 4711-xx, since the values are in 4711. Beforehand a crosscheck is advised ;)