Tracking historical data in a live model without making the model huge
I've built a model where end users input information and it calculates outputs. This tool will be completed by end users on a adhoc basis, some will use it weekly or monthly, while others will it once in a while. One of the outputs, shown below, is based on live information in the model:
As we want to keep a record of calculated outputs, I want to develop a process where end users 'submit information' at a point in time. I think the best way to do this, is by creating an action to import the data in the formula based module into a values based module. The values based module would have the same dimensions but also have time (by days) dimension to capture when the date the import was run.
As users will not be using the submit functionality often, I have concerns that this imported module will have a huge size which will mostly be blank. Is there a better way of tracking the historical calculations?
Have you already considered storing the data in a flat module (dimensioned by a flat list that would be automatically updated when a user runs the submission)? Then, you would need to create some filters/report on top of the stored data to allow them seeing the scope of the data they would like to analyse out of all the submitted data (e.g. data submitted at a specific point in time, or for a specific member of the hierarchy, etc).
I usually find that this way of storing data is highly efficient as it would not be stored in a multidimensioned module. Of course it means that you would need to understand what will be done with the submitted data to be able to show it in a nice way.2
Quick suggestion is to use 'Subsets' in your target module. Enable the items in the subset if it has value before you run the import action.