TM1 Forum

I'm very new to TM1 but have done the IBM 5 day training, but obviously wasn't concentrating enough.

I have a cube called Sales_Margins dimensioned by Months, Countries, Versions, Products and a measure called Sales_Margins. One item in the Sales_Margins dimension is called Volume.
I want to pass data the volumes data into another cube called BuyerVolumes that is a dimensioned by Months, Versions(as in the Sales_Margins cube) and Buyers.

I have created an attribute on Products dimension called Buyer and populated it with the buyers names (eg products A,B,C belong to Jim, D,E,F belong to Bob etc)

My Rule in BuyerVolumes looks like;

[]=N:DB('Sales_Margins','COUNTRIES TOTAL',ATTRS('Products',!Products,'Buyer'),!Months,!Versions),'Volume');

I get a syntax error around the Products bit.

I know I could have the buyers in the products dimension as a hierarchy but that would be too easy.

I get a syntax error around the Products bit.

This is logic, you write your rule in the BuyerVolumes cube, which does not contain the Products dimension, so he does not understand !Products

I know I could have the buyers in the products dimension as a hierarchy but that would be too easy.

You've got it, that's the solution !

fourfive wrote:I know I could have the buyers in the products dimension as a hierarchy but that would be too easy.

Given what you want to do it is really your only option. Your rule would then be:

[]=N:DB('Sales_Margins','COUNTRIES TOTAL',!Buyer,!Months,!Versions,'Volume');

Make sure the names of the buyers in the Product hierarchy are spelled the same as the elements in the Buyers dimension or it won't work.

The rule is incorrect as there is no !Product in the cube you are writing the rule.....

I was once given this advise and its served me well, generally best to keep the data in as much detail as possible then its easy to aggregate later, in this example you would be best to have the buyer as a dimension in in the sales margins cube, this allows for 1 to many and changes in buyer product relationships.
I wouldn't mix/include it as part of the hierarchy for the reasons mentioned, best to keep dimensions clean.

Thankyou all for informed and quick replies, as an aside and probably talking in ignorance could the first question be done with TI ?

fourfive wrote:Thankyou all for informed and quick replies, as an aside and probably talking in ignorance could the first question be done with TI ?

Yeah, pretty much, of course updates won't be real time like they would with a rule. You could use a view of the "Sales_Margins" cube as a data source and for each record, look up the value of the "Buyer" attribute for element of the "Product" dimension. Of course depending on how the data got into "Sales_Margins" in the first place, you might want to populate "BuyerVolumes" from that source.

JDLove wrote:I wouldn't mix/include it as part of the hierarchy for the reasons mentioned, best to keep dimensions clean.

Personally, I don't worry too much about this. I find as long as meaningful consolidation names are used (perhaps with a prefix or similar) and the subsets used in reports etc are well thought out, it doesn't cause too much grief. There will be times when this approach is the way to go.