Search results matching tags 'BISM' 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.

Free online session at PASS DW/BI Virtual Chapter #sqlpass – Wed Jan 18th, 2012

sqlbi — Mon, 16 Jan 2012 22:33:41 GMT

On January 18th, 2012 at 12pm Eastern Time (which is 5pm GM) Alberto Ferrari will deliver a free online session for the PASS DW/BI Virtual Chapter. This is the official description of the session.

Many-to-Many Relationships in DAX

The introduction of many to many dimension relationships in SSAS (since 2005) opened new scenarios that can be solved using OLAP cubes. Now, with the introduction of the Vertipaq engine and the DAX programming language, it seems that many-to-many relationships are no longer supported. As we are going to show, this is not true: many-to-many relationships can be leveraged in DAX too, even if this means some deep understanding of the Vertipaq engine and of the DAX programming language. During the session we will show many scenarios where many-to-many relationships can be managed by using DAX. The implementations are different from those used in SSAS, both from the data modeling and from the programming points of view. Gaining the ability to master many-to-many relationships will open new modeling scenarios that look very promising in SSAS.

This is the session that scored the best session in BI Platform Track at PASS Summit 2011, so you might have high expectations! Don’t miss this session if you are serious with BISM Tabular. It is an advanced one, but is a must for any serious BI Developer who wants to use BISM Tabular!

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!