Reporting Services, MDX, large dimensions, and dimension properties.

I am using Reporting Services against a SSAS 2005 (SP2) database.

I have a very large Items dimension (about 2.8 million items), which has a hierarchy of Category - Family - Series.

I need to produce a report that has monthly orders for all items (well, it has more than this - past sales and current inventory and vendor purchase orders, but I'll simplify the scenario here).

I need to show the item number, description, series, family, and category on the report, plus the month of sales across the columns.

The query builder in SSRS designer gives me this MDX:

SELECT

NONEMPTY { [Measures].[Order Quantity] } ONCOLUMNS,

NONEMPTY {

([Items].[Category].[Category].ALLMEMBERS *

[Items].[Family].[Family].ALLMEMBERS *

[Items].[Series].[Series].ALLMEMBERS *

[Items].[Item Number].[Item Number].ALLMEMBERS *

[Items].[Description].[Description].ALLMEMBERS *

[Time].[Month].[Month].ALLMEMBERS ) }

DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWS

FROM [Consolidated] CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The problem is that the cross-join for all those Items attributes quickly overwhelms the 4 billion tuple limit. (2 million item numbers * 2 million descriptions * 50 categories * 50 families * 250 series = a big number)

If I add filter parameters to limit this, the query designer puts them in the FROM clause, but I don't know when the cross join is performed - from the resulting filtered FROM cube? It doesn't seem to do that.

In my reading on MDX, I see that there are dimension properties that seem like just the ticket to get around this tuple limitation...

SELECT

NONEMPTY { [Measures].[Order Quantity] } ONCOLUMNS,

NONEMPTY {

[Items].[Item Number].[Item Number].ALLMEMBERS *

[Time].[Month].[Month].ALLMEMBERS ) }

DIMENSIONPROPERTIES

[Items].[Category].[Category],

[Items].[Family].[Family],

[Items].[Series].[Series],

[Items].[Description].[Description],

MEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWS

FROM [Consolidated] CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

(Assuming I can get the above to work - see this thread.)

When I run this in the MDX query window in SQL Management Studio, I don't get additional columns in the result set, but I presume that these attributes become (custom?) properties of the Items.ItemNumber column in the resultset. Are these properties accessible in the dataset created by Reporting Services? If so, how?

If not, then how can I show the descriptive attributes of the dimension in the report without the huge cross-join?

(As an aside, I am sure hoping the next generation of SSRS (or some 3rd party controls) will replace the matrix control which can only handle a 2-dimensional resultset, so I don't have to flatten out a multi-dimensional, hierarchical dataset down to a 2-d table structure. It's ugly.)

Mike

You may want to take a look at the EXISTING and/or the NONEMPTY functions. You might be able to use these creatively on your row axis to limit the number of members in the set prior to the cross join.

B.