Hierarchy slow in filter drop down list

#1

Hi,
I have simple dashboard which references one cube and displayed as a table. I have a filter which is connected to a value within the cube which is connected to a hierarchy.
This filter is a 2 step hierarchy connected to 2 values within the cube.
This filter is extremely slow. It appears to be running the whole cube query each time you try to look at the values available within the filter (just to display the values) available within the filter. I would like the filter to simply show all value within the hierarchy. Is there a way to stop this pre cube query?
Cheers,
Jen

#2

I have the same issue as well (my hierarchy is a 3 level hierarchy with 5M rows, and I explicitly chose a cascading filter in the hope that each level could be filtered individually, which would mena a search of less than 1000 rows as a time).

I have a feeling, but haven’t confirmed it yet, that if any data cube in your dashboard is not checked-in, then none of the cached cubes will be used. If I am right and your source database is slower than the Dundas cache it could explain your (and mine) issue.

1 Like
#3

If you’re using a filter directly in the cube by either a manual query with placeholder parameters or simply a filter transformation, it’s not possible to perform any caching or in-memory operations. The reason for this is because the cube essentially changing what it is querying at your reject.

You might want to check how fast a similar query is running when queried directly on your data source and do some optimization on the database. Perhaps create a well indexed view?

Otherwise, consider removing the filter mid-query and then use the in-memory capabilities.

1 Like
#4

I found that the order I link the parameters to the my datasets makes a big difference. If I click the filter to a small data first and then to the bigger dataset the filter performs very quickly.

#5

Yes, the view parameter is initialized with the members of the hierarchy selected first.
One method of having the filter controls dropdown lists load faster is:

  1. Create a dedicated data cube that retrieves only the hierarchies needed for the filter controls. This data cube can be warehoused for better performance.
  2. In the dashboard create a metric set visualized as a data label, for example, and add all the necessary hierarchies. Set this data visualization as hidden
  3. When connecting the filter controls, in the Connect Filters dialog, select first the hierarchy from the hidden metric set created above, and after that select the hierarchies from the visible metric sets needed to be filtered. The filter control will have the dropdown list load the members of the hierarchy retrieved from the dedicated data cube created above.
1 Like