Filter Dimensions with Many Members

When using filter dimensions with large number of members there is a risk for bad performance due to the time it takes for the filter web part to load all the members. It is a good idea trying to avoid that.

If for example the time dimension in a cube has four levels (Year, Quarter, Month and Day) and only Years and Months are relevant for the dashboard it is possible and recommended to instead of [Time].Members use the following syntax:

Hierarchize({[Time].[Year].Members,[Time].[Month].Members})

By using cross page filtering in combination with hidden filters it is possible to build dashboards where the user will get appropriate data on the dashboard dependent on the URL. If a dimension where the chosen member is given in the URL holds lot of members there is a risk for bad performance if the filter web part has to load all members. It is possible to avoid that by using the _FILTERTUPLE_.Item(<index>) expression as the source in the filter web part (index 0 represents left/top most dimension).

In the example above:

• use the expression _FILTERTUPLE_.Item(0) as Expression for Product to avoid loading members from the Product dimension
• use the expression _FILTERTUPLE_.Item(1) as Expression for Time to avoid loading members from the Time dimension
• use the expression _FILTERTUPLE_.Item(2) as Expression for Warehouse to avoid loading members from the Warehouse dimension