Many to Many modeling with SSAS

View previous topic View next topic Go down

Many to Many modeling with SSAS

Post  Ham09 on Tue Aug 02, 2011 7:35 am

Up until recently I haven't needed to model 'many to many' relationship data in SSAS. I have found quite a good paper called "The many-to-many revolution" written by Marco Russo which goes through several different design patterns to cater for different business scenarios. Core to many of the patterns described is the use of Factless Fact table used to bridge different fact tables via dimensions.

I have also had a look at 'Adventure Works DW 2008' example solution where 'FactInternetSales' table and 'FactInternetSalesReason' table are directly joined to each other (using SalesOrderNumber and SalesOrderLineNumber keys) without an intermediate Dimension. There is a dimension 'DimSalesReason' but this joins to 'FactInterSalesReason' only. Is this bad design practice or doesn't matter?

I have noticed the records in the Adventure works sample data appears to be one (sale) to many (sales reason) however on the Dimension Usage tab, the Sales Reason dimension shows a 'Many-to-Many' relationship with the Internet Sales measure. Can someone explain why it is modeled this way?

Thanks,

Simon

Ham09

Posts : 8
Join date : 2011-07-26
Location : United Kingdom

View user profile

Back to top Go down

Re: Many to Many modeling with SSAS

Post  Ham09 on Tue Aug 02, 2011 6:02 pm

I have since found out why the AW example is the way it is. It is because 'Internet Sales Order Details' and 'Internet Sales Facts' share the same table as they are a 'Fact Dim' type table in SSAS. It looks strange at first seeing 'Yellow' pointing to 'Yellow' in the data source view (implying a fact to fact join at first glance) when in reality it is a Fact to 'Fact Dim' join.

Ham09

Posts : 8
Join date : 2011-07-26
Location : United Kingdom

View user profile

Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum