How to combine measures with different dimensionality into single select statement using MDX & Analysis Services (SSAS)?

Question

I would like to add a measure that has different dimensionality to the same SELECT mdx statement. In general, most of my measures are linked to [Customer Creation Date].[Date Hierarchy].[Month Name]. However, Anonymous customers do not have the link to customer creation date; therefore, I have to link anonymous to Enquiry Creation Date. How to combine two measures into single select query.

> WITH  MEMBER [Measures].[Allow Contact] as
            ([Measures].[# CRM Customers],[Customer].[Customer Allow Contact].&[Yes],[Customer].[Customer Status].&[Active] )

        MEMBER [Measures].[Total Identified Customers] AS
            ([Measures].[# CRM Customers],[Customer].[Customer Status].&[Active] )

        MEMBER [Measures].[Anonymous Customers] AS
            ([Measures].[# Unique Distinct Customers on Contact],[Enquiry].[Anonymous].&[Yes]) 



SELECT NON EMPTY {  Measures].[Allow Contact], 
                    [Measures].[Total Identified Customers]
                    //,[Measures].[Anonymous Customers]

                 } ON COLUMNS
       ,NON EMPTY ([Customer Creation Date].[Date Hierarchy].[Month Name]
       //,[Enquiry Creation Date].[Date Hierarchy].[Month Name] //How to add different dimensionality  

       ) ON ROWS
FROM [Cube]

Have you created the dimension [Enquiry Creation Date], which would need to have a regular relationship to your calendar dimension in your cube structure? — Mez
Yes, I have created regular relationship between calendar dimension & fact table. The problem is that I want to use different calendar dimension for Anonymous customers. — BI Dude
You can create 2 other measures, having the counts of new customers based on creation date, and also the inquiry count. Take a look at this link which elaborates scenarios where you have 2 date dimensions - dwbi1.wordpress.com/2009/12/16/… — Mez

FrankPl FrankPl · Accepted Answer · 2014-07-08T16:52:39

If both time dimensions have the same structure (which should obviously be the case if they are implemented as role playing dimensions on the same dimension object), you can use the LinkMember function in the definition of [Measures].[Anonymous Customers] to use the [Customer Creation Date].[Date Hierarchy] in the rows, but use these dates as the [Enquiry Creation Date].[Date Hierarchy] in the measure calculation:

WITH  MEMBER [Measures].[Allow Contact] as
            ([Measures].[# CRM Customers],[Customer].[Customer Allow Contact].&[Yes],[Customer].[Customer Status].&[Active] )

        MEMBER [Measures].[Total Identified Customers] AS
            ([Measures].[# CRM Customers],[Customer].[Customer Status].&[Active] )

        MEMBER [Measures].[Anonymous Customers] AS
            ([Measures].[# Unique Distinct Customers on Contact],[Enquiry].[Anonymous].&[Yes], 
             LinkMember([Customer Creation Date].[Date Hierarchy].CurrentMember, [Enquiry Creation Date].[Date Hierarchy]) 

SELECT NON EMPTY {  Measures].[Allow Contact], 
                    [Measures].[Total Identified Customers]
                   ,[Measures].[Anonymous Customers]
                 } ON COLUMNS
       ,NON EMPTY ([Customer Creation Date].[Date Hierarchy].[Month Name]
       ) ON ROWS
FROM [Cube]

How to combine measures with different dimensionality into single select statement using MDX & Analysis Services (SSAS)?

1 Answers