Professional Microsoft SQL Server 2012 : Analysis Services with MDX and DAX

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.

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

Customizing Your Tables in the DSV Designer 115

Data Source Views in Depth 119

Diagrams 119

Data Source View Properties 121

Diff erent Layouts in DSVs 123

Validating Your DSV and Initial Data Analysis 125

Multiple Data Sources Within a DSV 126

Summary 127

CHAPTER 5: DIMENSION DESIGN 129

Working with the Dimension Wizard 130

Working with the Dimension Designer 136

Attributes 137

Attribute Relationships 139

User Hierarchies 144

Browsing the Dimension 148

Sorting Members of a Level 157

Optimizing Attributes 159

Defining Translations in Dimensions 159

Creating a Snowflake Dimension 162

Creating a Time Dimension 165

Creating a Parent-Child Hierarchy 168

Summary 172

CHAPTER 6: CUBE DESIGN 173

The BISM Multidimensional Mode 173

Creating a Cube Using the Cube Wizard 176

Browsing Cubes 180

Cube Dimensions 184

Relationship Types 185

No Relationship 185

Regular Relationships 186

Fact Relationships 186

Many-to-Many Relationships 187

Data Mining Relationships 187

Referenced Relationships 187

Browsing Reference Dimensions in Excel 190

Measures and Measure Groups 192

Calculated Members 198

Calculated Measures 200

Querying Calculated Measures 203

Creating Perspectives 203

Creating Translations 205

Browsing Perspectives and Translations 206

Summary 208

CHAPTER 7: ADMINISTRATION AND MANAGEMENT 209

Administration Using SQL Server 2012 Tools 210

Managing Analysis Servers 210

Managing Analysis Services Objects 214

Database Creation 215

Processing Analysis Services Database Objects 217

Processing a Cube 221

Processing a Dimension 226

Managing Partitions 228

Managing Assemblies 234

Backup and Restore 237

Detach and Attach 241

Synchronization 244

Managing Security 249

Server and Database Roles 249

Enabling or Disabling Features 249

Online Mode 250

Programmatic and Advanced Administration 253

Analysis Management Objects (AMO) 253

Processing Analysis Services Databases 253

Back Up and Restore 258

Adding Assemblies to Analysis Services 259

PowerShell and Analysis Services 261

Resource and Activity Monitoring 261

HTTP Connectivity to Analysis Services 264

Analysis Services and FailOver Clustering 265

Summary 265

PART III: ADVANCED TOPICS IN BISM

CHAPTER 8: ADVANCED DIMENSION DESIGN 269

Custom Rollups 270

Enhancements to Parent-Child Hierarchies 280

Unary Operators 280

Specifying Names of Levels in a Parent-Child Hierarchy 286

Using Properties to Customize Dimensions 288

Ordering Dimension Members 289

The All Member, Default Member, and Unknown Member 289

Error Confi gurations for Processing 292

Storage Mode 293

Grouping Members 294

Dimension Intelligence Using the Business Intelligence Wizard 295

Account Intelligence 295

Time Intelligence 301

Dimension Intelligence 305

Server Time Dimension 307

Dimension Writeback 311

Summary 314

CHAPTER 9: ADVANCED CUBE DESIGN 315

Measure Groups and Measures 316

Adding and Enhancing Dimensions 321

Fact Dimensions 322

Many-to-Many Dimensions 323

Data Mining Dimensions 325

Role-Playing Dimensions 328

Adding Calculations to Your Cube 329

Key Performance Indicators (KPIs) 337

KPI Creation 337

KPIs in Depth 344

Using ADOMD.NET to Query KPIs 345

Drillthrough 347

Actions 348

Action Types 348

Action Target Types 348

URL Action 349

Browse URL Action in the Cube Browser 353

Report Actions 354

Drillthrough Action 356

Adding Intelligence to the Cube 362

Semiadditive Measures 363

Currency Conversion 365

