TM1 Forum

Hi,

I have the following problem with creating rules for cubes with different dimensions:

I have one cube that uses Date dimension which is build up by days and the other one uses year and month (so no days in there).
Also I have a product dimension in one cube which is build up by sku's and the other one only has 10 categories, so high level.

Do I really have to specify for each month, and each category what goes in where?

['Actual','FY11','Jan','Cat1','Sales $'] =
DB(Product)','Actual','JAN-2011','Cat1','Sales');

['Actual','FY11','Feb','Cat1','Sales $'] =
DB(Product)','Actual','JAN-2011','Cat1','Sales');

Rest of year

AND

['Actual','FY11','Jan','Cat2','Sales $'] =
DB(Product)','Actual','JAN-2011','Cat2','Sales');

['Actual','FY11','Feb','Cat2','Sales $'] =
DB(Product)','Actual','JAN-2011','Cat2','Sales');

Rest of categories.

Surely there is an easier option that this because with 12 months and 10 categories I have 120 of these lines of code, and that is for only one measure: sales. I have cost of sales, margin and many others as well.

I need some kind of loop function or is there an other way?

Any help is appreciated.

Cheers

You definitely do not need many separately hard coded rules!

All that is required is a lookup cube to convert between year - month in one cube and date in the other cube, then reference the lookup cube in the rule.

There's a bit more to it, I would suggest.
As well as the lookups for the rules, you'll need to build lookups for the feeders. Remember feeders only do anything at base level.
What I think you are doing is a 2-way summary:
JAN-2011 is a total of the days in January
Cat1 (in the source cube) is a total of SKUs.

The _rule_ will pull in the totals (once you've linked ['FY11','Jan'] to ['JAN-2011']). (Make sure you use n: - this is almost always what you want.)
To get them to _feed_, you will need links from the days under JAN-2011 to ['FY11','Jan'] and also from the SKUs under Cat1 to Cat1. Attributes are the safest bet for these unless you have only one parent in the respective dimensions.
If you don't do the feeders, your destination cube won't rollup properly, and the numbers will disappear in a zero suppressed view.

I've done a lot of these over the years. I remember my frustration when first encountering the 'feeders only work at base' issue, which (then) was not documented, and my deep satisfaction when, in a meeting with Martin Richmond Coggan, he said, firstly, 'everyone knows that' (the Gnostic knowledge approach still all too prevalent in the TM1 world) and then 'but it's on the knowledge base' - and it wasn't.

HTH

Hi David,

Thanks for that. However, it did confuse me a bit.

'What I think you are doing is a 2-way summary:
JAN-2011 is a total of the days in January
Cat1 (in the source cube) is a total of SKUs.'

That is spot on but I am not sure about the linking part of all SKU's. we have about 800k sku's so looks a bit complicated....

I think it might be easier to set up a whole new TI process to populate the cube by category and FY/month!?

So SQL on product level and roll it up (categories are defined in TI process for product dimension) to category.

Thanks for your help.

Cheers