Understand Microsoft's dramatically updated new release of its premier toolset for business intelligence
The first major update to Microsoft's state-of-the-art, complex toolset for business intelligence (BI) in years is now available and what better way to master it than with this detailed book from key members of the product's development team? If you're a database or data warehouse developer, this is the expert resource you need to build full-scale, multi-dimensional, database applications using Microsoft's new SQL Server 2012 Analysis Services and related tools.
Discover how to solve real-world BI problems by leveraging a slew of powerful new Analysis Services features and capabilities. These include the new DAX language, which is a more user-friendly version of MDX; PowerPivot, a new tool for performing simplified analysis of data; BISM, Microsoft's new Business Intelligence Semantic Model; and much more.
* Serves as an authoritative guide to Microsoft's new SQL Server 2012 Analysis Services BI product and is written by key members of the Microsoft Analysis Services product development team
* Covers SQL Server 2012 Analysis Services, a major new release with a host of powerful new features and capabilities
* Topics include using the new DAX language, a simplified, more user-friendly version of MDX; PowerPivot, a new tool for performing simplified analysis of data; BISM, Microsoft's new Business Intelligence Semantic Model; and a new, yet-to-be-named BI reporting tool
* Explores real-world scenarios to help developers build comprehensive solutions
Get thoroughly up to speed on this powerful new BI toolset with the timely and authoritative Professional Microsoft SQL Server 2012 Analysis Services with MDX.
Autorentext
Sivakumar Harinath is a senior program manager with the
SQL Server Analysis Services team.
Ronald Pihlgren is a senior tester with the SQL Server
Analysis Services team.
Denny Guang-Yeu Lee is a principal program manager with
the SQL Customer Advisory Team.
John Sirmon is a senior program manager with the SQL
Server Customer Advisory Team at Microsoft.
Robert M. Bruckner is a principal architect with the SQL
Server Reporting Services team.
Inhalt
INTRODUCTION xxxv
PART I: INTRODUCTION
CHAPTER 1: INTRODUCTION 3
BISM Multidimensional Mode 4
A Closer Look at Data Warehousing 5
Key Elements of a Data Warehouse 8
Fact Tables 8
Dimension Tables 9
Dimensions 10
Cubes 11
The Star Schema 12
The Snowfl ake Schema 13
Inmon Versus Kimball Different Approaches 14
Business Intelligence Is Data Analysis 15
BISM Tabular Mode 15
SQL Server Analysis Services 2012 17
The Business Intelligence Semantic Model 18
Data Model 19
Query Language 20
Data Access Technology 20
Summary 21
PART II: DESIGNING MULTIDIMENSIONAL BISM
CHAPTER 2: A FIRST LOOK AT MULTIDIMENSIONAL BISM 25
Development, Administrative, and Client Tools 26
Upgrading to Analysis Services 2012 26
Using SQL Server Data Tools to Build Analysis Services Multidimensional Applications 31
Creating a Project Using SQL Server Data Tools 32
The Solution Explorer 34
The Properties Window 35
The Output Window 35
Creating an Analysis Services Database Using SQL Server Data Tools 35
Creating a Data Source 36
Creating a Data Source View (DSV) 40
Creating a Cube Using the Cube Wizard 44
Deploying and Browsing a Cube 53
Using SQL Server Management Studio 56
The Object Explorer Pane 59
Querying Using the MDX Query Editor 61
Summary 63
CHAPTER 3: UNDERSTANDING MDX 65
What Is MDX? 66
MDX Concepts 66
Measures and Measure Groups 66
Hierarchies and Hierarchy Levels 67
Members 68
Cells 70
Tuples 72
Sets 72
MDX Queries 73
SELECT Statement and Axis Specification 74
FROM Clause and Cube Specification 75
Subselect Clauses 75
WHERE Clause and Slicer Specification 76
Slicer Dimension 76
WITH Clause, Named Sets, and Calculated Members 77
Named Sets 77
Calculated Members 79
Ranking and Sorting 81
MDX Expressions 82
MDX Operators 83
Arithmetic Operators 83
Set Operators 83
Comparison Operators 84
Logical Operators 84
Special MDX Operators Curly Braces, Commas, and Colons 84
MDX Functions 85
MDX Function Categories 85
Set Functions 85
Crossjoin 86
NONEMPTYCROSSJOIN and NONEMPTY 86
Filter and Having 87
Member Functions 87
Numeric Functions 88
Dimension Functions, Level Functions, and Hierarchy Functions 89
String Manipulation Functions 89
Other Functions 89
MDX Scripts 89
MDX Script Execution 90
CALCULATE Statement 90
Cube Space 92
AUTO EXISTS 93
Cell Calculations and Assignments 94
Recursion 97
Freeze Statement 97
Restricting Cube Space/Slicing Cube Data 98
SCOPE Statement 98
CREATE and DROP SUBCUBE 98
Using EXISTS 99
Using EXISTING 99
Using SUBSELECT 100
Parameterized MDX Queries 101
MDX Comments 102
Summary 102
CHAPTER 4: DATA SOURCES AND DATA SOURCE VIEWS 103
Data Sources 104
Data Sources Supported by Analysis Services 105
.NET Versus OLE DB Data Providers 109
.NET Framework Data Providers 109
OLE DB Data Providers 109
The Trade-Off s 110
Data Source Views 110
DSV Wizard 111
DSV Designer 111
Adding/Removing Tables in a DSV 113
Specifying Primary Keys and Relationships in the DSV 114