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

Optimize Many-to-Many with SUMMARIZE and Other Techniques

sqlbi — Fri, 01 Jun 2012 12:12:00 GMT

We are still in the early days of DAX and even if I have been using it since 2 years ago, there is still a lot to learn on that.

One of the topics that historically interests me (and many of the readers here, probably) is the many-to-many relationships between dimensions in a dimensional data model. When I and Alberto wrote the The Many to Many Revolution 2.0 we discovered the SUMMARIZE based pattern very late in the whitepaper writing. It is very important for performance optimization and it should be always used. In the last month, Gerhard Brueckl also presented an approach based on cross table filtering behavior that simplify the syntax involved, even if it’s harder to explain how it works internally.

I published a short article titled Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering on SQLBI website just to provide a quick reference to the three patterns available. A further study is still required to compare performance between SUMMARIZE and Cross Table Filtering patterns. Up to now, I haven’t observed big differences between them, even if their execution plans might be not identical and this suggest me that depending on other conditions you might favor one over the other.

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!

Many-to-Many Dimensions: Query Performance Optimization Techniques

sqlbi — Sat, 22 Dec 2007 14:51:02 GMT

More than one year ago I published a paper about design patterns for many-to-many dimension relationships. Since then, I built other models and I got more experience about possible performance optimizations. Unfortunately, I still hadn't time to write about it and this topic requires verbose document and analysis to be reproducible and understandable by everyone. Luckily, now there is a white paper (Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques) downloadable from Microsoft site that explore this world and offers an interesting set of scenarios and possible optimizations, with numbers that explain when, what and how you can expect to optimize a model based on one or more many-to-many dimension relationships.

I already read the document and it is pretty good. Unfortunately, in the real world there are still complex scenarios that cannot be optimized with the techniques used here. I hope that in future versions the SSAS engine will provide some more advanced optimizations for similar cases. One of the first step could be simply automating the optimizations made "by hand" like the Matrix Relationship Optimization shown in the paper. Another would be the simplification for defining efficient aggregations when many-to-many relationships are involved (now you could create a lot of aggregations that are unused when M2M are involved, and sometimes users query a cube only using M2M relationships - you have to tune the aggregations manually).

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