Cubes structure optimisation or complex feeders

Post Reply
adasko
Posts: 13
Joined: Fri May 16, 2014 12:06 pm
OLAP Product: Cognos Tm1
Version: 10.1
Excel Version: 10

Cubes structure optimisation or complex feeders

Post by adasko »

Hello,
The software used Cognos Express 10.1. and your help needed and very welcome (as I did not find the similar topic).

The simple model to calculate the depreciation of new assets, consists of 2 cubes:
-> BUD_Assets_Attr (2 dimensions: F_BUD_Assets_item + M_BUD_Assets_Attr) where user will input the amount, the starting date, respective cost center and asset category using the picking list (which automatically defines the depreciation time in months)
-> BUD_Assets_Depn (5 dimensions: F_BUD_Assets_Annee + F_BUD_Assets_Mois + F_BUD_CC + F_BUD_Assets_item + M_BUD_Assets_Depn) used to caclulated the depreciation per assets and cost center starting from the starting date indicated in the first cube

To trigger the depreciation in the BUD_Assets_Depn cube, I use the conditional rule (see attached file) with 3 IF conditions ie If year, month and cost center inputed by the user (in the first cube) match with 3 elements of the current BUD_Assets_Depn cube, put the amount / the depreciation time in months. Afterwards the depreciation amount is put forwards across periods with DB function and 'Prev" attribute.

Using the 3 IF condition, the model is of course very slow. So my requested is how to:
- either redefine (optimise) the current model in order to use DB nested function (with additional intermediate cube ? )
- either how to define the feeders for such model (if my solution seems acceptable for experienced people)

I'd like to stay with rules (and not use the TI to copy attributes from the first cube) to see immediately any changes introduced in the 1st cube
I hope this short description is clear enough to get your help.
Thanks
Adam
Attachments
ACorp.docx
(95.91 KiB) Downloaded 265 times
whitej_d
Community Contributor
Posts: 103
Joined: Mon Sep 05, 2011 11:04 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2010

Re: Cubes structure optimisation or complex feeders

Post by whitej_d »

I presume you must have more rules than this, or is this just the starting point?

I've usually used '&' instead of nested if statements for this sort of thing, but to be honest I've never really thought about which one would be faster. Perhaps someone else could shed some light?

I would also consider entering the year and month as string in the input cube and eliminating the need to convert using Numbr.

I don't think you need to if statement to test against zero in the 2nd rule, as long as it is below the first rule.

Be very careful with feeders using this approach as you'll need to feed the next month from the previous and if you have a lot of years and months, you'll get a stack overflow before too long. Then you'll be into a hybrid solution where you have to use TI for the rollover of years and that won't give you the instant feedback you're looking for. If you do stick with this structure, then feed the purchase month and then feed next from prior and so on. The difficulty you will have is stopping the feeder when the asset has expired without a horrible if statement testing the current month against the useful life + starting month for every month, or just feeding all eternity which will be slow.

I would reconsider the design here. I think you'd be better having a internediate calculation cube which uses relative time instead of years and months. T0 is the purchase month and then calculate the depreciation for each month as T1, T2 etc etc. Now you don't need any if statements. Everything starts at T0 and you only need to know the useful life to work out the depreciation. You can limit the depreciation period by using something like

not expired flag = min(1,max(0, (useful life * 12) - numbr(relative time)))

depreciation = (asset value, T0) / (useful life * 12) * not expired flag

The nice thing about this approach is the feeders. You can feed from the input cube to the expiry date of the relative time (eg T36 for 3 years useful life) and then feed back to T0. Then you never feed unneccesary months and you don't need any if statements in your feeders either.

Then you have an output cube with years and months, like your current cube and all you need to do is have a lookup of purchase year, purchase month and relative time in order to convert from your calc cube to feed the output cube from the calc cube and a look up cube which returns relative month from month, year and purchase date for the rule in the output cube.
User avatar
Steve Rowe
Site Admin
Posts: 2410
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: Cubes structure optimisation or complex feeders

Post by Steve Rowe »

You've not documented your feeders at all which will drive the performance to a large extent.
As Joe says I would do your first rule in a single If with an and.

Unless there was a very strong business argument for having the depreciation dynamic I'd do this with a TI process. ;)
Depending on how your submissions are arriving you may have a trigger to run this. In many cases depreciation, being a derivative of things that are planned for, is often not of interest during the budget cycle. It just needs to be filled in when the cycle is complete.

Cheers,
Technical Director
www.infocat.co.uk
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Cubes structure optimisation or complex feeders

Post by paulsimon »

Hi

This looks like a classic example of where it would be easier to use a combined Year-Month, Annee-Mois dimension, instead of two separate Year and Month dimensions, since the depreciation will naturally flow over year boundaries.

