Delivering Business Intelligence with Microsoft SQL Server 2016

About the Author......Page 2
Title Page......Page 4
Copyright Page......Page 6
Dedication......Page 8
Contents at a Glance......Page 9
Contents......Page 11
Acknowledgments......Page 20
Part I Business Intelligence......Page 22
Chapter 1 Equipping the Organization for Effective Decision Making......Page 23
Effective Decision Making......Page 24
Who Is a Decision Maker?......Page 25
What Is an Effective Decision?......Page 26
Keys to Effective Decision Making......Page 27
Are We Going Hither or Yon?......Page 28
Is Your Map Upside-Down?......Page 29
Panicked Gossip, the Crow’s Nest, or the Wireless......Page 31
Business Intelligence......Page 33
Business Intelligence and Microsoft SQL Server 2016......Page 34
Chapter 2 Making the Most of What You’ve Got: Using Business Intelligence......Page 35
What Business Intelligence Can Do for You......Page 36
Types of Data Exploration......Page 37
Business Intelligence at Many Levels......Page 38
The Top of the Pyramid......Page 40
Mid-Level......Page 41
Maximum Miniatures, Inc.......Page 42
Business Needs......Page 43
Current Systems......Page 44
Building the Foundation......Page 45
Chapter 3 Seeking the Source: The Source of Business Intelligence......Page 46
Seeking the Source......Page 47
Transactional Data......Page 48
The Data Mart......Page 51
Features of a Data Mart......Page 52
Data Mart Structure......Page 54
Why Analysis Services?......Page 67
Chapter 4 Two, Two, Two Models in One: The BI Semantic Model......Page 69
The BI Semantic Model......Page 71
Building OLAP Out of Cubes: The Multidimensional Model......Page 72
Features of a Multidimensional Model Implementation......Page 76
Architecture of a Multidimensional Model Implementation......Page 78
The Parts of a Multidimensional Model Implementation......Page 80
Features of a Tabular Model......Page 88
Architecture of a Tabular Model......Page 89
Choosing an Implementation Method......Page 90
Tools of the Trade......Page 91
Chapter 5 First Steps: Beginning the Development of Business Intelligence......Page 92
SQL Server Data Tools......Page 93
Visual Studio......Page 94
Navigating SQL Server Data Tools......Page 95
SQL Server Data Tools Options......Page 114
Installing SQL Server Management Studio......Page 116
The SQL Server Management Studio User Interface......Page 117
Don Your Hardhat......Page 122
Part II Defining Business Intelligence Structures......Page 123
Chapter 6 Building Foundations: Creating Data Marts......Page 125
Data Mart......Page 126
Who Needs a Data Mart Anyway?......Page 127
Designing a Data Mart......Page 130
Decision Makers’ Needs......Page 131
Available Data......Page 132
Data Mart Structures......Page 133
Creating a Data Mart Using the SQL Server Management Studio......Page 147
Creating a Data Mart Using SQL Server Data Tools......Page 160
Table Compression......Page 179
Types of Table Compression......Page 180
The Benefits of Integration......Page 183
Chapter 7 Transformers: Integration Services Structure and Components......Page 184
Integration Services......Page 185
Package Structure......Page 186
Control Flow......Page 201
Data Flow......Page 257
Getting Under the Sink......Page 337
Chapter 8 Fill ’er Up: Using Integration Services for Populating Data Marts......Page 338
Package Development Features......Page 339
Give It a Try......Page 340
Programming in Integration Services Packages......Page 352
Package Development Tools......Page 365
Change Data Capture......Page 379
Change Data Capture Architecture......Page 380
Loading a Data Mart Table from a Change Data Capture Change Table......Page 385
Loading a Fact Table......Page 390
Parameters......Page 410
The Integration Services Catalog......Page 414
Managing Packages in the Integration Services Catalog......Page 429
Executing Packages from the SSIS Catalog......Page 437
Meanwhile, Back at the BI Semantic Model......Page 440
Part III Working with a Tabular BI Semantic Model......Page 441
Chapter 9 Setting the Table: Creating a Tabular BI Semantic Model......Page 443
Managing the Workspace Database......Page 445
Data Sources for Our Tabular Models......Page 449
Measures in a Tabular Model......Page 463
Manually Creating a Time Table......Page 466
Relationships and Hierarchies......Page 470
Validating the Model Using the Analyze in Excel Feature......Page 476
A Second Tabular Model......Page 478
Data Persistence......Page 479
Calculated Columns......Page 487
Hierarchies Spanning Multiple Tables......Page 491
Putting More on the Table......Page 499
Chapter 10 A Fancy Table: Tabular BI Semantic Model Advanced Features......Page 500
Table and Column Properties......Page 502
Adding Images to a Tabular Model......Page 503
Parent/Child Relationships......Page 507
Multiple Relationships Between the Same Tables......Page 512
Partitions......Page 515
Perspectives......Page 518
DAX Operators......Page 520
The Context for Measures and Calculated Columns......Page 522
Filtering Aggregates......Page 524
Table-Valued Functions......Page 532
Aggregate Functions......Page 536
DAX Functions for Time Analytics......Page 541
Additional DAX Functions......Page 547
Putting DAX Functions to Use......Page 556
Variables in the Tabular Model......Page 559
Using a DAX Variable......Page 560
Deploying a Tabular Model......Page 561
Deploying from SQL Server Data Tools......Page 562
Completing the Deployment......Page 563
Deploying from the Analysis Services Deployment Wizard......Page 564
Another Model Awaits......Page 571
Part IV Working with a Multidimensional BI Semantic Model......Page 572
Chapter 11 Cubism: Measures and Dimensions......Page 574
Building a Multidimensional BI Semantic Model......Page 575
Creating a Cube......Page 576
Measures......Page 583
Measure Groups......Page 584
Made-Up Facts: Calculated Measures......Page 586
It Doesn’t Add Up: Measure Aggregates Other Than Sum......Page 590
Managing Dimensions......Page 596
Relating Dimensions to Measure Groups......Page 604
Types of Dimensions......Page 606
Slowly Changing Dimensions......Page 609
You Are Special......Page 614
Chapter 12 Bells and Whistles: Special Features of OLAP Cubes......Page 615
Deploying and Processing......Page 617
Deploying from SQL Server Data Tools......Page 618
Deploying from the Analysis Services Deployment Wizard......Page 627
Additional Cube Features......Page 639
Linked Objects......Page 640
The Business Intelligence Wizard......Page 643
Key Performance Indicators......Page 645
Actions......Page 652
Partitions......Page 655
Aggregation Design......Page 676
Perspectives......Page 681
Translations......Page 682
Creating Translations......Page 683
More Sophisticated Scripting......Page 685
Chapter 13 Writing a New Script: MDX Scripting......Page 686
Terms and Concepts......Page 687
Where Are We?......Page 688
Getting There from Here......Page 711
Cube Security......Page 720
This Year–to–Last Year Comparisons and Year-to-Date Rollups......Page 733
Extracting Data from Cubes......Page 739
Chapter 14 Pulling It Out and Building It Up: MDX Queries......Page 740
The MDX SELECT Statement......Page 742
The Basic MDX SELECT Statement......Page 743
Additional Tools for Querying......Page 759
Additional Dimensions......Page 772
Operators......Page 775
Functions......Page 778
Feel the Power......Page 782
Part V Modeling and Visualizing with Power BI......Page 784
Chapter 15 Power to the People: Loading Data with Power BI......Page 786
Power BI Desktop......Page 788
PowerBI.com......Page 789
Power BI Content on Premises......Page 790
The Power BI Data Engine......Page 791
The Start Dialog Box......Page 792
Get Data......Page 793
Creating Your First Power BI Content......Page 797
Using Live Connection......Page 807
Connection Types and Direct Query......Page 813
Transforming Data During the Data Import......Page 815
The Power BI Query Editor......Page 816
A New Sample: World Population......Page 818
Transformations......Page 826
Advanced Editor and Power Query Formula Language......Page 849
Parameters......Page 855
Putting Parameters to Use......Page 856
Delivering the Power......Page 863
Chapter 16 I Can See Clearly Now: Data Visualization with Power BI Reporting......Page 864
Enhancing Power BI Data Models......Page 865
Relationships and Intermediate Tables......Page 866
Formatting and Categories......Page 876
Hierarchies......Page 884
Basic Visualization......Page 887
High Interactivity......Page 897
Mapping......Page 904
Additional Visualization Elements......Page 908
Calculated Column: Order Weight......Page 909
Time Analytics......Page 911
Context Override: Percent of Total......Page 915
Roles......Page 917
Synonyms......Page 920
Import and Export......Page 921
Publishing......Page 922
Refreshing Data Online......Page 926
Special Delivery......Page 927
Part VI Delivering......Page 928
Chapter 17 Special Delivery: Microsoft Business Intelligence Client Tools......Page 929
Selecting a Front-End BI Tool......Page 931
Reporting Services......Page 933
Reporting Services Paginated Reports......Page 934
Paginated Report Structure......Page 935
Paginated Report Delivery......Page 938
Data Regions......Page 939
Creating a Reporting Services Paginated Report......Page 942
Reporting Services Mobile Reports......Page 959
Creating a Reporting Services Mobile Report......Page 961
Reporting Services Key Performance Indicators......Page 978
Creating a Reporting Services Key Performance Indicator......Page 979
Excel......Page 985
Creating Pivot Tables and Pivot Charts......Page 986
Pivot Chart......Page 998
Creating a Site Collection Using the Business Intelligence Center Template......Page 1002
Creating a Dashboard Using PerformancePoint Services......Page 1006
Putting It All Together......Page 1026
Chapter 18 Let’s Get Together: Integrating Business Intelligence with Your Applications......Page 1028
ADOMD.NET......Page 1030
ADOMD.NET Example......Page 1032
Folders......Page 1040
The Web Portal......Page 1041
Deploying Reports Using SQL Server Data Tools......Page 1043
Web Service Access......Page 1047
Great Capabilities, Great Opportunities......Page 1054
Index......Page 1056