Adding a user-specific selection to a calculation without using the User list?
Hello, i am trying to find a solution to the following user story:
There are a set of modules for a reporting page that currently consist of multiple dimensions (we cannot use a combined list of only possible combinations due to the users not liking the loss of flexibility). These modules return a calculation that needs to be affected by four user-driven booleans that change the underlying calculation.
The current setup's problem is the booleans are not user-specific and thus if one user makes a boolean selection to change the calculation, this will affect another user's work due to the numbers changing. If we want the calculations to be user-specifically changed, we need to add the user dimension to the modules (which will result in a massive explosion of space consumption which we cannot afford).
Is there a way we can make the booleans user-specific without exploding the model, without changing the current dimensionality of the modules to a combined list, etc? I've thought about replicating the KPIs in a separate module (that will work as a LISS) that has every possible combination of the tickboxes used, and to filter the metric we want to show based on the tickbox selection, but this has a similar space increase, so it's not a viable solution.
I have faced a similar issue but with FX where the original build calculated the correct output based on a currency selection. Like you the selection was not user specific so resulted in all users seeing the same calculation and not one which was specific to them.
We solved this by not incorporating the users list into the output module but the currency selection. We then allowed the user to select the correct reporting by choosing the relevant currency in a context selector.
In this example we did not create any additional sparsity as we used categories such as, 'local, reporting and group' so that all combinations of dimensions contained valid data.
However, if the boolean selection is a list or you are able to convert it into a dimension you could add it to the module and then filter the view based on the boolean selection. If you add users just to the selection you could use 'current user' filter settings to generate a unique filter for each user.
You will be faced with the problem of how you deal with summary levels as these will not re-calculate based on what the user chooses to filter but will display the summary value for all child items not just those shown.
Another option is to create a list which combines all possible combinations that the user can select from and then add this to the module. Calculate and limit the view by using the same user based filter as described above.
Lastly, I would revisit why you can not use a series of combination lists to show all the required angles of the data. Your client may enjoy the flexibility of including all the dimensions in a single module but the same outcomes can be achieved over a series of related module which use a variety of combination lists to derive the same outcomes. Your model will be far more efficient and will be easier to maintain when it goes live.
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA
The example you give is close to what i imagined - having all combinations of the selections, then make the user's choice filter what KPI we use in the front-end (if i understand your proposal correctly). This will not work in our case as it will create almost the same sparsity as incorporating the user dimension in the calculations.
If we cause the filtration to adjust the user's items in the UI view, indeed the summary levels will not work properly, and this is one of the key benefits our superusers praise the most in the report(s).
Which brings me to the answer for your last question - you are absolutely right that a series of combination lists as core dimensionality for the module setup will be beneficial, and can solve the same user stories the same way as separate lists, however the model is already live (i wasn't the original builder, thus i cannot say why the original builder hasn't chosen this method) and reworking it to this approach requires resources that cannot be spared at this time.
Re: Adding a user-specific selection to a calculation without using the User list?
@ADenchev the basic reason why you have this issue is that a multidimensional database cannot show/calculate the samecell value differently per User or any other criteria.
This is why it is needed a new dimension (Users or another List) in order to differentiate various calculations per cell. Different values can be shown/calculated on different cells.
It is needed to create more cells in order to calculate different results based on different criteria.
From here comes the "explosion" by the creation of a lot of cells in that module by adding the Users list or the ALL possible combinations List.
A solution to your case could be still the creation of a dimension with all possible combinations but you create also a Subset list on this List.
In the report module use the Subset list in order to have under control the sparsity of the module.
The User in the dashboard will need to choose a combination of criteria... If the combination is already present in the Subset, then the user can already see the final results.
If the combination is not present in the Subset, ask the user to launch an action in order to activate the combination in the Subset and then show in the report the final result related to that combination.
So, the idea is to have in this Subset only the last X combinations chosen by the users.
Not sure how many elements this subset could contain in order not to create the "explosion" of your model. I hope it will give you enough space to make a roll-over of the last 10 - 15 combinations chosen by the users.
This will give the impression to the user that he/she can see the results without affecting the other users that will look at the report at the same time.