THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
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 4, 2004 10:21 AM by Marco Russo (SQLBI)
Filed under: , ,



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?



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:


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.


January 25, 2011 5:56 AM

Robert said:


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.



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.


May 23, 2011 2:48 PM

Robert said:


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

Uday Kiran said:

Hi Chris,

How are you doing. I follow your BI blog a lot. I have a question regarding a design.


Dimension Usage:

Two Fact Tables F1 and F2. 6 Dimension Tables – D1 to D5

F1 is in a regular relationship with D1 and D2.

F2 is in a regular relationship with D3, D4 and D5.

F1 also needs to be sliced by D3. F1 is related to D3 by a many-to-many relationship with F2.

F1        F2

D1 D1F1

D2 D2F1

D3 F2 (M-M) D3F2

D4        D4F2

D5        D5F2


Measures – F1.M1 and F2.M2 are placed side by side and only D3 is chosen for slicing the cube is fast.

If D1, D2 and D4, D5 are chosen individually or together with F1.M1 and F2.M2, the cube gets stuck.

Is this the reason - F1 is not related to D4 and D5. F2 is not related to D1 and D2?

But if D1, D2, D4 and D5 are placed alongside as well (even when dimensions are not related to fact), the cube gets stuck due to missing relationships

Possible solution:

Create many-to-many relationships on F1 for D4 and D5 using F2.

Create many-to-many relationships on F2 for D1 and D2 using F1.

Is this feasible and a good use of dimension usage performance wise?

F1        F2

D1 D1F1        F1 (M-M)

D2 D2F1        F1 (M-M)

D3 F2 (M-M) D3F2

D4 F2 (M-M) D4F2

D5 F2 (M-M) D5F2

October 28, 2016 12:02 PM
New Comments to this post are disabled

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



Privacy Statement