Working with Partitions 371

Building a Local Partition 373

Building a Remote Partition 375

Storage Modes and Storage Settings 384

Building Aggregations 386

The Aggregation Design Process 389

Usage-Based Optimization 392

Real-Time Cubes 393

Long Latency Scenario 393

Caching After Data Change 394

Caching Using Timed Updates 396

Average Latency Scenario 397

Caching with MOLAP Storage Option 397

No Latency Scenario 401

Real-Time ROLAP Storage Option 402

Defining Security 403

Cell Writeback 407

Cell Writeback Prerequisites 408

Writeback Statement 409

Update Nonleaf Cell Value Using Allocation 411

Equal Allocation 411

Weighted Allocation 412

Incremental Allocation 413

Cautions 413

AMO Warnings 414

Design Experience 415

Dismissing Warnings 415

Warnings Designer 416

Summary 418

CHAPTER 10: DESIGNING MULITDIMENSIONAL BISM FOR PERFORMANCE 419

Optimizing Multidimensional BISM Design 422

Fine-Tuning Your Dimensions 422

Choosing the Right Key Attribute 422

Avoiding Unnecessary Attributes 423

Turning Off Optimization for Rarely Used Attributes 424

Turning Off Attribute Hierarchy for Member Properties 425

Defining Relationships between Attributes 425

Fine-Tuning Your Cube 427

Fact Table Í Measure Groups or Partitions 427

Optimizing Reference Dimensions 429

Many-to-Many Dimensions 429

Partitions 430

Merging Partitions 431

Partition Slices 434

Partition Slices and ROLAP 434

A Partition Slice Example 435

Distinct Count Partitioning 437

Optimizing for Processing 439

Creating Partitions to Speed Up Processing 441

Choosing Small and Appropriate Data Types and Sizes 441

SQL Server and Analysis Services Installations 442

Optimizing a Relational Data Source 442

Avoiding Excessive Aggregation Design 443

Using Incremental Processing When Appropriate 443

Parallelism during Processing 445

Identifying Resource Bottlenecks 449

Designing Aggregations 450

Understanding Aggregations 451

Creating Aggregations 453

Applying Aggregation Design 459

Usage-Based Aggregation Design 462

Aggregation Design Options 468

Designing Efficient Aggregations Using Hints 468

Relationships between Attributes 468

Properties Controlling Attributes and Aggregation Design 471

Managing Aggregation Designs 474

Scalability Optimizations 475

Configuring Server Configuration Properties 476

Scaling Out 477

Scaling Up 477

Handling Large Dimensions 478

Summary 478

CHAPTER 11: OPTIMIZING QUERY PERFORMANCE 479

How OLAP Enhances Performance 480

The Calculation Model 480

MDX Scripts 481

Scope and Assignments 483

Dimension Attribute Calculations 483

Session and Query Calculations 484

Query Execution Architecture 485

Analysis Services Engine Components 485

Stages of Query Execution 486

Query Evaluation Modes 487

Cell-by-Cell Mode 487

Subspace Computation 489

Performance Analysis and Tuning Tools 492

SQL Server Profi ler 492

Analysis Services Trace Events 497

Performance Monitor 498

Task Manager 501

SQL Server Management Studio 502

SQL Server Data Tools 502

Server Properties 502

Analyzing Query Performance Issues 503

Understanding FE and SE Characteristics 504

Common Solutions for Slow Queries 504

Large Storage Engine Requests 504

Several Storage Engine Requests 505

Formula Engine-Intensive Query 505

Query Optimization Techniques 505

Using NON EMPTY on Axes 506

Using Non Empty for Filtering and Sorting 508

Using SCOPE Versus IIF and CASE 509

Auto Exists Versus Properties 509

Member Value Versus Properties 509

Move Simple Calculations to Data Source View 510

Features Versus MDX Scripts 510

Scale Out with Read-Only Database 510

Writeback Query Performance 512

Summary 512

