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

[Darren Gosbell]

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 1FROM [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 fINNER 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*

Technorati Tags: Analysis Services, MDX, Many to Many, SQL





More...