sending data from a detail to consol cube with diff. dims

[ssp]

Hi,

I was just writing a rule to send 'qty' amount from a cube, which stores the qty by date - e.g. 1994-08-20 to a consol. cube, which has two dimension year and month.

So Cube 1 "orders" has:
Customer
Product
employee
scenario
shipper
days
measure

So Cube 2 "consol orders" has:
Customer
product
employee
scenario
shipper
month
year
measure

element names in the "days" dimension are as "1994-08-20"
element names in the "month" dimension are as "M08", "M09", "M12".
element names in the "year" dimension are as "1994", "1995" etc

Note: Measure dim stores quantity
How would I attack this one?

[Martin Ryan]

In the Days dimension create consolidations like
'M08 1994' made up of 1994-08-01 through to 1994-08-31
'M10 1996' made up of 1996-10-01 through to 1996-10-31

In cube 2 a rule like so:

Code: Select all

['Qty'] = N: DB('Cube1', !customer, !Product, !Employee, !Scenario, !Shipper, !Month | ' ' | !Year, !Measure);

That will retrieve the value from the consolidation by concatenating the Month (e.g. 'M08') and the Year (e.g. '1994'), which will match the consolidations you've made in Days dimension.

In cube 1 you'll need a feeder like so:

Code: Select all

['Qty']=>DB('Cube2', !Customer, !Product, !Employee, !Scenario, !Shipper, 'M' | subst(!Days, 6, 2), subst(!Days, 1, 4));

"subst" takes values from inside a string.

Just another thought. If you're building this second cube as a reporting cube, you might wish to use Turbo Integrator and make it a batch process. This will make the reporting faster as the numbers will be stored, rather than a dynamic rule link that has to go and retrieve the values from cube 1. The downside is, of course, that the numbers are static, not dynamic.

HTH,
Martin

[ssp]

Hi,
your suggestion worked. Thanks for your help. the mistake I made was having two level consolidations before the date element in:

1994 -> M08 -> 1994-08-20, 1994-08-21 etc

So after your suggestion, it is MO8 1994 -> 1994-08-20, 1994-08-21 etc

So by doing this i have the same dimensions according to TM1 going into the consol cube. TM1 automatically sees Month |' '| Year and recognises to split that when pushing data into the Consol cube into two dimensions, "month" and "year".

[Martin Ryan]

You can still have two levels of consolidations. E.g.
2004
-- M01 2004
-- 2004-01-01
-- 2004-01-31
-- M02 2004
-- 2004-02-01
-- 2004-02-28

I.e. days rolling up into months (with years, to distinguish them from one another, e.g. M01 1994 vs M01 1995) up into years.

You've hit upon a classic conundrum with TM1. Does one set up the time design with one dimension (days rolling into months into years) or with three (days, months, years all separate)? The answer depends on your situation, as you're discovering.

Martin

[Martin Ryan]

TM1 automatically sees Month |' '| Year and recognises to split that when pushing data into the Consol cube into two dimensions, "month" and "year".

Not quite. On the left hand side of your rule you have

Code: Select all

['Qty'] = N:

In fact what you have hundreds (depending on how many dimensions and elements you have) of formulae within that little ['Qty']. One example of the formulae you have is

Code: Select all

['Qty', 'M01', '1994']

This is because you haven't specified the month or the year, therefore the rule applies to ALL months and years. (You have specified 'Qty', so the rule will ONLY apply to 'Qty' in the measures dimension).

This particular formula example will translate at runtime to

Code: Select all

['Qty', 'M01', '1994'] = N: DB('Cube1', !customer, !Product, !Employee, !Scenario, !Shipper, 'M01 1994', !Measure);

Note that !Month | ' ' | !Year has been translated, this is because whenver you use !Dimension on the right hand side of the rule, the rule knows to use whatever is on the left hand side of the rule*. In this particular formula that is 'M01' and '1994'. In the next split second the rule will do the formula for 'M03', '1995' and put the value in there.

I hope that makes sense. It's quite a confusing concept, but the two basic premises are
- if you don't specify what element the rule applies to on the left side of the dimension, it applies to ALL elements in the dimension (the left side narrows your rule)
- unless you specify a different element to use on the right side of the rule, it will automatically use whatever element it is using on the left side

Martin

* When you do simple internal cube rules you don't even need to say !Dimension, it just knows. When you do intercube rules or more complex internal cube rules you need to specify the dimension order.

[ssp]

Martin,
your explanations have been most helpful. My other question is can I use an attribute as well. So instead of having consolidations of each date rolling up into month and year I create a month and year attribute against each date and use the ATTRS function in the rule?

[Martin Ryan]

You can for the feeders, but not the rule. Each day in cube 1 will be fed to a specific month and year in cube 2. That month and year information can be stored as an attribute in the 'Days' dimension.

But the month and the year are both required in cube 2 to work out where to go in cube 1 to retrieve the value from. I.e. in cube 2 you can't store the information required against ONE dimension, which is necessary for attributes.

Cheers,
Martin

[ssp]

I have to bother you once more martin. thanks for your help so far. much appreciated.
intercube feeding is fine if you have the same element names in both the source and target dimensions. What about a scenario say, where I have two cubes.

Cube 1 stores unit prices by Product Category
Cube 2 is the sales cube, which has Product Categories rolling down to SKUs.

Now cube 1 will have a 'product category' dimension
cube 2 will have a 'product by sku' dimension rolling up to categories.

My rule statement currently feeds unit prices from cube 1 to cube 2 but only to its equivalent product category name and doesn't roll down the unit price to each SKU. For this to happen what is the approach?

[Martin Ryan]

No problem, that's what we're here for.

Are your prices all the same for the SKUs in each product category?

In Cube 2
['Price'] = N: DB('Cube 1', elpar('SKUs', !SKUs, 1), !OtherDims);
OR
['Price'] = N: DB('Cube 1', attrs('SKUs', !SKUs, 'Product Category'), !OtherDims);

The second one is probably better, in case you have multiple parents for one SKU, as you might get unpredictable results from elpar.

Feeders in cube 1.
['Price'] => DB('Cube 2', !ProductCategory, !OtherDims);

By feeding to a consolidation you automatically feed it's children. It's a shorthand (there's another recent thread on that somewhere in the TM1 forum).

Cheers,
Martin

[ssp]

I tried exactly that but the values are not coming through to Cube 2. So essentially I'm entering prices at a category level in Cube1 and wanting to see the price in Cube 2 flow down to each SKU pertaining to its category. I have an attribute against each sku giving the product category it belongs to.

Any suggestions as to what I can look for to get it working? There are no errors showing btw.

[ssp]

Hi Martin,

The problem was that the values I was populating the Attribute, "Product Category" with were not the same as the consolidation name in the hierarchy. So TM1 was trying to do a lookup via the attribute value against the consol element in the Price Cube but both were not matching. ie the attribute values were missing the prefix of it's code e.g. 'Drinks' instead of '1-Drinks'.

thanks for ur help.
Sam