Alternative way to feed

[hiraniha]

Hi there,

I am looking for some advice on how to improve the time this feeder takes to evaluate during start-up or save, currently about 1 hour. Technically the target cube is fed correctly and the usage is performant, but I am designing a similar set of monthly and weekly cubes for another part of the business and was hoping for an insight from the brains trust of alternative approaches. The Measures dimension is shared and contains about 90 different elements for the variety of Revenue calulations and inputs.

I believe the issue is due to the fact the feeder is effectivly 'looping' through each measure, day by day translating, the translation is effectively fixed but will be looked up on each iteration of measure.

The weekly cube is a 1 to 7 cycle (Sun to Sat), so has a translation cube to map the components Year/Week/Week_Day to Yer/Period/Period_Day and vice versa, one way for the rule and the other for the feeder.

screenshots

screenshots.png (36.94 KiB) Viewed 5841 times

Rule:PN_Revenue_Weekly
[] = N:
DB('PN_Revenue_Monthly', !Version,
SUBST(DB('PN_Year_Period_Week_Day', !Year, 'Input', !Week, !Week_Day, 'F_YYYYMMDD'),1,4),
SUBST(DB('PN_Year_Period_Week_Day', !Year, 'Input', !Week, !Week_Day, 'F_YYYYMMDD'),5,2),
SUBST(DB('PN_Year_Period_Week_Day', !Year, 'Input', !Week, !Week_Day, 'F_YYYYMMDD'),7,2),
!PN_Service, !Measures_PN_Revenue);

Feeder:PN_Revenue_Monthly
['Feeding Versions','Future Years'] =>
DB('PN_Revenue_Weekly', !Version,
SUBST(DB('PN_Year_Period_Week_Day', !Year, !Period, '00', !Period_Day, 'F_YYYYWWDD'),1,4),
SUBST(DB('PN_Year_Period_Week_Day', !Year, !Period, '00', !Period_Day, 'F_YYYYWWDD'),5,2),
SUBST(DB('PN_Year_Period_Week_Day', !Year, !Period, '00', !Period_Day, 'F_YYYYWWDD'),7,2),
!PN_Service, !Measures_PN_Revenue);

Let me know if another further information is required.

Regards

[mattgoff]

Two suggestions:

1) Do all your versions need to be dynamic? A quick way to eliminate a lot of feeders (and calculated cells) would be to freeze of data that's not subject to change. Not sure if you're doing this via your feeder-- it's hard to tell since you're not explicitly identifying dimensions in your bracket notation (btw this is a dangerous habit), but if so you could also limit your rule too.

2) Is there any way to redesign your lookup cube to eliminate those SUBSTs? Apparently they are slow and you have 3x/cell and 3x/feeder. e.g. Could you have individual Year, Period, and Period_Day measures in the lookup cube?

Matt

[hiraniha]

Hi Matt,

Thank you for your suggestions, see my reply below

1) Do all your versions need to be dynamic? A quick way to eliminate a lot of feeders (and calculated cells) would be to freeze of data that's not subject to change. Not sure if you're doing this via your feeder-- it's hard to tell since you're not explicitly identifying dimensions in your bracket notation (btw this is a dangerous habit), but if so you could also limit your rule too.

All the versions are not live, there is a STET rule earlier to handle that, apologies I didn't mention that. I do have static versions that are used to freeze data and they are not ruled. The Feeder is feeding from a parent element that has two live versions, similar for the years, I use this method as it allows flexibility to change the versions and just re-fire feeder without having to change them.

2) Is there any way to redesign your lookup cube to eliminate those SUBSTs? Apparently they are slow and you have 3x/cell and 3x/feeder. e.g. Could you have individual Year, Period, and Period_Day measures in the lookup cube?

On your second point, I have considered this but wasn't sure without going through the pain of implementing it as to whether it would make a difference, as you have independently suggested it, I will try this out and see if it helps.

[hiraniha]

Hi Matt,

I tried the lookup as suggested, it didn't make any noticeable difference to the start-up times.

I have revert to an unconventional solution.

I have used a single measure called Day_Flag, which is fed if any calculation is performed on a given day, this is then the only measure I have used for the cross cube feeder to it's equivalent in the weekly cube. I have then had to effectively overfeed the remaining measures from this flag. As mentioned this does cause some overfeeding but I guess in the grand scheme of things, is a small sacrifice compared to the huge start-up time I get using a more efficient feeder.

[mattgoff]

I tried the lookup as suggested, it didn't make any noticeable difference to the start-up times.

Yes, in retrospect that would only affect rule calculation, not feeder/startup time.

I have revert to an unconventional solution.

I have used a single measure called Day_Flag, which is fed if any calculation is performed on a given day, this is then the only measure I have used for the cross cube feeder to it's equivalent in the weekly cube. I have then had to effectively overfeed the remaining measures from this flag. As mentioned this does cause some overfeeding but I guess in the grand scheme of things, is a small sacrifice compared to the huge start-up time I get using a more efficient feeder.

Is there a reason you're not using PersistentFeeders? Are you using MaximumCubeLoadThreads? If so, how high do you have it set? In my experience, the optimal value of this parameter is somewhere in the 4-8 range, even if I have more cores available. Bigger than this results in slower startup and a massive amount of garbage memory.

Matt

[hiraniha]

Hi Matt,

Thank you again for your response.

I have always steered clear of persistent feeders, for reasons that I'm sure many would have counter arguments for, so won't even mention them.

I do not restart instances for months and months at a time so I guess I am looking for something that can improve the timings slightly, not a workaround to make it instant.

In regards to the MaximumCubeLoadThreads, it too has issues with cube dependencies and incorrectly fed cubes after start-up not to mention the memory increase. So again I steer clear of this 'feature'.

My view in generally pretty simple, build the system to do its job efficiently, then work around all the 'issues' presented by the inefficiencies of TM1 where required, not always revert to the IBM 'workaround'.

I think I will go with what I have done so far unless someone does come up with an idea that can work.

[mattgoff]

[quote="hiraniha"I have always steered clear of persistent feeders, for reasons that I'm sure many would have counter arguments for, so won't even mention them. [/quote]
In my experience, PersistentFeeders has been very stable. I did have one issue related to a rule/feeder change that didn't correctly invalidate the files, but other than that it's been solid. My start times were in the 40+ minute range prior, now < 5 mins.

Matt