THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Analysis Services 2005: many-to-many dimension, killer feature!

SQL Server 2005 has a new version of Analysis Services with a lot of new features, so much that a whole (thick) book would be necessary to describe everything. Nevertheless, many improvements are "marginal", in the sense that bring us better productivity (= less development time) or better performance. Aside administrative and development features, there are not so (very) much end user features so important for him to convince to jump into the new release (even without changing every client...): Analysis Services 2000 is a great product and if pushed to the limits can resolve a lot of business problems, you only have to know the product very well and have to be ready to do some stunt.

Anyway, Analysis Services 2005 (AS2005 for shorter) has some feature that is revolutionary. One of those is the many-to-many dimension.

This is the business scenario: you have a fact table that describe a fact measure (the account balance at a certain date) for an entity (for a bank account) which can be joined to many members of another dimension (the many owners of a bank account). Who knows the multidimensional model already see the trouble, beacuse it's not easy to describe the non-aggregability of measures joined to dimensions with a many-to-many relationship (in this case, each bank account can have one or more owners and each owner can have one or more accounts).

With AS2005 the trouble, simply, disappears. The "trick" is to use an intermediate fact table that, in the relational model, indeed defines the relationship many-to-many. In the following figure fact tables are yellow and dimension tables are blue. Note that DimCustomerAccount is considered a fact table.

When you define relationships between dimensions and measure groups (a measure group is similar to a real cube of AS2000, while a AS2005 cube is more like a virtual cube of AS2000), you specify that the Customer dimension is joined to Balance with the dimension DimCustomerAccount (it's the selected item in the following figure).

The relationship is further described by the following dialog box that you obtain with a click on the button contained in the selected item of the previous image. This dialog box is available for every combination between dimensions and measure groups and it defines the type of relationship.

Here is the result. In test tables I created 5 customers (Luca, Marco, Paolo, Roberto e Silvano) and 9 accounts (numbered from 1 to 9). Each account is joined to one or more customers and the balance for each account is always 100.

As you can see, for each customer you can identify accounts he owns and for each account you can see the balance repeated for each owner.... but the total for each account is always 100 (Grand Total row) and the balance for all accounts is 900 (100 * 9). Try to do that with any other multidimensional model (and I'm not talking only about AS2000) and see what happens... The following image synthetize the non-aggregability of some measure in respect of some dimensions.

You can obtain the same result with AS2000 but only with some stunt and some tradeoff in terms of processing time or query performance. Who, like me, already experienced similar issues, can't wait to upgrade to SQL 2005 in a production environment just only for this feature...

Published Monday, October 04, 2004 10:21 AM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

luka said:

Hi Marco,

It would be of a great help if you could post a query that inserts rows into the factless fact table. I am dealing with this problem but am unsure what to put into the factless fact (what kind of a join, and what to join). As I noticed in some examples one can insert records to the factless fact from OLTP or from DW, is this true?

Thanks,

Luka

September 21, 2010 9:04 AM
 

Sean said:

I've always found the way you have to do many-to-manys in AS very odd.  Each time I do it I've had to invent an intermediate dimension just so the intermediate fact table can join to "real" fact table.

In a normal SQL database two related tables are joined by a link table and that's it.  But in AS you have the link table and you have to have another one as an intermediate dimension.  Usually there is no obvious dimension to hand so I have to invent one - which feels very uncomfortable.  I generally make this extra dimension hidden so that the end user cannot see it.

Surely there's a better way?

January 25, 2011 5:39 AM
 

Marco Russo (SQLBI) said:

Sean,

if you think about it, only a 1-many or many-1 relationship can be expressed as you are saying. A true many-to-many relationship, such as bank account and customers, really needs an intermediate table to establish such a relationship.

The reason SSAS adopt this design pattern is to be the more flexible as possible and also because it is the way that Kimball dimensional modeling describes it.

Marco

January 25, 2011 5:56 AM
 

Robert said:

Marco,

I have a question on the actual Dimension Usage with a Many-2-Many relationship.  In your example it is pretty clear because you only are sharing a single dimension Dim Account.  What happens to the Many-2-Many relationship under the covers when I share 2 or 3 dimensions.

Thanks,

Robert

May 23, 2011 1:34 PM
 

Marco Russo (SQLBI) said:

Both dimensions (or the N dimensions) act as a constraint on both fact tables and you might think as a "single" tuple that needs to be the same between both sides of the relationships.

Marco

May 23, 2011 2:48 PM
 

Robert said:

Marco,

Thank you for your response.  So, if I go down the path of M2M I want to make sure the shared dimensions are valid for constraining the data.  If the shared dimensions are too constraining then I could materialize the Intermediate Measure Group to create the M2M.

Thank you very much.

I also have reread the M2M Revolution paper and am now rethinking a series of Distinct Count thoughts.

May 24, 2011 10:00 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement