The Many-to-Many Revolution 2 - SQLBI

1 The Many-to-Many Revolution ADVANCED DIMENSIONAL MODELING WITH MICROSOFT SQL server analysis services produced by The many - to- many Revolution Advanced dimensional modeling with Microsoft SQL server analysis services Author: Marco Russo, Alberto Ferrari Published: Version Revision 1 October 10, 2011 Contact: and Summary: This paper describes how to leverage the many - to- many dimension relationships, a feature that debuted available with analysis services 2005 and is now available by using DAX in the new BISM Tabular available in analysis services Denali.

2 After introducing the main concepts, the paper discusses various implementation techniques in the form of design patterns: for each model, there is a description of a business scenario that could benefit from the model, followed by an explanation of its implementation. A separate download (available on ) contains SQL server database and analysis services projects with the same sample data used in this paper. BISM Tabular examples are available as PowerPivot for Excel workbooks. Acknowledgments: we would like to thank the many peer reviewers that helped us to improve this document: Bryan Batchelder, Chris Webb, Darren Gosbell, Greg Galloway, Jon Axon, Mark Hill, Peter Koller, Sanjay Nayyar, Scott Barrett, Teo Lachev, Grant Paisley, Javier Guill n.

3 I would also like to thank Anand, Marin Bezic, Marius Dumitru, Jeffrey Wang, Ashvini Sharma and Akshai Mirchandani who answered to our questions. TABLE OF CONTENTS INTRODUCTION .. 5 MULTIDIMENSIONAL MODELS .. 7 A Note about Visual totals .. 7 A Note about Naming Convention & Cube Design Best Practices .. 7 A Note about UDM and BISM acronyms .. 7 CLASSICAL Many-to-Many RELATIONSHIP .. 8 Business scenario .. 8 Implementation .. 8 CASCADING Many-to-Many RELATIONSHIP .. 13 Business scenario .. 13 Implementation .. 16 SURVEY .. 24 Business scenario .. 25 Implementation .. 25 DISTINCT COUNT.

4 33 Business scenario .. 33 Implementation .. 34 Performance .. 45 MULTIPLE GROUPS .. 48 Business scenario .. 48 Implementation .. 49 CROSS-TIME .. 54 Business scenario .. 54 Implementation .. 55 TRANSITION MATRIX .. 63 Business scenario .. 63 Implementation .. 65 MULTIPLE PARENT/CHILD HIERARCHIES .. 70 Business scenario .. 70 Implementation .. 72 HIERARCHY RECLASSIFICATION WITH UNARY OPERATOR .. 80 Business scenario .. 80 Implementation .. 82 Handling of the unary operator .. 83 Using SQL to expand the expressions .. 85 Building the model .. 86 CONSIDERATIONS ABOUT MULTIDIMENSIONAL MODELS .. 94 Links.

5 94 TABULAR MODELS .. 96 A Note about UDM and BISM acronyms .. 96 Modeling Patterns with Many-to-Many .. 96 CLASSICAL Many-to-Many RELATIONSHIP .. 98 Business scenario .. 98 BISM Implementation .. 98 Denali Implementation .. 103 Performance analysis .. 103 CASCADING Many-to-Many RELATIONSHIPS .. 104 Business scenario .. 105 BISM Implementation .. 107 SURVEY .. 112 Business Scenario .. 112 BISM Implementation .. 114 Denali Implementation .. 119 Performance analysis .. 120 MULTIPLE GROUPS .. 122 TRANSITION MATRIX .. 124 Transition Matrix with Snapshot Table .. 126 SNAPSHOT TABLE IN THE SLOWLY CHANGING DIMENSION SCENARIO .. 127 SNAPSHOT TABLE IN THE HISTORICAL ATTRIBUTE TRACKING SCENARIO.

6 130 TRANSITION MATRIX WITH CALCULATED COLUMNS .. 133 BASKET analysis .. 138 Denali Implementation .. 143 CONSIDERATIONS ABOUT MULTIDIMENSIONAL MODELS .. 147 Links .. 147 The Many-to-Many Revolution 5 Introduction 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.

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

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

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

10 6 The Many-to-Many Revolution 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.