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!
Seeking advice on Rule (under/over feeding or use TI)
- Martin Ryan
- Site Admin
- Posts: 1989
- Joined: Sat May 10, 2008 9:08 am
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
- Location: Wellington, New Zealand
- Contact:
Re: Seeking advice on Rule (under/over feeding or use TI)
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
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
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Jodi Ryan Family Lawyer
Re: Seeking advice on Rule (under/over feeding or use TI)
I haven't read your whole post, but why write a rule for YTD? Just update your Month Dimension wit YTD elements.
- Steve Rowe
- Site Admin
- Posts: 2455
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Seeking advice on Rule (under/over feeding or use TI)
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
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,
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,
Technical Director
www.infocat.co.uk
www.infocat.co.uk
- Steve Vincent
- Site Admin
- Posts: 1054
- Joined: Mon May 12, 2008 8:33 am
- OLAP Product: TM1
- Version: 10.2.2 FP1
- Excel Version: 2010
- Location: UK
Re: Seeking advice on Rule (under/over feeding or use TI)
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.
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.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
-
- Posts: 31
- Joined: Tue Jun 10, 2008 8:21 pm
- OLAP Product: TM1
- Version: 9.1sp4 64x
- Excel Version: 2003
- Location: Hartford, CT
Re: Seeking advice on Rule (under/over feeding or use TI)
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?
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

-
- MVP
- Posts: 3698
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Seeking advice on Rule (under/over feeding or use TI)
... 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.kbogies wrote:I wrote feeders for each, however, it seems to run much better (and is still correct) without any feedersshould this be alarming?
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

-
- Posts: 31
- Joined: Tue Jun 10, 2008 8:21 pm
- OLAP Product: TM1
- Version: 9.1sp4 64x
- Excel Version: 2003
- Location: Hartford, CT
Re: Seeking advice on Rule (under/over feeding or use TI)
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
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
- Site Admin
- Posts: 330
- Joined: Sun May 11, 2008 4:58 pm
- OLAP Product: Any
- Version: 1.0
- Excel Version: 2020
- Location: Lytham UK
- Contact:
Re: Seeking advice on Rule (under/over feeding or use TI)
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.
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.
John Hobson
The Planning Factory
The Planning Factory