Performing allocations

      Performing allocations

      Hi

      Need some advice on the best course of action to take to perform this allocation.

      In one cube, I have costs by cost centre, product and month. I want to allocate these costs by customer based on sales units which is held in another cube. What would be the best course of action?

      Many thanks,

      Kavee

      CUBE: Production_Costs (3 dims. Cost Centre, Product, Month)

      Dim: COST CENTRE {A...x}
      Dim: Month
      Dim: Product
      J
      F
      M
      A
      M
      J
      J
      A
      S
      O
      N
      D
      Prod1
      2000
      ...










      Prod2
      ...











      Prod3











      ...
      Prod4










      ...
      3500

      CUBE: Sales_Units (3 dims. Product, Customer, Month)

      Dim: Product {Prod1...4}
      Dim: Month
      Dim: Customer
      J
      F
      M
      A
      M
      J
      J
      A
      S
      O
      N
      D
      Cust12
      3
      4
      5
      ...








      Cust2
      2
      3
      ...









      Cust3
      7
      ...










      Cust4
      8
      ...











      CUBE: Allocated_Costs (4 dims. Cost Centre. Product. Customer. Month)

      Dim: COST CENTRE {A...X}
      Dim: Product {Prod1...4}

      Dim: Month
      Dim: Customer

      J
      F
      M
      A
      M
      J
      J
      A
      S
      O
      N
      D
      Cust1












      Cust2












      Cust3












      Cust4












      Files

      Post was edited 1 time, last by “Kaveenga”: included attachment ().

      In fact, you could create a Rule Extract in the ETL, with the same parameters. Once you do that, you assign the rule to a cube and load the rule, just the way you load a cube or a dimension. The assignment can be done in a Rule Load.

      You can copy/ paste the rule from the cube>rule to the Rule Extract.

      Kiran
      Thanks Michael for the suggestions! Got it to work. 8o

      Hi Kavee,
      I would suggest for you to look into the following: -
      1. Cube Extract - You need a Cube Extract for each Cost and Sales Units Cubes.

      2. Table Join Transform - After defining the Tables(Extract) for Cost and Sales Units, you can use Table Join tranform to merge the two tables.
      3. Field Transform with Groovy/Java Script function. Once you have a single table, then calculate for the allocation.
      4. Then Load it into Allocated_Costs Cube
      Regards,
      Michael Maquinto