I suggest that in your BUD Assets Attr cube you pull in the Asset Class and number of periods over which the Asset is to be depreciated. I think you may already be doing that but I cannot tell what AC and AF are. It looks like AC is the number of months? I would suggest that in this cube you use a rule to calculate the depreciation amount per month ie Value / Number of months over which it is to be depreciated. That will save a little time as opposed to doing the calculation in the main cube.

It is rare that depreciation needs to be a dynamic calc. If so then I would go with the relative periods intermediate cube idea.However, I would be more inclined to go with the TI approach if a dynamic recalc approach is not required.

It is not clear as to whether you are using Skipcheck and Feeders. If not then this would explain the poor performance.

As another tip, I would avoid using Attr in a cube name since this can be confused with an }ElementAttributes cube.

Regards

Paul Simon
adasko
Posts: 13
Joined: Fri May 16, 2014 12:06 pm
OLAP Product: Cognos Tm1
Version: 10.1
Excel Version: 10

Re: Cubes structure optimisation or complex feeders

Post by adasko »

ACorp.docx
(97.08 KiB) Downloaded 257 times
Hello Guys,
Thanks a lot four your advices.

As I see I was not exhaustive enough in the description of my model, so I included (in the attached file) all relevant rules. And I'd like to highlight one point: the model will always include only 2 years: the current (Est) + the next one (Bud) because for each budget calculation only these 2 years are used and analysed (the previous being deleted). I think (hope) this simplifies the problem of the next month from the previous feeding.

For the moment I'd prefer to stay with the depreciation being calculated dynamically (unless the model will be really to slow), mainly for 2 reasons:
- very often the request is to see how the change of the starting period would impact the EUR / HRE rate of the cost center
- no need to completly redesign my current model ;)

As adviced I changed nested if statements to '&' so the rules are much easier to read. At the beginning I defined (cube: BUD_Assets_Attr) the year and month as string but the rules editor did not really like it (Syntaxe error: !F_BUD_Assets_Annee incorrect logical comparison), that's way finally I used Numbr convversion.
Unfortunately, without the if statement in the 2nd rule, the depreciation amount is not forwarded to the next year period :( .

Yes, the BUD Assets Attr cube pulls the number of months (AC) based on the assets category selected by the user.

I think the (slow) speed of this small model comes from the feeders defined in BUD_Assets_Attr cube (2nd and 3d) which feed all 'Est','Tot_Mois','Total CC / type' elements. I dont know whether, with the current structure, it could possible to define more efficient feeders (please advice).

Finally, the relative periods intermediate calculation cube idea could be the good one (unless the more efficient feeders exist). However I do not see (lack of experience I think) how to put data from BUD_Assets_Attr (which includs the month and year starting period) to an internediate calculation cube which uses relative time (ie how to convert T0 period from the intermediate calculation cube to the starting Month and Year period used in my both cubes). Could you please advice (give more details) ?

Thanks a lot
Adam
adasko
Posts: 13
Joined: Fri May 16, 2014 12:06 pm
OLAP Product: Cognos Tm1
Version: 10.1
Excel Version: 10

Re: Cubes structure optimisation or complex feeders

Post by adasko »

Hi every one.

Finally with more data my model is really slow so the redesigned is in process :roll: .

In conlusion the inter cubes rules with if complex statements are not the good idea.

Thanks for your help
User avatar
Steve Rowe
Site Admin
Posts: 2410
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: Cubes structure optimisation or complex feeders

Post by Steve Rowe »

In conlusion the inter cubes rules with if complex statements are not the good idea
This is a pretty sweeping generalisation. There should be no issue with the above in general providing
1. Feeders are accurate.
2. Cube dimension order is optimised for calculation (not always the same as optimising for size).
3. Environment is able to make use of MTQ and other cfg settings have been tuned.
4. Frequency of data change is not high.

What is your definition of "really slow"?
What size in RAM are the cubes involved?
How long is the calculation chain before you get to the area of concern?

Cheers,
Technical Director
www.infocat.co.uk
adasko
Posts: 13
Joined: Fri May 16, 2014 12:06 pm
OLAP Product: Cognos Tm1
Version: 10.1
Excel Version: 10

Re: Cubes structure optimisation or complex feeders

Post by adasko »

Maybe my statement is too extreme but I think when your (or rather my) model is not well designed (well thought out) from the beginning, its complexity should be overcome by horrendous nested if statement in rules which impact the overall performance (that's way I think the additional calculation cube, proposed by whitej_d is a good idea).

"Really slow" means waiting more than 1 minute for the view generation. With few (10) items charged the view generation is quite ok but with 200 items it takes more than 1 minute.

For sure feeders are not accurate but I did not know how to overcome the large overfeeding (what I said in my previous post
"I think the (slow) speed of this small model comes from the feeders defined in BUD_Assets_Attr cube (2nd and 3d) which feed all 'Est','Tot_Mois','Total CC / type' elements. I dont know whether, with the current structure, it could possible to define more efficient feeders (please advice)".

Adam
Attachments
Perf.jpg
Perf.jpg (32.84 KiB) Viewed 4848 times
Post Reply