Rules to pull data from one cube to another when dimensions do not match

[wigglyrat]

Hi -

Thank you in advance for any advice provided.

I am faced with the task of writing a rule that will pull measure data from one existing cube to a new cube. Out of seven dimensions, the new cube has only four of the same dimensions that the existing cube does. How do you best determine, when one dimension is not in the other cube, telling TM1 what part of the cube you want to feed it to?

Apologies for the newbie question.

Thank you for any assistance,

[Wim Gielis]

Then you carry over the value of a consolidated element like "Total [dimension name]".
Like "Total Vendor" could be the consolidated element that adds up all elements in a Vendor dimension.

[lotsaram]

Your post is a little confused and it isn't clear
- is the rule in the 4D cube and pulling from the 7D cube?
OR
- is the rule in the 7D cube and pulling from the 4D cube?

I assume it is the former.

If it is the former then as Wim said you just reference 'Total' on the 3 missing dimensions and you would either feed from the 7D cube as normal only on the matching dimensions, or feed from a logical measure to feed from within the 4D cube.

If it is the later then on the extra dimensions does the data go to one unique location (and consolidate?) or is it like a repeated reference value and apear the same for all members of the missing dimension? In such a situation best NOT to feed into 'Total' on the extra dimensions as this can result in overfeeding and poor performance. Always better to feed from a populated measure withing the cube with more dimensions.

[wigglyrat]

Thank you for your help.

I have one cube that has been created in order to give managers a view into performance of sales people. This was created separately, as security will be later applied so that only managers can view this data. There is one dimension 'sales person' that is in this new Test cube that is not in the Existing cube. In the Existing cube, there is a dimension, Client that lists clients by number. In the Test cube however, sales people have been linked to clients via a TI, so it is already set up so that the managers can see which sales person looks after which client.

The measures that I want to pull into test are in the Existing cube. But the Test cube has the following dimensions:

sales person
class
month
year
sale type
measures

The Existing cube shares

class
sale type
month
measures (this is the data I want to pull in)

and also has

client (this is already now found in the sales person dimension in Test, via the TI)

and two other dimensions that do not appear in Test.

Please let me know if this helps clarify matters or if any further detail is needed.

Thank you again for your assistance.

[Paul Segal]

If you already have a TI working that identifies which sales person is responsible for which client, then (assuming there is a one to many relationship sales person->clients) would it not be worth considering making the whole thing a TI? Then you have each client as an element of the relevant sales person, copy the data via TI, and possibly no need for rules at all.

[tomok]

The advice is pretty much the same as has been given already. I don't like the terms "Existing" and "Test" because all they do is confuse the issue. Let's call them "From" and "To". When you have dimensions in From that don't exist in To, you have to have a hierarchy in them that adds it to a total. You then hard code this element in the rule. You didn't tell us what those dimensions are so I can't give an example. When you have dimensions that exist in To and not From, then you need to hard code the element in To where the values in From should go. In your example this would be Year. You have to hard code the values to go to a specific year like 2015 or 2016 or whatever.

The tricky part is this supposed relationship between sales person and client. if there is a many to many relationship between sales person and client, then forget about it. Nothing will work. If there is many to one relationship between sales person and client then forget about it, won't work. The only relationship that will work is a one to many relationship between sales person and client. To make this work via rule you'll have to create a hierarchy in Client that rolls each client to a parent that has the same name as the sales person element in SalesPerson.

The end rule in To would look something like this:

['2015'] =N:DB('From', !Class, !Month, !Year, !SaleType, "TotalFromMissingDIm1', 'TotalFromMissingDim2', !SalesPerson, !Year, !Measures)

Since you didn't provide the order of the dimensions in From you may need to change the order of the parameters in the DB formula but you should get the idea. If you can't build this sales person hierarchy in Client then TI is going to be the only answer unless you want to hard code the the relationship individually in the rules but I wouldn't advise it.