Pulling columns directly from Dimension table for better performace

#1

Hi Team

We are having a Dundas cube with 7 dimension tables and one fact table which is giving the one final process result in the end. Is there a way to pull the column (filter) directly from the dimension table instead of process result (Fetching column from process result is taking time because it is combination of all 7 dimension and one fact table and it has to perform join and other calculation before fetching data).

#2

What you could do here is use a user hierarchy.

https://www.dundas.com/support/learning/documentation/analyze-data/how-to/how-to-create-a-custom-user-hierarchy

You can base the user hierarchy off the dimension table directly from your data connector (drag the table from your connector onto the hierarchy designer canvas), and then you can go into your data cube and on process result, promote the appropriate dimension to the user hierarchy created.

https://www.dundas.com/Support/learning/documentation/cleanse-consolidate-modify-data/transforms/output/process-result#h1-2-1-replace-with-a-hierarchy

Then at the dashboard level, connect the filter to this hierarchy on your metric set. As such, whenever the filter needs to populate its members, it will source the members from the source powering the user hierarchy, which in this case is obviously just the lone dimension table, which saves on processing power.