Assuming we have the following dimension
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
?
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 ?