How to populate lookup cube dynamically?

[vladino]

Hi all,
I'm trying to find out how to populate lookup cube dynamically via rule according to user input.

Let's say that user input cube has following dimensionality:
Months
Years
Products
Metrics

One of the metrics is "Durability" as string with static pick list attribute values: 1 month, 2 months..., 12 months, 2 years, 3 years etc. (first year in months and after it only years)

I need to populate lookup cube with following dimensionality:
Months
Years
Products
Metrics

Metrics dimension has only one member, which is "Valid".

Let's say that for specific product user selects "3 years" for durability in intersection with June and 2010. Now I need to populate lookup cube dynamically and set "1" starting in June 2010 and ending in May 2013 for that specified product.

What the rule should look like? Could anyone give me an advice how to do that?

Thanks a lot,
Vladino

[Wim Gielis]

Hi Vladino

Given the time now (past midnight... ) I have not worked out the full exercise. But according to me, this seems impossible. Part of the reason being that you cannot "loop" in rules statements (I mean looping over months). You could use messy IF and AND and OR statements, but either it will prove unfeasible, either you won't get your head around these rules after some time.

You would better go for a second best approach (in terms of calculation - it will not be on the fly) by using a Turbo Integrator process. Also a good zero out will be needed in that process. You can schedule the process at a short time interval.

Unless someone else sees a way to do it using rules

Wim

[vladino]

Hi Wim,
thank you very much for your interest. I'm playing with this for about 3 days without any solution. TI process is the alternative and I think I will move that way because I'm totally desperate playing with rules...

Thank you again, I was waiting for this confirmation to move on.

Vladino

[vladino]

But anyway... Has anyone some recommendation how to do this using Turbo Integrator? Is there any best practice regarding this issue?

Thanks,
Vladino

[jstrygner]

Although I would also probably (facing the real problem) lean towards TI I could not resist of trying to make such an example in rules.

My solution here does not concentrate on efficiency (no skipcheck/feeders etc.), it just shows, how it could be implemented. But maybe in real life your dimensions/cubes are too big to use the "rules" approach.

1. You create dimensions Years, Months and Products in a regular way, for the Months dimension you use names like '1', '2', ..., '12'. The Metrics dimension consists of 'Duration', 'Left' and 'Valid' elements (I needed to add the one in the middle).

2. You create 'Example' cube with dimensions order of 'Years', 'Months', 'Products', 'Metrics'.

3. In a view you pick 'Duration' (my picklist consists of static values 1, 2, ..., 12, 24, 36, 48 to make it simple in rules, of course you could use a description like '3 years' and then convert it somewhere to value 36) and you can see the 'Valid' flag in the same cube (as shown on screenshot 'DurationAndValidExample.jpg').

4. To make it work this way you should put rule like this (checking the "previous month" coordinates could be done via attributes):

Code: Select all

['Left']=IF
  (
  ['Duration']>0,
  ['Duration'],
  IF
    (
    DB('Example',
      STR(NUMBR(!Years)-IF(!Months@='1',1,0),4,0),
      IF(!Months@='1','12',STR(NUMBR(!Months)-1,2,0)),
      !Products,!Metrics)>0,
    DB('Example',
      STR(NUMBR(!Years)-IF(!Months@='1',1,0),4,0),
      IF(!Months@='1','12',STR(NUMBR(!Months)-1,2,0)),
      !Products,!Metrics)-1,
    0
    )
  );

['Valid']=SIGN(['Left']);

I marked in red frames all inserted and all calculated-to-non-zero values. I also did a case when one period is not finished yet and I defined a new period to start (green frame for the new one).

HTH