CHAPTER 12: DATA MINING, MULTIDIMENSIONAL BISM, AND DATA MINING ADD-INS FOR OFFICE 2010 513

The Data Mining Process 514

Topic Area Understanding 515

Data: Understand It, Configure It 516

Choose the Right Algorithm 517

Train, Analyze, and Predict 517

Real-World Applications 518

Fraud Detection 518

Increasing Profi ts in Retail 519

Data Mining in the NBA 519

Data Mining in Call Centers 519

Data Mining Algorithms in SQL Server Analysis Services 2012 520

Microsoft Decision Trees 521

Microsoft Naïve Bayes 521

Microsoft Clustering 522

Microsoft Sequence Clustering 522

Microsoft Association Rules 522

Microsoft Neural Network 522

Microsoft Time Series 523

Microsoft Linear Regression 523

Microsoft Logistic Regression 523

Working with Mining Models 524

Relational Mining Model 524

OLAP Mining Models 548

Analyzing the Cube with a Data Mining Dimension 555

Using Data Mining with Office 2010 557

Table Analytics 558

Analyze Key Influencers 559

Detect Categories 562

Fill from Example 565

Forecast 566

Highlight Exceptions 568

Shopping Basket Analysis 569

Scenario Analysis 571

Data Mining Tools 571

Explore Data Wizard 572

Clean Data: Outliers and Re-label Wizards 574

Sample Data Wizard 576

Classification Model 577

Model Accuracy 579

Classification Matrix 582

Visio Add-in 584

The Decision Tree Shape 584

The Cluster Shape Wizard 588

The Dependency Shape Wizard 592

Summary 594

CHAPTER 13: SQL INTEGRATION SERVICES AND SQL SERVER REPORTING SERVICES WITH MULTIDIMENSIONAL

BISM MODELS 597

SQL Server Integration Services 599

Creating an Integration Services Project 599

Creating Integration Services Packages for Analysis Services Operations 600

The Execute DDL Task 601

Processing an Analysis Services Object 611

Loading Data into an Analysis Services Partition 613

Deploying the Integration Services Project 622

Integration Services Tasks for Data Mining 626

Automating Execution of SSIS Packages 627

Monitoring SSIS Package Executions 634

SQL Server Reporting Services 635

Report Designer, Report Builder 635

Report Defi nition Language 636

Report Wizard 636

Report Server 636

Creating a Report on a Relational Database 637

Connecting and Deploying to a Report Server 644

Creating a Report on a Multidimensional Model 648

Designing Your Analysis Services Report 649

Enhancing Your Analysis Services Report 654

Enhancing Your Report Using Extended Properties 662

Custom Aggregates 665

Summary 669

CHAPTER 14: SECURING MULTIDIMENSIONAL BISM 671

Securing Your Source Data 672

Securing Your Dimension Data 674

A Scenario Using Dimension Security 674

The User-Role Approach 682

The Access-Role Approach 697

The Member Property Approach 698

The Security Measure Group Approach 700

The External Function Approach 703

Securing Your Cube Data 705

Scenario Using Cell Security 706

Summary 715

PART IV: POWERPIVOT AND TABULAR BISM

CHAPTER 15: SELF-SERVICE BUSINESS INTELLIGENCE AND INTRODUCTION TO POWERPIVOT 719

SQL Server 2012 720

Self-Service Business Intelligence 721

PowerPivot: Microsoft’s Implementation of SSBI 722

PowerPivot Applications 722

PowerPivot for Excel 723

PowerPivot for SharePoint 732

The Analysis Services Engine in VertiPaq Mode 736

Summary 739

CHAPTER 16: A FIRST LOOK AT TABULAR BISM 741

Tabular Mode Projects in SSDT 742

Setting Up a Tabular Instance of Analysis Services 2012 742

Creating a Tabular Project Using SQL Server Data Tools 743

Workspace Server and Deployment Server 744

Importing Data 746

