We have a module with 6 dimensions including time, we have to make a staging list from this module at combination level to store the previous data.
For this we need only non-zeros combinations to be created in the staging list.
Each line item in the existing module is taking ~10-12 GB space therefore we have the summaries as "None"
Filters are only getting applied to the dimensions that are in rows
Eg. If we have channels in pages and Channel A is selected then combination will only be made for Channel A similarly combination is only getting made for a month that is selected.
Please let us know the feasible solution, treat this as priority as this is really urgent
I would not recommend to have 6 dimensions like this.
Is it possible for you to concatenate the 5 dimensions into a single list covering all possibilities? (not including time).
Then use this concatenated dimension to view/save/record the data.
This then greatly reduces the sparsity,
@NakulAggarwal I work with issues of sparsity and moving information for saving all the time.
There are multiple ways to solve your problem.
Most Viable IMO:
Flatten your hierarchy, I have a list with 660k list items and the model is under 2 GB total. Even with Summaries turned off, their is hidden information that is generated and stored (parents, codes, etc.)
I would take your lowest level out of the hierarchy, build 1 column for each roll up and use that as references via lookups. Apply 1-2 dimensions and use a filter per intersection to reduce the step sizes for the imports that I am about to explain. DO NOT APPLY TIME UNTIL YOU HAVE REDUCED YOUR INFORMATION AS MUCH AS POSSIBLE!
So, your module should be a summary of the total value for the lowest level + 1 dimension, create a view as you were originally working with, and filter out zeros. Import into a new list with a unique ID with the combination of the two dimensions (or replace two with three to reduce steps but increase sparsity)
Take the new list and apply it against the other dimensions. Repeat as necessary. You have your lowest level without blowing out your model and being able to control the intersection generation. You can use that lowest level intersection from to create a boolean filter and now you have fixed your view issue or you can pull in time and generate in a new module based off of this trimmed down list and export to your holding location/import from this to the location that you need.
TWO, can create sparsity issues:
Concatenate dimensional information for the lowest level of intersection you are going to need and create a new list with multiple imports based off of a flag indicating which intersections you are generating and append to the end of the list, with the new intersections per iteration. (2 imports, one to update the 'control' view and one to create the additional append action) repeat process for how ever many iterations you need.
You have created a list, apply time, use a lookup, and filter down via whatever criteria you need. Import/Export into your holding location.
THREE, I will respond to my own post. As you stated this was urgent.