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.

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 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 I 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 Naive 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