Dashboard Tips and Tricks - unique value pairs in dashboards

[former_member184494]

Very often we build a lot of dashboard with a lot of graphs and tables. Once the dashboard goes live , the users are happy and start using the same. But once the transaction data driving the same grows ... some of the pains of the users come through regarding report filtering and navigation.

One of the common pain points are - in a dashboard which shows summarized information , there is a lot of noise in the form of additional values in the dropdowns for which no data exists.The main reason for this is twofold , namely ....

1. The dropdowns are based off the cube and are not interlinked. Each dropdown is a list of values in the cube , and when the user selects one value , the other does not change intuitively. An example is highlighted below.

In this diagram , the issue is that the unique values are represented correctly , but if you look at the dropdown behavior and the transaction data , it becomes clear that you cannot restrict the user from selecting Plant B and Material Y for which no data exists. If you have Historical data and additional drilldowns , multiply this value set by 100 and you have a problem of :

1. Training users to know what values to select - someone in the business will know that they dont sell Material Y in Plant B and will stay away from it , but someone who is using this for the first time will have a difficult time learning the same and might get put off from using the dashboard

2. Since these values are from the dimension tables in the cube  it becomes very hard to purge values which are not used in the dimension tables without routine maintenance

3. If the dashboard goes on to be used in mobile devices etc , the user has to scroll through a lot of values to get to what they want.

This can be solved / mostly addressed by using unique key value pairs

This approach would entail building an additional cube on top of the transaction cube which holds the unique combinations of the filters from the transaction data.

The create a query for all the drilldowns together and then link them to the dropdowns. You could do this with a query on the transaction data as well but then given the larger volumes , this would be faster. Here if the user selects Plant B , h/she would then see only Material X thereby removing the dependency on the user to know these combinations in advance.

But then the catch with this approach is that because they are separate queries / data sources . you would then have to pass the values via javascript to the main data so that it too gets filtered, this problem is avoided if you use the same transaction data query for your filters if performance is not a concern.

Hope this was helpful ...

Do let me know your thoughts on the same...