Relacion Varios A Varios Sq

LThe many-to-many revolution
Advanced dimensional modeling with SQL Server Analysis Services 2005

Author: Marco Russo Published: Version 1.0 Revision 93 – September 25, 2006 Contact: marco.russo(AT)sqlbi.eu – http://www.sqlbi.eu Summary: This paper describes how to leverage the many-to-many dimension relationships, a feature that debuted available with Analysis Services 2005. Afterintroducing 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 http://www.sqlbi.eu/manytomany.aspx) contains SQL Server database and Analysis Services projects with thesame sample data used in this paper Acknowledgments: I would like to thank the many peer reviewers that helped me to make this document more readable: Bryan Batchelder, Chris Webb, Darren Gosbell, Greg Galloway, Jon Axon, Mark Hill, Peter Koller, Sanjay Nayyar, Scott Barrett. A special mention goes to Teo Lachev, who made the deepest review. I would also like to thank T.K. Anand and Marin Bezic,who answered to my questions. Finally, special thanks are reserved for Claudio Civetta, Marcella Caviola and other people working for Cedacri spa (Italy): they trusted me enough to adopt many of the described patterns in a production environment and gave me an invaluable feedback.

i

Table of Contents
The many-to-many revolution............................................................................i Table of Contents ........................................................................................... iii Introduction ..................................................................................................... 1 Classical many-to-many relationship ............................................................... 2 Business scenario............................................................................................ 2 Implementation .............................................................................................. 2 Cascading many-to-many relationship ............................................................. 7 Business scenario ............................................................................................ 7Implementation ............................................................................................ 11 Survey.............................................................................................................20 Business scenario .......................................................................................... 21 Implementation............................................................................................ 22 Distinct Count .................................................................................................31 Business scenario .......................................................................................... 31 Implementation ............................................................................................ 32 Performance................................................................................................. 47 Multiple Groups ...............................................................................................50 Business scenario .......................................................................................... 50 Implementation............................................................................................ 52 Cross-Time ......................................................................................................57 Business scenario .......................................................................................... 57 Implementation ............................................................................................ 58 Transition Matrix...