PERFORMANCE CANVAS

The Best Practice in Financial Control - EPM, CPM, Budgets, Reports, Plans, Dashboards, KPI Management, Scorecards & Consolidation

User Tools

Site Tools


pe:filterdimensionswithmanymembers

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.


Use the Hierarchize Function to Specify Specific Levels or Members

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})


Avoid loading all members for hidden dimensions

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
pe/filterdimensionswithmanymembers.txt · Last modified: 2014/03/03 04:07 by pcevli