1.01-08 Daily Timescales on large timescales

rob_marshall

Review the need for daily granularity for long timescales, specifically if the daily timescale spans five years or more.  Use time ranges to restrict the daily calendars if possible and also use PREVIOUS rather than CUMULATE to optimise calculation efficiency.

 

Related to Rule:

2.02-10 Using PREVIOUS vs. CUMULATE

 

Best Practices articles:

• Time Range Application
• Performance Issues with Cumulate Over Lists

DaneClemow

@rob_marshall If you need more than 5 years at daily level (financial services application), what are your thoughts on using a list to stand in as days? With the cumulate functionality now, cant think of too many things absolutely need to use native time for, and if your data is sparse across days, it could reduce the cell count significantly.. Thoughts on which would be better for model performance?

rob_marshall

@DaneClemow

I would be careful using Cumulate as that function can be a performance hog. Are you thinking about creating a list consisting of 1 → 31 and dimensioning that my Native Time at the month level or are you just thinking of creating a list with Days rolling up to Months to Quarters to Year? If you are doing the latter, I would read this article by @MarkWarren and myself (Link).

As for your initial question, without knowing the "shape" of your data, what the transactions look like, what your code for the transactional list is, it is pretty much impossible to suggest a way forward. With that said, a couple of questions you need to ask yourself:

• Does the data really need to be at the day granularity?
• If so, how many modules will be using the daily timescale?
• What reporting will be needed at the daily timescale?
• Are users going to be entering data using the daily timescale?
• What and how big are the other dimensions that will be used with the daily timescale?
• Do you really need all the years at a daily timescale? Would current and possibly current -1 be sufficient?

DaneClemow

Awesome stuff, as usual @rob_marshall . Yes, we do need to calculate long strings of cash flows over many years to calculate IRRs, but luckily Anaplan can do IRRs on cash flows stored in a list, which is what got me thinking in this direction.. And as for cumulate, I have no plans to use the cumulate- I am just saying that with the new sequential functionality its much less likely that you paint yourself into corner with no way out, than it used to be.

As for the list structure- I was thinking of just having a very simple list, say 0-100 that are Cash Flow Day 1, Cash Flow Day 2, etc., and then you can store those daily cash flows using that list in a non-sparse fashion.. So 20 years of data, with sparse cash flows, you could store down much smaller, and hopefully faster than having your model calendar set to 20.. You could always create mapping between this pseudo-time list and native time periods, if you ever wanted to sum data to Monthly, Quarterly or Annual levels, so I dont think I would even have it rolling up to months, but that is an interesting idea about 31 item list dim'ed by months- how is the performance on that? Better than pure daily time scale?

rob_marshall

@DaneClemow

Interesting…As for the performance of 1→31 vs Native Time, it will depend on how it is used. Obviously, you will lose some functionality like lag, movingsum, next, etc. (if those are needed). Also, it will create fewer but bigger blocks (1→31 will be in the same block whereas each native time day will be its own block). Also, I would take a look at the below video that David put together before he left.

https://community.anaplan.com/discussion/93234/modeling-video-series-1-flexible-time-mapping

DaneClemow

@rob_marshall ohh, very interesting- days are their own blocks.. i can see why that is so impactful on performance then.

Thanks for the articles/links- I have already read your write up on Hyperblock, many times, haha, and will watch that video on time modeling.