SQL Server Analysis Services introduced modeling many-to-many relationships between dimensions in version 2005. At a first glance, we may tend to underestimate the importance of this feature: after all, many other OLAP engines do not offer many-to-many relationships. Yet, this lack did not limit their adoption and, apparently, only a few businesses really require it.

The SQL Server Analysis Services version that will be released in 2012 (currently codenamed “Denali”) will introduce a new modeling (BISM, Business Intelligence Semantic Model) choice that is called “BISM Tabular” and will rename the former UDM (Unified Dimensional Model) to “BISM Multidimensional”.
UDM/BISM Multidimensional models can leverage many-to-many relationships helping you to present data from different perspectives that are not feasible with a traditional star schema. This opened a brand new world of opportunities that transcends the limits of traditional OLAP. At the same time, while BISM Tabular will not directly support many-to-many relationships between tables, you will be able to express such relationships by using DAX formulas.

The DAX language can be used in PowerPivot for Excel, which basically is SSAS running in process inside Excel and provides a good method to prototype complex cubes, learn the DAX language and experiment with the modeling features we are describing here.

Table of Contents

In this paper, we will explore many different uses of many-to-many relationships in both BISM Multidimensional and BISM Tabular, in order to give us more choices to model effectively business needs, including:

  • Classical many-to-many
  • Cascading many-to-many
  • Survey
  • Distinct Count
  • Multiple Groups
  • Cross-Time
  • Transition Matrix
  • Multiple Hierarchies
  • Hierarchy Reclassification with unary operator
  • Basket Analysis

The paper will first present the BISM Multidimensional models, and then the BISM Tabular models. Most of the models correspond to the BISM Multidimensional and one is unique to BISM Tabular (Basket Analysis). You can read these two sections of the paper independently.

Although you do not have to, we recommend reading the models in the presented order, because often one builds upon a previous model and we have arranged them in order of complexity.

It is fundamental to understand how many-to-many relationships work within Analysis Services (in both BISM Multidimensional and BISM Tabular) in order to use them for different purposes: minor implementation details such as the relationships between dimensions and measure groups could have major design repercussions since small changes may lead to different results and confusion to the end users. The theory of chaos applies wonderfully to the usage of many-to-many relationships.

Each model has a brief introduction, followed by a business scenario that may benefit from its use and an explanation of its implementation. Each model uses only the minimal set of dimensions that are necessary to explain the concept behind it and a small dataset that demonstrates the underlying behavior.

Sample content

You can get the sample content of this white paper from here: Many2Many_2.0_Demo.zip