locked
Prefilter dimension for Excel pivot table RRS feed

  • Question

  • Hello.

    I have a cube with very large dimension (over 3 million members), but I need show only significant members (with [Measures].[Count] > 1000) in Excel pivot table. Using of all members is very slow.

    I can use computed set (like Filter([dim].[atr].members, [Measures].[Count]>1000)), but if I use this set in Excel pivot table, I can't filter members of this set, like I can filter members of whole dimension.

    Is there any way to prefilter dimension members before they are used in excel pivot table?

    Ondra

    Wednesday, January 22, 2014 10:53 AM

Answers

  • Hi,

    The only solution I can think of is to build a related to the fact table version of the large dimension. Join the dimension with the fact table and mark these dimension members as fact members in the ETL process.

    One problem with large dimensions with million of members is that BISM MD needs to send down a lot of meta data to the pivot table when clients run queries. This problem gets worse if that dimension is very flat and without hierarchies.

    BR

    Thomas Ivarsson

    • Proposed as answer by Charlie Liao Thursday, January 23, 2014 1:34 PM
    • Marked as answer by Charlie Liao Wednesday, February 5, 2014 1:50 AM
    Wednesday, January 22, 2014 6:03 PM