Workarounds To Sum Data While Using NONE as Summary Method Within Source Module

jczimbal

As the Anaplan model size capacity has recently grown from roughly 18 billion cells to 100 billion cells with the introduction of HyperModel, it is still important to structure models efficiently, while creating as little 'wasted space' as possible.

The Planual states that the Summary Method for most line items should be set to None. This is a great rule to follow—unless:

• The totals need to be displayed on a dashboard/page.
• The data needs to be aggregated.

Let's assume we have several levels in a cost center hierarchy as shown below:

- L1 Region

  - L2 Sub Region

    - L3 Business Unit

      - L4 Cost Center

Let's assume we have a source module dimensioned by Accts, L4 Cost Centers, Months, and a single line item where NONE is the Summary Method.

Now, let's say we have a target module dimensioned by Accts, L2 Sub Region, Months, and some line items.

If we simply reference the source module without any functions then this will not roll the data up (see above line item on the left). A quick way around this is to use the SUM formula and reference an SYS module that has the mapping of the L4 Cost Centers to the L3, L2, and L1.

In this example, the formula would be Source Data.'Amt (No Summary)'[SUM: 'SYS L4 Cost Center'.Sub Region]. Simple enough, right?

But what if the target module does not contain a higher level dimension of the L4 Cost Center structure? A workaround is to create a one-item dimension...

 

...and map it to the L4 Cost Centers list within the L4 Cost Center SYS module.

Whereas the L4 Cost Center mapping to L3, L2, etc. is based on the composite structure and uses the PARENT formula, the mapping to this one-item list is just OneItemList.MemberOfOneItemList.

Then, in order to sum the data within a target module, the one-item dimension must be included in the structure and reference the L4 Cost Center SYS module for the one-item mapping via a SUM formula.

If it is not desired to have the one-item dimension in the final output module, then the above module can be used as the source in conjunction with a line item to select the 'Dummy Total'...

...and then feed into a target model that references the staging module using a LOOKUP statement to point to the above line item.

Depending on the number of levels and size of the dimensions for the source and target modules this can save a lot of space, roughly 30-70%. For small modules, using this approach is likely not worth the effort, but this can provide tremendous space savings on very large modules.

MarinaKetelslegersNumlix

How would you sum months into years without summary method?