The Many-to-Many Revolution 2.0 #ssas #mdx #dax #m2m

sqlbi — Wed, 09 Nov 2011 12:17:00 GMT

In September 2006 I had announced in this blog the release of the first version of The Many-to-Many Revolution, a whitepaper that describes how to leverage the many-to-many dimension relationships feature that had being available since Analysis Services 2005. The paper contains many generic patterns that can be applied in many common data analysis’ scenarios.

More than 5 years later and more then 20.000 unique people that downloaded the 1.0 paper, I am proud to announce that we released

The Many-to-Many Revolution 2.0.

These are the news in this edition:

Alberto Ferrari joined me as co-author of the paper
We added a new pattern for BISM Multidimensional (formerly known as UDM)
We translated several existing pattern to BISM Tabular model.
- Because BISM Tabular doesn’t support many-to-many relationships in its data model, you have to rely on DAX formulas to obtain the desired results. This produces many changes in data modeling and we tried to cover these differences in the paper, too

The paper is freely available in PDF format. We will publish single patterns described in the paper as web articles, in order to improve readability and indexing from search engines (today everybody use a web search engine instead than looking for a document in local disk, just because it’s faster).

If you are willing to watch a one-hour session about a few of the BISM Tabular models described in the paper, I suggest you to look at the Many-to-Many Relationships in DAX session that Alberto held in Liverpool at SQLBits 9 two months ago. Yes, the paper has much more details and model, but you can start with the video and then study on the paper!

We look forward to get your feedback!

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

sqlbi — Mon, 04 Oct 2004 07:21:00 GMT

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...

Search results matching tags 'SSAS', 'M2M', and 'Analysis Services'

The Many-to-Many Revolution 2.0 #ssas #mdx #dax #m2m

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