Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX
Description
More details
Other editions
Additional editions
Persons
Content
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
