Many to Many modeling with SSAS
Page 1 of 1
Many to Many modeling with SSAS
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
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
Re: Many to Many modeling with SSAS
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
Similar topics
» where to keep sql server data type text
» Periodic snashot and SSAS
» MDX blows up SSAS. Need help
» SSAS First And Last Time
» Yes/No flag in SSAS
» Periodic snashot and SSAS
» MDX blows up SSAS. Need help
» SSAS First And Last Time
» Yes/No flag in SSAS
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum