Seeking advice on Rule (under/over feeding or use TI)

[kbogies]

Hi all!
Currently i have a rule to calculate YTD, where 4 of the measures YTD=Month (Rule A) and 10 measures aggrigate Months to calculate YTD (Rule B). See sample below of the two scenarios. I've limited to show Jan YTD & Dec YTD rules as well as the feeders for each. This has greatly impacted performance. Can anyone tell me if I'm over/underfeeding? And if so, how might you suggestion I write the rules to better perform?

Also, I'm learning that in order to limit performance issues on a large cube (2GB) for end-users, it might be wiser to use TI to populate the data after loading the Monthly data during off-hours. Can anyone advise what that syntax might look like in the advanced prolog section of TI (using a cube view as the source)?

SKIPCHECK;

### Rule A: YTD = Month
['Actual','Jan YTD','IN FORCE NEW BUSINESS']=['Actual','January','IN FORCE NEW BUSINESS'];
#…………. etc
['Actual','Dec YTD','IN FORCE NEW BUSINESS']=['Actual','December','IN FORCE NEW BUSINESS'];

### Rule B: YTD = Aggregation of Months
['Actual','Jan YTD','New Business Written Premium']=['Actual','January','New Business Written Premium'];
#................etc
['Actual','Dec YTD','New Business Written Premium']=['Actual','January','New Business Written Premium']+['Actual','February','New Business Written Premium']+['Actual','March','New Business Written Premium']+['Actual','April','New Business Written Premium']+['Actual','May','New Business Written Premium']+['Actual','June','New Business Written Premium']+['Actual','July','New Business Written Premium']+['Actual','August','New Business Written Premium']+['Actual','September','New Business Written Premium']+['Actual','October','New Business Written Premium']+['Actual','November','New Business Written Premium']+['Actual','December','New Business Written Premium'];

FEEDERS;

### Feeder A: Month => YTD
['Actual','January','IN FORCE NEW BUSINESS']=>['Actual','Jan YTD','IN FORCE NEW BUSINESS'];
#................etc
['Actual','December','IN FORCE NEW BUSINESS']=>['Actual','Dec YTD','IN FORCE NEW BUSINESS'];

### Feeder B: Most current Month => YTD
['Actual','January','New Business Written Premium']=>['Actual','Jan YTD','New Business Written Premium'];
#................ etc
['Actual','December','New Business Written Premium']=>['Actual','Dec YTD','New Business Written Premium'];

Thanks a bunch!

[Martin Ryan]

Actually I think you might be underfeeding.

In any case, I'd try another approach. I'd set up all the YTD figures as consolidations. E.g. YTD Mar Conso = Jan + Feb + Mar. That would make your rules much more readable.

SkipCheck;
['Actual','Dec YTD','IN FORCE NEW BUSINESS'] = ['Dec']; (only specify what's different on the right side of a rule)
['Actual','Dec YTD','New Business Written Premium']=['Dec YTD Conso'];
Feeders;
['Dec', 'IN FORCE NEW BUSINESS'] => ['Dec YTD'];
['Dec YTD Conso', 'New Business Written Premium']=> ['Dec YTD'];

Martin

[par3]

I haven't read your whole post, but why write a rule for YTD? Just update your Month Dimension wit YTD elements.

[Steve Rowe]

Yeah it looks to me that you don't need rules to do this at all, rules are much much slower to calculate than a consolidation in a dimension.

Also you really need to include the N qualifier in the rules so that they only apply to the bare minimum of cells.

What this looks like though is classic case of some of the underlying data being stored as YTD positions and others as monthly movements, how do you then get the data to show correctly at the YTD position in the cube.

There are two options.

The simple 1 is to convert YTD data to movements as you read it in, if all the data in the cube is stored on the same basis (i.e. movements) then a lot of things become more straight forward. You can just have consolidations for periods and you will also have a smaller cube since you are storing movements and generally this tends to be a sparse data set where as YTD values are nearly always a dense data set.

If you go down this road you'll need to think about how to handle opening balances for the year and so forth.

Option 2 using rules
Still create in your period dimension a YTD period consolidation, where Feb YTD is the sum of Jan and Feb and so forth. In this case though you need to override the consolidation for those values where you have stored the YTD position.
Create an attribute against the period dimension called closing period.
Populate the attribute for all C level period elements, so Feb YTD would have Feb and Full Year would have Dec.

Write a rule like this

Code: Select all

['Actual','IN FORCE NEW BUSINESS']=
#This rule should only be evaluated at the C level in the cube
C:
#And only for C level elements of the period dimension
If (Ellev('Periods', !Periods)>0.
#THEN we only want the closing balance for the period
     DB( 'ThisCube' ,leave everything with !Dimension,
#Substitute !Periods reference for attribute reference
        Attrs ('Periods', !Periods, 'Closing Balance'),
     blah rest of DB statement),
#ELSE we are at the N level of the periods dimension and normal consolidations should apply
     stet);

This rule doesn't need feeding as it is a C level rule.
So if you create the consolidations in the period dimension this is the only rule you should need. If you need it to apply to multiple elements then use the { ‘Ele1’, ‘Ele2’} notation.

……………………..
Some general tips for rule writing since if you have a 2 GB cube that is slow performing then it’s probably because of your rules.

1. Consider if you really need a rule, try and do things with consolidations. Rules that have a “+” or a “-“ in them can often be done with a consolidation in the dimension.
2. Most rules should have the N: qualifier, if you don’t have the N: qualifier then you are overriding the consolidations of the cube.
3. Consolidations are something like 10x faster than a rule, so the golden rule is don’t write a rule if a consolidation will work.

HTH cheers,

[Steve Vincent]

another way to create the hierarchy is do do something like;

Jan YTD = Jan
Feb YTD = Jan YTD + Feb
Mar YTD = Feb YTD + Mar
etc etc...

As mentioned tho, its all dependant upon if the data is loaded as monthly values or movement or positions. My example is based on the data being the monthly values, in the end it depends on what the users want to see.

[kbogies]

Following up....

I took your suggestion(s) and created the YTD as consolidations, and wrote rules for the measure where the exception is needed i.e.
['Feb YTD','In Force New Business']=['February','In Force New Business'];
['Mar YTD','In Force New Business']=['March','In Force New Business'];
['Apr YTD','In Force New Business']=['April','In Force New Business'];.......

I did not specify the C: vs N: level since the rule is applicable to all levels of the other dimensions. Also, I wrote feeders for each, however, it seems to run much better (and is still correct) without any feeders should this be alarming?

[lotsaram]

I wrote feeders for each, however, it seems to run much better (and is still correct) without any feeders should this be alarming?

... depends. Have you unloaded and reloaded the cube or restarted the server since removing the feeders? (as this is the only way to clear fed cells.) Once fed a cell stays fed for that server session, even if the feeder is removed unless the cube is unloaded from memory.

Also, depending on the exact nature of your rules (I admit to not reading the rest of the thread and your original question in detail) feeding may not be necessary C level rules in general do not require feeding as long as there is underlying data at N level below the rule calculation as the purpose of feeding in the first place is to mimic having physical numeric data in cells to alert the sparse consolidation algorithm. Therefore in your case you might not need to feed the rule as there is actual data in the model that you are overwriting with a calculation. The rule is C by default as the YTD nodes are consolidations (there are no leaf cells in the area statement) so technically you should probably add the C: qualifier to the rule (mainly for the sake of correctness.)

[kbogies]

yes, the cube has been unloaded and server restarted many times since, so with that said, i think i'll be ok...

after looking into this issue, i realized that a former employee turn consultant wrote some similar rules without feeders in another application. i have been under the impression that rules should always have a corresponding feeder. this new enlightenment could be dangerous though... a life w/o feeders! jk

[John Hobson]

If your cube is quite small and dense then then the overhead of writing and maintaining feeders mat well outweigh the benefit that you get from them.

Be aware though that although it is quite legitimate to NOT have feeders, certain functionality doesn't work properly without them - you need to be very careful with .cma cube exports and view creation in TI.