MDX crossjoin with 5 dimensions

1
votes

Assuming we have the following dimension

  • DATE
  • CONTRACTOR
  • COLOR
  • SIZE

and a set of 6 measures, we want to get the 6 measures for every Semestrer, Trimester, Month, Contractor, Color and Size for a given Year.

Here is my MDX query:

SELECT 
   { 
         ... the 6 measures ... 
   } ON COLUMNS, 
    { 
          (
                [Dim DATE].[year].[year].AllMembers * 
                [Dim DATE].[SEMESTRE NOM].[SEMESTRE NOM].AllMembers * 
                [Dim DATE].[TRIMESTRE NOM].[TRIMESTRE NOM].AllMembers * 
                [Dim DATE].[MOIS NOM].[MOIS NOM].AllMembers *
                [Dim CONTRACTOR].[Name CONTRACTOR].AllMembers *
                [Dim COLOR].[Name COLOR].AllMembers *
                [Dim SIZE].[Name SIZE].AllMembers 
         ) 
   } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 

 FROM   ( 
   SELECT ( { StrToSet( "[Dim DATE].[year].[year].&[" + @Year + "]" , CONSTRAINED ) }      

   ) ON COLUMNS 
FROM [TheCube]

This is very slow (more than 20 minutes), with only 6 Contractors, 6 Color, 18 sizes... and what is the differences between .AllMembers and .Members ?

1
ssasmdxcubecross-join

1 Answers

1
votes

Depending on the actual number of years in your hierarchy, you might create a quite large set with the crossjoin. As icCube mentionned in the comment, why not using :

StrToSet( "[Dim DATE].[year].[year].&[" + @Year + "]" , CONSTRAINED )

in the crossjoin on the ROWS ?

AllMembers vs Members; here is a doc; allmembers include the calc. members; do you have any costly calc. members ?

How many members do you have in [Dim DATE].[MOIS NOM].[MOIS NOM].AllMembers ? trimestre, semestre ?