none
combine two sets in MDX which have different dimensionality

    Question

  • hi,

    I need to combine results of two sets in MDX which have different dimensionality.

    I have following dimensions and fact table.

    Dimension 1

    ----------------

    Segment1

    Bitters

    CKE

    Cooking Wine

    Mustard


    Dimension 2

    -----------------

    Segment2

    CKE

    Cox Mkt

    FSA


    Fact Table

    --------------

    sk

    Segment1

    Segment2

    price

    1

    Bitters

    CKE

    25

    2

    Bitters

    FSA

    98

    3

    Bitters

    Cox Mkt

    24

    4

    Cooking Wine

    FSA

    12

    5

    Cooking Wine

    Cox Mkt

    32

    6

    Cooking Wine

    FSA

    54

    7

    Mustard

    Cox Mkt

    43

    8

    CKE

    CKE

    30

    9

    Bitters

    CKE

    35

    10

    CKE

    CKE

    55

    I Want to get output like following: (combine two result set in together in MDX)

    segment1/segmet2                        price

    ------------------------                        ----------

    Bitters                                            147

    CKE                                                  85

    CKE                                              145

    FSA                                               164        

    My Initial code snippet is as following:

    with
        set [set1] as Filter([Segment1].[Segment1].[Segment1],([Segment1].[Segment1].CurrentMember.Membervalue='Bitters' OR [Segment1].[Segment1].CurrentMember.Membervalue='CKE'))
        set [set2] as Filter([Segment2].[Segment2].[Segment2],([Segment2].[Segment2].CurrentMember.Membervalue='CKE' OR [Segment2].[Segment2].CurrentMember.Membervalue='FSA'))
    Select
    non empty
    (
        [set1]
       ,[set2]
    ) on rows
    ,
    (
    [Measures].[Price]
    )
    on columns
    From [Cube]

    ----

    I need the result as union does in T-SQL. But In MDX I cannot use union as set1 & set2 have different dimensionality.

    How I can get the combined results of two sets in MDX which have different dimensionality.

    Regards,

    Sadhin



    Tuesday, April 23, 2013 10:32 AM

Answers

  • with 
     
    Member [Segment1].[Segment1].[CKE2] AS ([Segment1].[Segment1].[All],([Segment2].[Segment2].[CKE])
    Member [Segment1].[Segment1].[FSA] AS ([Segment1].[Segment1].[All],([Segment2].[Segment2].[FSA])
     set [set1] as Filter([Segment1].[Segment1].[Segment1],([Segment1].[Segment1].CurrentMember.Membervalue='Bitters' OR [Segment1].[Segment1].CurrentMember.Membervalue='CKE'))
    Select 
    non empty
    (
        [set1]
       ,[Segment1].[Segment1].[CKE2]
    ,[Segment1].[Segment1].[FSA]
    ) on rows
    ,
    (
    [Measures].[Price]
    )
    on columns
    From [Cube]

    Try this

    • Marked as answer by sadhin.dhrubo Wednesday, April 24, 2013 10:42 AM
    Tuesday, April 23, 2013 7:49 PM

All replies

  • with 
     
    Member [Segment1].[Segment1].[CKE2] AS ([Segment1].[Segment1].[All],([Segment2].[Segment2].[CKE])
    Member [Segment1].[Segment1].[FSA] AS ([Segment1].[Segment1].[All],([Segment2].[Segment2].[FSA])
     set [set1] as Filter([Segment1].[Segment1].[Segment1],([Segment1].[Segment1].CurrentMember.Membervalue='Bitters' OR [Segment1].[Segment1].CurrentMember.Membervalue='CKE'))
    Select 
    non empty
    (
        [set1]
       ,[Segment1].[Segment1].[CKE2]
    ,[Segment1].[Segment1].[FSA]
    ) on rows
    ,
    (
    [Measures].[Price]
    )
    on columns
    From [Cube]

    Try this

    • Marked as answer by sadhin.dhrubo Wednesday, April 24, 2013 10:42 AM
    Tuesday, April 23, 2013 7:49 PM
  • hi,

    thanks. It was really helpful.

    Regards,

    sadhin

    Wednesday, April 24, 2013 10:42 AM