SSAS: T-SQL Equivalent for a Many-to-Many relationship

This question came a while ago now in this SSAS forum thread: What is wrong in my query and I thought it was something that may interest other people.

Basically it boiled down to trying to find a T-SQL equivalent to the following MDX which is querying a dimension with a many-to-many relationship to the measure.

So given the following simple MDX query, what would be the equivalent in SQL?

 

select 
  measures.[Internet Sales Amount] on 0
  , [Sales Reason].[Sales Reasons].[Reason Type].Members on 1
FROM [Adventure Works]

 

Well, what I came up with was the following where I ended up effectively joining to the fact table twice. I don't know about you, but I'd rather write the MDX version any day. :)

 

SELECT 
  m2m.SalesReasonReasonType
  ,Sum(f.SalesAmount) 
FROM FactInternetSales f
INNER JOIN 
(
    SELECT DISTINCT salesOrderNumber, SalesOrderLineNumber, D.SalesReasonReasonType
    FROM [dbo].[DimSalesReason] AS dim
    INNER Join dbo.FactInternetSalesReason isr
        ON dim.SalesReasonKey = isr.SalesReasonKey 
) m2m
    on f.SalesOrderNumber = m2m.SalesOrderNumber 
    And f.SalesOrderLineNumber = m2m.SalesOrderLineNumber 
GROUP BY m2m.SalesReasonReasonType

 

Print | posted on Sunday, May 3, 2009 9:47 PM

Comments on this post

# re: SSAS: T-SQL Equivalent for a Many-to-Many relationship

Requesting Gravatar...
Quite inspiring,

The MDX query has come in very handy

keep up the good work

Thanks
Left by Web developers on Oct 16, 2009 7:54 PM

# re: SSAS: T-SQL Equivalent for a Many-to-Many relationship

Requesting Gravatar...
Here are this and some other articles on Many-to-Many Dimension:

http://ssas-wiki.com/w/Articles#Many-to-Many_Dimension

Left by Sam Kane on Mar 18, 2011 12:33 AM

Your comment:

 (will show your gravatar)