Reporting Cube 2

[tryinghard]

I have 2 cubes A and B. B has a one different dimension from A in which about 100 Items from A needs to be mapped to about 20 items in B, its a Many to one mapping.

I can do this by creating Rules in B where I can say this Item1 in B = Item 1 in A + Item 2 in A + Item 3 in A etc. This would be a very cumbersome process, is there a better way to do this.

[Andy Key]

Build a hierarchy in your 100 element dimension to perform your grouping to get you a 1-1 mapping with your 20 element dimension. Then you can have a simple rule in Cube B to pull the data across.

You will also need a method of feeding the data from A to B. You can't use a simple feeder as the elements don't match at the N level.

You can either feed from your 100 elements to their ElPar, which, if you have gone from 100 element to 20 in one level will be an element in cube B, but this will only work reliably if you have a single hierarchy and no one comes along and adds an extra level of grouping.

Or you can add an attribute to your 100 element dimension. This attribute contains an element from your 20 element dimension. You can then feed to the value of this attribute. The downside to this method is that you need to make sure that this attribute gets populated when you add a new element, but as there must be some logic to the grouping, it may be possible to populate this from another data source.

Alternatively, as you say that the only difference between the two cubes is this one dimension, is there a business reason why creating the hierarchy in cube A doesn't solve the problem anyway?

[tryinghard]

Thanks a lot for the response Andy, I have a few follow up questions:

Build a hierarchy in your 100 element dimension to perform your grouping to get you a 1-1 mapping with your 20 element dimension. Then you can have a simple rule in Cube B to pull the data across.

So in this case, I have to create 20 calculations in cube B, or is there a way to create just one calculation that applies to all elements.

You will also need a method of feeding the data from A to B. You can't use a simple feeder as the elements don't match at the N level.

You can either feed from your 100 elements to their ElPar, which, if you have gone from 100 element to 20 in one level will be an element in cube B, but this will only work reliably if you have a single hierarchy and no one comes along and adds an extra level of grouping.

Can you give an example of how I would use an ELPAR in the FEEDER, if the levels do not change.

Or you can add an attribute to your 100 element dimension. This attribute contains an element from your 20 element dimension. You can then feed to the value of this attribute. The downside to this method is that you need to make sure that this attribute gets populated when you add a new element, but as there must be some logic to the grouping, it may be possible to populate this from another data source.

I have been trying to use attributes, but struggling with them since the manual says that ATTRS(dimension, element, attribute) and the element here is one element, how do I apply it to all the elements in a dimension unless I have a formula for each element and in effect 20 Feeders, which defeats the purpose of a Attributes. I can maintain the attributes in this dimension if new elements are introduced.

alternatively, as you say that the only difference between the two cubes is this one dimension, is there a business reason why creating the hierarchy in cube A doesn't solve the problem anyway?

I need to transfer data to new cube since I have to make a few adjustments to the 20 categories, which is not possible or very cumbersome in the existing cube.

Thanks again for the response

[Andy Key]

You only need one rule in Cube B:

Code: Select all

[]=N:DB('Cube A', !<common dim 1>, !<common dim 2>, !<Dimension in Cube B with 20 elements>, !<common dim 4>);

Obviously you need to change the dimensionality as suits your cubes.

Assuming ElPar is ok the feeder in Cube A is:

Code: Select all

[]=>DB('Cube B', !<common dim 1>, !<common dim 2>, ElPar( 'Dimension in Cube A with 100 elements', !<Dimension in Cube A with 100 elements, 1), !common dim 4>);

Using an Attribute as a feeder; define an Attribute on the Dimension in Cube A with 100 elements called Feeder for Cube B and, for each of the bottom level elements, populate it with the parent, i.e. an element within the Dimension in Cube B with 20 elements.
Your feeder in Cube A then becomes:

Code: Select all

[]=>DB('Cube B', !<common dim 1>, !<common dim 2>, AttrS( 'Dimension in Cube A with 100 elements', !<Dimension in Cube A with 100 elements, 'Feeder for Cube B'), !common dim 4>);

Hmm, perhaps I should have used shorter example dimension names...

[tryinghard]

Thanks Andy - you are awesome. I have another query its about allocation and Feeders, would really appreciate your expert advice

I need some help on the feeders:

Source - Cube 1 has 5 dimesions - A,B,C,D,E

Intermediate Cube 2 has 6 dimensions - (As cube 1) B,C,D New (F,G,H)

Target cube 3 - this is final cube in which the dimensions are same as cube 2. In this cube I have calucation that takes data from cube 1 Total of dimension A and one element of E, rest of the diemsions are common.

Basically it is an allocation where Cube 1 has data, cube 2 has allocation profiles and cube 3 is the final cube.

I am trying to create feeder in cube 1 that feeds cube 3; however the problem is that there is no dimnsion F,G and H in Cube 1 and I am targeting total levels of F,g and H, which is making the feeder super slow. I know I can use cube 2 as feeder, but there are certain elements in Cube that should feed to cube 3

The calculation in Cube 3 is woring fine but as expected very slow since it does not have the feeder.

[Andy Key]

If you are doing a standard allocation calculation where you are only going to end up with an allocated value in Cube 3 when you have a profile value in the same cell in Cube 2, I would be feeding from Cube 2.

[tryinghard]

Thanks Andy, the problem is that part of the rule says if there is no allocation in cube 2 then distribute the data in cube 1 equally among all the items in cube 3. For this to run I cannot use the feeder from cube 2 since there is no value in cube 2. For this I need the feeder from cube 1 and I am feeding the total of the 2 dimensions thereby feeding all leafs and making the calculation slow.