ssasmdxbusiness-intelligencemdx-query

UNION ALL of 2 MDX queries (within data cube)


I'm mdx rookie.. A box of gold for whoever can tell how to do UNION ALL of these two MDX queries:

SELECT
{
[Measures].[P Count]
}
ON COLUMNS,
{
(
[Dim P View].[Person Key].[Person Key].ALLMEMBERS *
[Dim P View].[Is Sensitive Data A].[Is Sensitive Data A].ALLMEMBERS *
[Dim P View].[Is Sensitive Data B].[Is Sensitive Data B].ALLMEMBERS *
[Dim P View].[Is Person Imp X].[Is Person Imp X].ALLMEMBERS 
)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS FROM ( SELECT ( { [Dim P View].[Is Person Imp X].&[True] } )
ON COLUMNS FROM [BI_CUBE])


***UNION ALL***


SELECT
{
[Measures].[P Count]
}
ON COLUMNS,
{
(
[Dim P View].[Person Key].[Person Key].ALLMEMBERS *
[Dim P View].[Is Sensitive Data A].[Is Sensitive Data A].ALLMEMBERS *
[Dim P View].[Is Sensitive Data B].[Is Sensitive Data B].ALLMEMBERS *
[Dim P View].[Is Person Imp Y].[Is Person Imp Y].ALLMEMBERS 
)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS FROM ( SELECT ( { [Dim P View].[Is Person Imp Y].&[True] } )
ON COLUMNS FROM [BI_CUBE])

I tried to append queries via Power Pivot / Power BI - which succeeded but this is main requirement to have it in one query.


Solution

  • Try this:

    
    SELECT
    {
    [Measures].[P Count]
    }
    ON COLUMNS,
    {
    (
    [Dim P View].[Person Key].[Person Key].ALLMEMBERS *
    [Dim P View].[Is Sensitive Data A].[Is Sensitive Data A].ALLMEMBERS *
    [Dim P View].[Is Sensitive Data B].[Is Sensitive Data B].ALLMEMBERS *
    {
     ([Dim P View].[Is Person Imp X].&[True], [Dim P View].[Is Person Imp Y].[All]),
       ([Dim P View].[Is Person Imp X].[All], [Dim P View].[Is Person Imp Y].&[True])
    }
    )
    }
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
    ON ROWS FROM [BI_CUBE]