Sql2012As Multidimensional Modeling

Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services

Microsoft Business Intelligence Technical Article
Authors
Hitachi Consulting:
Liz Vitt – Author
Scott Cameron – Author
Hilary Feier - Reviewer
Microsoft:
T.K. Anand - Reviewer
Ashvini Sharma - Reviewer
Published: May 2012
Applies to: SQL Server 2012 Analysis Services
Summary: This whitepaper provides practical guidance to help BI professionals and decision makers decide whether SQL Server 2012 Analysis Services tabular or multidimensional modeling provides the best fit for your next BI solution.
Copyright

This document is provided “as is.” Information and views expressed in this document, including URL and other Internet website references, may change without notice. You bearthe risk of using it.
This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.
© 2012 Microsoft Corporation. All rights reserved.

Contents
Introduction 5
BISM Modeling Primer 5
Multidimensional Modeling 5
Tabular Modeling 6
BISM Client Analysis Tools 7Data Model 7
Data Relationships 7
One-to-Many Relationships 7
Many-to-Many Relationships 7
Reference Relationships 8
Hierarchies 9
Standard Hierarchies 9
Ragged Hierarchies 9
Parent-Child Hierarchies 9
Additional Modeling Features 10
Business Logic 12
Row-Level Transformations 12
Aggregated Values 13
Calculations 13
Business Logic Scenarios 15
Hierarchy Logic 15Custom Rollups 15
Semiadditive Measures 16
Time intelligence 17
KPIs 17
Currency Conversion 17
Named Sets 17
Data Access and Storage 19
Performance and Scalability 19
Multidimensional Models 19
Tabular Models 20
Programmability 22
Security 22
Row/Attribute-Level Security 23
Dynamic Security 23
Cell-Level and Advanced Security 24
Summary 25
For More Information 29Introduction
Data modeling is a discipline that has been practiced for many years by BI professionals with one common goal: organizing disparate data into an analytic model that effectively and efficiently supports the reporting and analysis needs of the business. As data modeling evolves through the years with new technologies and tools, organizations face the growing challenge of effectivelyblending their modeling paradigms in a seamless and coherent manner that not only satisfies diverse analysis needs but also provides a common analysis experience to the business.
With the release of SQL Server 2012, Microsoft addresses this goal and challenge with the introduction of the BI Semantic Model (BISM), a single model that can support a broad range of reporting and analysis while blendingtwo Analysis Services modeling experiences behind the scenes:
* Multidimensional modeling, introduced with SQL Server 7.0 OLAP Services and continuing through SQL Server 2012 Analysis Services, enables BI professionals to create sophisticated multidimensional cubes using traditional online analytical processing (OLAP).
* Tabular modeling, introduced with PowerPivot for Microsoft Excel 2010,provides self-service data modeling capabilities to business and data analysts. The tabular modeling experience is more accessible to these users, many who have spent years working with data in desktop productivity tools like Excel and Microsoft Access. In SQL Server 2012, tabular modeling has been extended to enable BI professionals to create tabular models in Analysis Services or to import atabular model from PowerPivot into Analysis Services. Note that a PowerPivot model cannot be imported into an Analysis Services multidimensional model.
The goal of this white paper is to provide practical guidance to help you decide which SQL Server 2012 Analysis Services modeling experience – tabular or multidimensional - is the best fit for your next BI solution. The product descriptions and...