The Tabular Designer 748

The Model Menu 750

Working with Tables in the Designer 752

Working with Columns in the Designer 756

Relationships 759

Adding Calculations to Your Tabular Model 761

Calculated Columns 761

Measures 762

Browsing the Model 765

Modeling and Deploying 767

Administering Your Tabular Model Using SSMS 769

Summary 772

CHAPTER 17: ENHANCING YOUR TABULAR BISM 773

Sourcing Data for Your Model 773

Refining Your Tabular Model 774

Changing the Model 774

Adding a New Table 774

Modifying an Existing Table 775

Hiding and Deleting Tables, Columns, and Measures 776

Creating a Date Table 777

Creating and Managing Relationships 777

Column Operations 777

Filtering and Sorting 777

Configuring for PowerPivot and Power View 779

Enhancing Your Model with Hierarchies 779

Creating a Date Hierarchy 780

Making Use of Time Intelligence Functions 783

Creating a Geography Hierarchy 785

Creating a Product Hierarchy by Combining Columns from Different Tables 786

Creating Parent/Child Hierarchies 788

Enhancing Measures 788

Building Explicit Measures with DAX 788

Implicit Measures 790

Change Measure Properties 792

Mimicking the Multidimensional Model 792

Building Semi-Additive Measures 794

Creating KPIs 794

Creating Perspectives 798

Creating Partitions 800

DirectQuery Partitions 804

Processing a Tabular Database 808

Creating and Applying Security Roles 809

Understanding Security Roles 809

Row Filters 810

Summary 812

CHAPTER 18: INTRODUCTION TO DAX 813

Sample Data 814

DAX Fundamental Concepts 815

DAX Syntax 815

DAX Data Types 816

Calculated Columns and Measures 816

Row Context and Filter Context 816

DAX Operators and Blank Values 820

DAX Function Categories with Examples 821

Scalar DAX Functions 822

Statistical DAX Functions 829

Other DAX Functions 835

RELATED and RELATEDTABLE 838

LOOKUPVALUE 839

EARLIER and EARLIEST 840

VALUES and DISTINCT 840

FILTER 841

CALCULATE, ALL, and ALLSELECTED 842

Time Intelligence Functions 846

Context Information Functions 852

DAX as a Query Language 853

DAX and MDX 864

Summary 865

CHAPTER 19: ADVANCED TOPICS IN DAX 867

Parent-Child Hierarchies 868

Cross-Filtering with Many-to-Many Relationships 876

Filtering 877

Cross-Filtering 877

Cross-Filtering with DAX 878

Multiple Relationships Between Tables 887

Multiple Instances of the Table 888

Multiple Relationships Between Two Tables 891

Time-Based Analysis 895

Moving Average 895

Opening and Closing Balance 900

Non-Aggregatable Columns 904

Summary 905

CHAPTER 20: ANALYZING MULTIDIMENSIONAL AND TABULAR BISMS IN EXCEL 907

Analyzing Data in Excel 2010 908

Analyzing Data Using Pivot Tables 908

Creating a Pivot Table from a Tabular Model Using Analysis Services Data 909

Creating a Pivot Table from a Multidimensional Model 912

Updating SSAS Connection Information 913

Analyzing Data in Pivot Tables 914

Filtering in Pivot Tables 921

Drilling Down to Detailed Data 925

Analyzing Multiple Measures 929

Custom Grouping 930

Organizing Attributes in the PivotTable Field List 935

Number Formatting 936

Highlighting Exceptions 937

Viewing Member Properties 939

Sorting Data 941

Filtering Data 942

Style and Design 948

Excel 2010 Conditional Formatting 949

Perspectives and Translations 950

Key Performance Indicators 954

Named Sets 956

Sheet Data Reports 956

Cube Functions in Excel 2010 960

Pivot Charts 962

Local Cubes 963

Excel Services 966

Summary 967

