Mdx union between different dimensions

Question

I have situation where I have two different dimensions in a model. Dimension A has ColorId, FirstColor and SecondColor. Dimension B has ColorId and Firstcolor. I am trying to get a complete list of colorIds from both dimensions that are distinct. Please keep in mind that this mdx needs to be used in ssrs.

Dimension A

ColorId     FirstColor    SecondColor
T0z17       Blue           Green
T0lqa       Red            Yellow

Dimension B

ColorId     FirstColor
T201k       Black
Tph1v       Orange

Intended Output (only 1 column)

ColorId
T0z17
T0lqa
T201k
Tph1v

It's a very uncommon task for SSAS. The idea behind OLAP is to provide dimension with distinct values only. It's possible, but the easiest way to do so is to get the result from DWH using SQL querying. — Danylo Korostil, Apr 08 '17 at 19:50
To be clear you expect the same color to appear in Dimension A and in Dimension B? — GregGalloway, Apr 08 '17 at 19:55
@greggalloway No it doesn't necessarily have to have the same color. Just the column names and data types. Color ID will be in the same format as well — Mutai, Apr 08 '17 at 19:58
@Danylo Korostil Any way of doing this in ssrs? I would prefer not to add a separate dimension just for this. I can't use sql query since for our requirement we can only use the tabular model. Prefer to use ssrs or mdx — Mutai, Apr 08 '17 at 20:00
I'm not a SSRS expert, but most likely you have to create two datasets (A, B dimensions values) and reuse the Lookup() function there as join alternative. Then get distinct values. — Danylo Korostil, Apr 08 '17 at 20:11

score 3 · Answer 1 · edited Apr 09 '17 at 06:16

3

Try this union query:

WITH MEMBER [Measures].[Color] as 
   IIf(
     [Dimension A].[Color Id].CurrentMember 
        Is [Dimension A].[Color Id].[All]
   , [Dimension B].[Color Id].CurrentMember.Name
   , [Dimension A].[Color Id].CurrentMember.Name
   )
SELECT [Measures].[Color] on 0,
{
 [Dimension A].[Color Id].[Color Id].Members
 * [Dimension B].[Color Id].[All]
}
+
{
 [Dimension A].[Color Id].[All]
 * [Dimension B].[Color Id].[Color Id].Members
}
On 1
FROM [YourCube]

edited Apr 09 '17 at 06:16

whytheq

34,466
65
172
267

answered Apr 08 '17 at 21:30

GregGalloway

11,355
3
16
47

Nice, but it will result duplicates, not distinct values. – Danylo Korostil Apr 09 '17 at 07:10
I've reused Greg's code and added a distinct concept. – Danylo Korostil Apr 09 '17 at 08:35
@Danylo - the `+` operator removes duplicates when performing a union – whytheq Apr 09 '17 at 22:10
@whytheq interesting. Didn't know that. Unfortunately it won't dedup across dimensions. Luckily Mutai clarified deduping wasn't necessary. – GregGalloway Apr 10 '17 at 00:25
@whytheq {[Dimension A].[Color Id].[Black] * [Dimension B].[Color Id].[All]} + {[Dimension A].[Color Id].[All] * [Dimension B].[Color Id].[Black]} will return two Black members. – Danylo Korostil Apr 10 '17 at 06:06
@DanyloKorostil true - my mistake - it de-dupes but not across a two dimensional tuples as in this script – whytheq Apr 10 '17 at 07:19

Danylo Korostil · Accepted Answer · 2017-04-09T08:42:34.213

As Greg's answer isn't real distinct, I'd fix his code in the following way:

With 
Member [Measures].[SameColor] as
IIF(
    [Dimension A].[Color Id].CurrentMember.Name = [Dimension B].[Color Id].CurrentMember.Name,
    1,
    NULL
)

Member [Measures].[OnlyAColor] as
IIF(
    IsEmpty(
        LinkMember(
            [Dimension A].[Color Id].CurrentMember,
            [Dimension B].[Color Id]
        )
    ),
    1,
    NULL
)

Member [Measures].[OnlyBColor] as
IIF(
    IsEmpty(
        LinkMember(
            [Dimension B].[Color Id].CurrentMember,
            [Dimension A].[Color Id]
        )
    ),
    1,
    NULL
)

Member [Measures].[Color] as 
IIF(
    [Dimension A].[Color Id].CurrentMember is [Dimension A].[Color Id].[All],
    [Dimension B].[Color Id].CurrentMember.Name
    [Dimension A].[Color Id].CurrentMember.Name
)

Select [Measures].[Color] on 0,  
NonEmpty(
    [Dimension A].[Color Id].[Color Id].Members * [Dimension B].[Color Id].[Color Id].Members,
    [Measures].[SameColor]
)
+
NonEmpty(
    [Dimension A].[Color Id].[Color Id].Members * [Dimension B].[Color Id].[All],
    [Measures].[OnlyAColor]
)
+
NonEmpty(
    [Dimension A].[Color Id].[All] * [Dimension B].[Color Id].[Color Id].Members,
    [Measures].[OnlyBColor]
) on 1
From [YourCube]

if Dim A and Dim B have used different key values for the colors so Red is key 101 in A but 102 in B (entirely possible) then `LinkMember` will not work — whytheq, Apr 09 '17 at 15:30
Yeah, you're right. You can reuse the StrToMember() function with names in that case. We don't know, I just wanted to make a point about distinct logic. — Danylo Korostil, Apr 09 '17 at 20:11
(upped) I really like the `NonEmpty(....,[Measures].[SameColor])` trick for de-duping. — whytheq, Apr 17 '17 at 07:52

Mdx union between different dimensions

2 Answers2