Delivering Business Intelligence with Microsoft SQL Server 2016

Description

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