Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Dimension with a huge number of attribute members


We have a Date dimension with an attribute that represents each individual date. We have about 10 years worth of data in our data warehouse which effectively means there are about 3650 individual dates in the Date dimension. As you can imagine, loading all these dates from the SSAS to the PivotGrid takes a significant amount of time. 

We have written our own ADOMD provider for PivotGrid that connects to our domain service which uses ADOMD to fetch information from the SSAS database. This allowed us to change the behavior of the method responsible for returning the dimension attribute members so that it only returns the dates we actually want to analyze. As a result, the MDX query XmlaCommand class generates looks roughly as follows:

SELECT NON EMPTY (Hierarchize(VisualTotals(Hierarchize(AddCalculatedMembers({DrilldownMember({[Date].[Date].[All Dates]},{[Date].[Date].[All Dates]},RECURSIVE)}))),POST)) DIMENSION PROPERTIES CHILDREN_CARDINALITY,PARENT_UNIQUE_NAME ON COLUMNS FROM [Cube]

This MDX query will, of course, return all possible dates in the Date dimension and not just those that we want to analyze. Next we tried changing the method that returns the dimension attribute members so that it returns the dates we want and a few extra ones that will be marked as not being selected. This generates the following MDX query:

SELECT NON EMPTY (Hierarchize(VisualTotals(Hierarchize(Except(AddCalculatedMembers({DrilldownMember({[Date].[Date].[All Dates]},{[Date].[Date].[All Dates]},RECURSIVE)}),{[Date].[Date].&[20010108],[Date].[Date].&[20010109],[Date].[Date].&[20010110],[Date].[Date].&[20010111],[Date].[Date].&[20010112]}))),POST)) DIMENSION PROPERTIES CHILDREN_CARDINALITY,PARENT_UNIQUE_NAME ON COLUMNS FROM [Cube]

This is not much better as it will return all dates except for those few extra ones that were loaded but marked as not being selected.

Looks like the generated MDX query will always return all members with the members not being selected (IsSelected=False) removed by using the EXCEPT clause. Is there any way to change this behavior so that the generated MDX query would return only the members that are selected (IsSelected=true)?