CHAPTER 21: POWERPIVOT FOR SHAREPOINT 969

SharePoint 2010 970

Excel Services 970

Comparing Excel and Excel Services 970

Excel Services and PowerPivot 971

Key Servers in PowerPivot for SharePoint 973

SharePoint Web Front End (WFE) 973

SharePoint Application Servers (App Servers) 974

SharePoint Content Databases (Content dBs) 974

Key Services in PowerPivot for SharePoint 975

Analysis Services Engine Service 975

PowerPivot Mid-Tier Service 978

Connectivity 978

Data Refresh 978

Services Architecture Workfl ow Scenarios 980

Excel Client Upload to SharePoint 980

Excel Services Rendering 980

Excel Services Server Action 981

What’s New 983

New Administrative Capabilities 983

Setup Improvements 983

PowerPivot for SharePoint 984

Installation and Configuration 984

New Server Installation 984

Existing Farm Installation 985

Scale Out Implementations 985

Scale Out SQL 985

Homogeneous Scale Out 986

Heterogeneous Scale Out 986

Verifying Your PowerPivot for SharePoint Setup 986

Publishing Your Excel Workbooks 986

Viewing Workbooks in PowerPivot Gallery 988

Viewing Workbooks in Excel Services 991

Optional Setup Steps 991

Configuring File Size Limits 991

Turning Off the External Data Warning on Data Refresh 993

Integrating Reporting Services 994

BI Appliances 994

Summary 995

PART V: ADVANCED TOPICS WITH TABULAR BISM AND INTEGRATION WITH POWER VIEW

CHAPTER 22: INTRODUCTION AND CONFIGURATION OF POWER VIEW 999

Reporting Services Power View 999

Power View 1000

Reporting Services 1001

Reporting Services Add-in 1001

Reporting Services Service Application 1002

PowerPivot for SharePoint 1002

Installing Reporting Services for SharePoint 1002

SharePoint Site Settings 1010

Preparing Model Connection for Power View Tutorial 1012

Deploying the FAA Flight Data Model 1012

Creating SharePoint Image Library for FAA Airline Images 1014

Publishing FAA Workbook Directly to PowerPivot Gallery 1014

Creating Data Source Connections for Power View 1016

BI Semantic Model (BISM) Connection File 1016

Report Data Source (RSDS) Connection 1017

Configuring Data Source Connections 1018

Connecting to PowerPivot Workbooks 1020

Connecting to a BISM Connection File 1021

Connecting to an RSDS 1022

Authentication Scenarios 1023

Comparison and Trade-offs 1025

Kerberos Delegation with BISM or RSDS 1025

BISM Connection and RS Service Account 1025

RSDS and Stored Windows Credentials 1026

RSDS with Impersonation and Eff ectiveUser 1028

Summary 1030

Resources 1030

CHAPTER 23: VISUAL ANALYTICS WITH POWER VIEW 1031

Introduction to Power View 1031

Visual Analytics with Power View 1036

Getting Started with Power View 1036

Creating a New Power View Report 1036

Opening an Existing Power View Report 1037

Introduction to the Power View Design Experience 1038

Creating a Table Visualization 1039

Converting Visualizations 1041

Sorting Inside Charts 1042

Expanding Visualizations 1043

Filtering in Views 1044

Multiple Views 1047

Saving Reports 1051

Permissions for Power View 1053

Visualizations and Interactivity 1053

Tile Visualizations 1054

Highlighting in Visualizations 1057

Matrix 1058

Slicers 1059

Filters 1060

Card, Callout Views 1061

Zooming in Charts 1063

Scatter and Bubble 1064

Animated Timeline Charts 1065

Refreshing Data in a Power View Report 1068

Presenting and Exporting in Power View 1069

Reading and Presentation Mode 1069

Printing Views 1071

PowerPoint Export and Interactivity 1071

Tips and Tricks for Power View 1074

Summary 1076

Resources 1076

INDEX 1077

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.