Dynamic Subtotalling for List item subset based on user selection - Module Size Optimization

Yehya123

Hello,

Apologies for the long post, I tried to be descriptive enough!

I've a module in production which has close to ~3 billion cells, (this module alone occupies 25GB of space). I'm trying to optimise this module for space and make it scalable.

The reason for the size **** up is multidimensionality and extreme sparsity of the module data.

the source data for this module(target module) comes from a smaller module(i.e. it has 2 dimensions - Product_Customer List X Time in months)

Source Module:

One Combination List (Product_Customer) X weeks

however when data is brought to the target module, SUM formula is used to expand the data and put it across 2 separate product and customer lists (Product) X (Customer) X Time X Unit of Measure) . However not all combination of product X customer is valid, the invalid combinations have zero value cells contributing to sparsity, however the source module list (Product_Customer Combination list only contains valid combinations), added to that we also have a unit of measure list as the dimension in the target module, used to display the numbers in the unit selected by the user.

The Unit of measure has only 2 items, so the cell count just doubles because of the presence of this extra dimension.

Target Module Dimensionality:

9LE List items:

All these factors is exponentially increasing the current module size.

Current formula to bring data from source to target:

Using SUM the source data is expanded from a single combination list into its two constituent single independent lists

L9 Product|Customer -> L8 Product, C9 Customer and 9LE List:

'CDP.001 Consensus Demand Plan - Core_CM'.Statistical Forecast_CM[SUM: 'LIST - L9 Product Hierarchy - Admin'.'C9 - Parent Group', SUM: 'LIST - L9 Product Hierarchy - Admin'.'L8 Planning Code'] * 'CDP_SYS_Conversion Factor 9LE'.Conversion Factor

The reason for this because in the UX Page, the end users wishes to see the numbers independently across any level of these two lists (L8 and C9) and also toggle between (case or 9LE) Unit of measure.

Now to the Solution:

I’ve figured out a way to optimise the module,but hit some roadblocks on the way.

Let me explain, so the current target module runs against L8 Product,C9 Customer, Time and 9LE/Case list, In the model I already have a combination list for (L8_C9 which only has the valid combination L8 and C9) which we use for some other purpose, The idea is to leverage this existing L8_C9 combination list, to condense the target module from separate L8 X C9 to a single L8_C9 combination list and let it run against time and 9le/case list.

However since it is a single list this removes the flexibility for the user to review numbers independently against any L8 and C9 at a given time, a single product(L8 ) can occur against multiple Customer (C9), so as a work around, plan is to introduced a user selection filters rather than context selector drop down, user selection filter to be formatted with L8 Flat list (product) and C9 flat list(Customer) , and L8|c9 items selected dynamically based on based on a selection of L8 and C9 from users.

Selection logic:

C9 selection boolean:

ISBLANK('CDP.025.FIL User Selection Input'.Parent Customer Group) OR 'CDP.025.FIL User Selection Input'.Parent Customer Group = 'CDP.022.L8 Planning - Channel |C9 - Admin'.'C9 Parent Customer group'

L8 selection Boolean :

ISBLANK('CDP.025.FIL User Selection Input'.Planning Code) OR 'CDP.025.FIL User Selection Input'.Planning Code = 'CDP.022.L8 Planning - Channel |C9 - Admin'.'L8 List'

Final filter : L8 selection booleand AND C9 Selection Boolean.

based on the filter selection of L8 and/or C9 dynamically valid L8|c9 items are selected, so I want to only display the SUM of all these combinations for all the lineitems of the module , (not interested about the values of a single L8|c9 item but their SUMof valid selections based on the user selected values of L8 and C9),

The problem in the solution:

Solution 1 :

The selection logic works fairly well except for the final summing.

For summing only the valid l8|c9 based on selection I used the below formula

CDP Valid Items: IF

    'CDP.026.FIL User Selection Output'.Final Filter CDP[LOOKUP: 'L8|C9 List']

THEN

    'L8|C9 List'

ELSE

    FINDITEM('L8 Planning - Channel |C9', "")

The line item used in the sum formula will only have an L8|c9 item to sum with only if it matches the selection criteria else it will be blank, leaving out the non-selected combination at any time dynamically,

However this will require me to dimension all the line items in the target module with USERS dimension to dynamically sum values based on individual users’ selection, this will again **** the module up in size, which is not feasible

Solution 2 :

I thought of another solution, which is to update the user selection subset dynamically based on the user selection : l8|c9 :User Selection subset which would only have the valid list items based on user selection. The problem with this is, since I only want the sum of the valid list item for all line items I want to know Is there a way to dynamically display the summary values of only the selected items of the subset by users.

Any other solutions to achieve the required functionality is much appreciated as well 😊

devrathahuja

@Yehya123 - I believe you already have the 2 possible solutions in your mind in this case :)

1 thing you can do to further optimize it with the users option is evaluate how many users would really need this report & add a subset of the user dimension to the module instead of the whole list to optimize on the size. In that case you can also remove the case/9LE list as that can just be a user selection.

Yehya123

@devrathahuja thanks for your response,

the issue here is that probably every model user will need to access to this page, so using a user list subset as a dimension, cannot be a viable solution here.