Referencing Line Items from Other Modules/Lists (Performance Questions)
Is anyone able to describe the performance impact (if any) of referencing line items from other modules or lists which have the same relevant dimensionality? Particularly using SUM and LOOKUP operators.
For example, a model may have a Register A and a Schedule B (over time), both of which have the same dimensionality (Product). A third Schedule C has a different dimensionality, say Store, which has an implicit relationship with Product.
Something like total revenue (by Product) may be calculated in Schedule B, and needs to be aggregated up into Schedule C (by Store). Is there any performance impact if the aggregation helper is in a different location to the source line item?
A couple of different ways this could be done is:
Schedule C.Total Revenue = Schedule B.Revenue by Product[SUM: Register A.Store]
Schedule C.Total Revenue = Schedule B.Revenue by Product[SUM: Product.Store]
Schedule C.Total Revenue = Schedule B.Revenue by Product[SUM: Schedule B.Store]
(Where Register A.Store and Schedule B.Store are non-time dependent items returning the Store for the specific Product, and Product.Store is a list property returning the Store for the specific Product.
Many thanks!
Best Answer
-
Hi Kevin,
I've worked on a couple larger sized, models (50-80GB range) that had severe performance issues related to how formulas were written. The location of the SUM line item (or property), as you describe, did NOT factor into performance improvement changes. The fixes usually related to simplifying overly complex / nested IF-Then-Else logic (that was combined with multiple other Anaplan Formula Functions).
Nevertheless, there are folks in the community that I know have worked on massive (sized) models that adhere to a handful of best practices. One of those best practices is to locate list attributes in modules instead of lists. In this context your example of "SUM: Product.Store" (which looks like a list-based attribute in the Product list), this would be called out as less efficient than if that attribute were managed in a module. For the models I've worked on, this best practice hasn't mattered... I suspect that most models are not impacted by this, only the massive-sized ones.
Let's see what other community members add to this dicussion!
Cheers!
Paul
1
Answers
-
Thanks Paul! That lends me confidence to consolidating these aggregation/lookup properties into a single location.
I'm in the same boat, working with a large model hitting over 80GB - my main motivation for this question was to consolidate these SUM/LOOKUP properties into a single location, mainly for maintenance purposes, however did not want to suffer any performance impacts as a result of this.
We had quite a rapid development phase, with a number of Anaplanners working on the model - as such, it was difficult to coordinate what helper properties for aggregations/lookups existed, and where they were.
What we eventually ended up with was (essentially) the same aggregation property in multiple locations, e.g.
Schedule 1.Total Revenue = Schedule A.Revenue by Product[SUM: Schedule A.Store]
Schedule 1.Total Profit = Schedule B.Profit by Product[SUM: Schedule B.Store]
etc, where Schedule A and B had the exact same dimensionality
It's a simplified example above, but the two line items in the SUM statement should (in my opinion) be consolidated into one, in a central location (such as the register for Product).
Thanks again for the answer!
0 -
Hi,
Two notes on your question:
- modules are just a display thing. The property you look up on can be in the same module than your target or in another one, it doesn't matter in terms of performance
- it's better to do SUM over line items rather than list properties (as Paul Said)
- don't do SUM and LOOKUP within the same operation ( tosum[SUM:line item1, LOOKUP:condition]). This results in bad performance. You better do it in two line items even if it takes more space.
0
