I have a situation where I want to query multiple attributes (~8 altogether) and include subtotals. This is the kind of result that I want:
╔═══════╦═════════╦════════╦═════════╗
║ Attr1 ║ Attr2 ║ Attr3 ║ Measure ║
╠═══════╬═════════╬════════╬═════════╣
║ All ║ All ║ All ║ 50% ║
║ Foo ║ All ║ All ║ 25% ║
║ Bar ║ All ║ All ║ 90% ║
║ Foo ║ Anna ║ All ║ 42% ║
║ Foo ║ Brian ║ All ║ 12% ║
║ Bar ║ Charles ║ All ║ 10% ║
║ Bar ║ Dory ║ All ║ 112% ║
║ Foo ║ Anna ║ Box ║ 58% ║
║ Foo ║ Anna ║ Circle ║ 13% ║
║ ... ║ ... ║ ... ║ ... ║
╚═══════╩═════════╩════════╩═════════╝
Now, I can almost get there by doing something like this:
select
{[Measures].[Measure]} on columns,
nonempty({
[Dim1].[Attr1].allmembers *
[Dim2].[Attr2].allmembers *
[Dim3].[Attr3].allmembers
}) on rows
from [Cube]
However, this of course gets me a set that includes members that look like this:
╔═══════╦═════════╦════════╦═════════╗
║ Attr1 ║ Attr2 ║ Attr3 ║ Measure ║
╠═══════╬═════════╬════════╬═════════╣
║ Foo ║ All ║ Box ║ 25% ║
║ Bar ║ All ║ Circle ║ 90% ║
║ Foo ║ Anna ║ Box ║ 16% ║
║ Bar ║ Charles ║ Circle ║ 78% ║
║ ... ║ ... ║ ... ║ ... ║
╚═══════╩═════════╩════════╩═════════╝
Which I don't want - I could live with them, except that with 8 dimensions it makes it go a bit crazy with the cross-join (it gives me an error about having a set with more than 4 billion tuples in it...). Now, if I were writing SQL I could do something simple like:
select
Dim1.Attr1,
Dim2.Attr2,
Dim3.Attr3,
Sum(Measures.Measure) as Measure
group by
Dim1.Attr1,
Dim2.Attr2,
Dim3.Attr3
with rollup
But I can't find an easy way to reproduce this in MDX. I can manually build each rollup level with something like this:
select
{[Measures].[Measure]} on columns,
nonempty(
{
{[Dim1].[Attr1].[All]} *
{[Dim2].[Attr2].[All]} *
{[Dim3].[Attr3].[All]}
} +
{
{[Dim1].[Attr1].[Attr1].allmembers} *
{[Dim2].[Attr2].[All]} *
{[Dim3].[Attr3].[All]}
} +
{
{[Dim1].[Attr1].[Attr1].allmembers} *
{[Dim2].[Attr2].[Attr2].allmembers} *
{[Dim3].[Attr3].[All]}
} +
{
{[Dim1].[Attr1].[Attr1].allmembers} *
{[Dim2].[Attr2].[Attr2].allmembers} *
{[Dim3].[Attr3].[Attr3].allmembers}
}
) on rows
from [Cube]
But that's already getting tedious with just three dimensions - specifying 9 groups of these is going to be nasty. So - is there a way of doing this concisely in MDX, or do I just have to go with the long-hand solution?
In terms of prior research, I've come across a bunch of answers like this one that say to use a WITH MEMBER statement to create a total row - but that's pointless for me, since it results in the same cross-join behaviour that I'm trying to avoid with the allmembers function.
Edit: this is the latest (sanitized) version of the code, including @Danylo's suggestion of NonEmptyCrossJoin:
NON EMPTY {
NONEMPTYCROSSJOIN(
{[Dim1].[Attribute].[All]} *
{[Dim2].[Attribute].[All]} *
{[Dim3].[Attribute].[All]} *
{[Dim4].[Attribute].[All]} *
{[Dim6].[Attribute].[All]} *
{[Dim7].[Attribute].[All]} *
{[Dim8].[Attribute].[All]} *
{[Dim9].[Attribute].[All]} *
[Dim0].[Attribute].[Attribute].ALLMEMBERS
) +
NONEMPTYCROSSJOIN(
[Dim1].[Attribute].[Attribute].ALLMEMBERS *
{[Dim2].[Attribute].[All]} *
{[Dim3].[Attribute].[All]} *
{[Dim4].[Attribute].[All]} *
{[Dim6].[Attribute].[All]} *
{[Dim7].[Attribute].[All]} *
{[Dim8].[Attribute].[All]} *
{[Dim9].[Attribute].[All]} *
[Dim0].[Attribute].[Attribute].ALLMEMBERS
) +
NONEMPTYCROSSJOIN(
[Dim1].[Attribute].[Attribute].ALLMEMBERS *
[Dim2].[Attribute].[Attribute].ALLMEMBERS *
{[Dim3].[Attribute].[All]} *
{[Dim4].[Attribute].[All]} *
{[Dim6].[Attribute].[All]} *
{[Dim7].[Attribute].[All]} *
{[Dim8].[Attribute].[All]} *
{[Dim9].[Attribute].[All]} *
[Dim0].[Attribute].[Attribute].ALLMEMBERS
) +
...
}
I can't see a way around using at least one cross-join (though putting the measure you're interested in into the NonEmpty() function - see comment - may help with the cross-join performance/out-of-memory problem).
The SQL ROLLUP-style totals exclude certain combinations of ALL and non-ALL, based on the order of the columns in your GROUP BY clause. (In your example, this exclusion shows as a neat triangular pattern of ALLs in your result-set). MDX doesn't do this: it doesn't really care about the order of the sets in your cross-join.
There's a way to do this by making MDX aware of this order. It's a bit elaborate, but may be easier (or better-performing) than the long, "handbuilt" approach you tried:
WITH
MEMBER Measures.DimensionsAllPattern AS
CASE WHEN [Dimension1].[Hierarchy].CurrentMember.Properties("LEVEL_NUMBER")="0" THEN "1" ELSE "0" END +
CASE WHEN [Dimension2].[Hierarchy].CurrentMember.Properties("LEVEL_NUMBER")="0" THEN "1" ELSE "0" END +
CASE WHEN [Dimension3].[Hierarchy].CurrentMember.Properties("LEVEL_NUMBER")="0" THEN "1" ELSE "0" END +
... etc up to dimension 8...
MEMBER AllPatternStrNum AS VBA!Cstr(VBA!CLng(Measures.DimensionsAllPattern))
SELECT
{Measures.DimensionsAllPattern,Measures.AllPatternStrNum} ON 0,
FILTER
(CROSSJOIN
([Dimension1].[Hierarchy].AllMembers,
[Dimension2].[Hierarchy].AllMembers,
.... etc
)
,
(Measures.AllPatternStrNum="0") OR
(Measures.AllPatternStrNum=VBA!String(VBA!Len(Measures.AllPatternStrNum),"1"))
)
ON 1
FROM TheCube
What this is doing is:
For each combination of members from the dimensions, building a string corresponding to the pattern of All/Non-All, based on a specified order of dimensions. For example, {All,Something,All,Something} would be coded as 1010.
A second calculated member converts this measure to a number and then back to a string: so 1010 would end up as 1010, but 0011 would end up as 11 (simple way of stripping off leading zeros)
The cross-joined set is then filtered according to this second member. It must either be equivalent to 0 (no ALL values at all), or be a string of 1s as long as its own length.
(Note I haven't included any NonEmpty stuff in my example, or the measure you actually want to see).
You may need to do wrap the filtered set in an ORDER(set,something,BASC) to get it looking the way you want.