Delivering Business Intelligence with Microsoft SQL Server 2016, Fourth Edition, 4th Edition

Delivering Business Intelligence with Microsoft SQL Server 2016, Fourth Edition, 4th Edition

by Brian Larson
Released November 2016
Publisher(s): McGraw-Hill
ISBN: 9781259641497

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Distribute Actionable, Timely BI with Microsoft® SQL Server® 2016 and Power BI

Drive better, faster, more informed decision making across your organization using the expert tips and best practices featured in this hands-on guide. Delivering Business Intelligence with Microsoft SQL Server 2016, Fourth Edition, shows, step-by-step, how to distribute high-performance, custom analytics to users enterprise-wide. Discover how to build BI Semantic Models, create data marts and OLAP cubes, write MDX and DAX scripts, and share insights using Microsoft client tools. The book includes coverage of self-service business intelligence with Power BI.

• Understand the goals and components of successful BI
• Build data marts, OLAP cubes, and Tabular models
• Load and cleanse data with SQL Server Integration Services
• Manipulate and analyze data using MDX and DAX scripts and queries
• Work with SQL Server Analysis Services and the BI Semantic Model
• Author interactive reports using SQL Server Data Tools
• Create KPIs and digital dashboards
• Implement time-based analytics
• Embed data model content in custom applications using ADOMD.NET
• Use Power BI to gather, model, and visualize data in a self-service environment

Table of contents

  1. Cover
  2. About the Author
  3. Title Page
  4. Copyright Page
  5. Dedication
  6. Contents at a Glance
  7. Contents
  8. Acknowledgments
  9. Part I Business Intelligence
    1. Chapter 1 Equipping the Organization for Effective Decision Making
      1. Effective Decision Making
        1. Who Is a Decision Maker?
        2. What Is an Effective Decision?
      2. Keys to Effective Decision Making
        1. Are We Going Hither or Yon?
        2. Is Your Map Upside-Down?
        3. Panicked Gossip, the Crow’s Nest, or the Wireless
      3. Business Intelligence
        1. Business Intelligence and Microsoft SQL Server 2016
    2. Chapter 2 Making the Most of What You’ve Got: Using Business Intelligence
      1. What Business Intelligence Can Do for You
        1. Types of Data Exploration
      2. Business Intelligence at Many Levels
        1. The Top of the Pyramid
        2. Mid-Level
        3. The Broad Base
      3. Maximum Miniatures, Inc.
        1. Business Needs
        2. Current Systems
      4. Building the Foundation
    3. Chapter 3 Seeking the Source: The Source of Business Intelligence
      1. Seeking the Source
        1. Transactional Data
      2. The Data Mart
        1. Features of a Data Mart
        2. Data Mart Structure
      3. Why Analysis Services?
    4. Chapter 4 Two, Two, Two Models in One: The BI Semantic Model
      1. Online Analytical Processing
        1. The BI Semantic Model
      2. Building OLAP Out of Cubes: The Multidimensional Model
        1. Features of a Multidimensional Model Implementation
        2. Architecture of a Multidimensional Model Implementation
        3. The Parts of a Multidimensional Model Implementation
      3. Building OLAP Out of Tables: The Tabular Model
        1. Features of a Tabular Model
        2. Architecture of a Tabular Model
      4. Choosing an Implementation Method
      5. Tools of the Trade
    5. Chapter 5 First Steps: Beginning the Development of Business Intelligence
      1. SQL Server Data Tools
        1. Visual Studio
        2. Installing SQL Server Data Tools
        3. Navigating SQL Server Data Tools
        4. SQL Server Data Tools Options
      2. SQL Server Management Studio
        1. Installing SQL Server Management Studio
        2. The SQL Server Management Studio User Interface
      3. Don Your Hardhat
  10. Part II Defining Business Intelligence Structures
    1. Chapter 6 Building Foundations: Creating Data Marts
      1. Data Mart
        1. Who Needs a Data Mart Anyway?
      2. Designing a Data Mart
        1. Decision Makers’ Needs
        2. Available Data
        3. Data Mart Structures
        4. Creating a Data Mart Using the SQL Server Management Studio
      3. Creating a Data Mart Using SQL Server Data Tools
      4. Table Compression
        1. Types of Table Compression
      5. The Benefits of Integration
    2. Chapter 7 Transformers: Integration Services Structure and Components
      1. Integration Services
        1. Package Structure
      2. Package Items
        1. Control Flow
        2. Data Flow
      3. Getting Under the Sink
    3. Chapter 8 Fill ’er Up: Using Integration Services for Populating Data Marts
      1. Package Development Features
        1. Give It a Try
        2. Programming in Integration Services Packages
        3. Package Development Tools
      2. Change Data Capture
        1. Change Data Capture Architecture
        2. Loading a Data Mart Table from a Change Data Capture Change Table
      3. Loading a Fact Table
      4. Putting Integration Services Packages into Production
        1. Parameters
        2. The Integration Services Catalog
        3. Managing Packages in the Integration Services Catalog
        4. Executing Packages from the SSIS Catalog
      5. Meanwhile, Back at the BI Semantic Model
  11. Part III Working with a Tabular BI Semantic Model
    1. Chapter 9 Setting the Table: Creating a Tabular BI Semantic Model
      1. Preparation for Creating Tabular Models
        1. SQL Server Analysis Services Tabular Instance
        2. Managing the Workspace Database
      2. Creating a Tabular Model
        1. Data Sources for Our Tabular Models
        2. Measures in a Tabular Model
        3. Manually Creating a Time Table
        4. Relationships and Hierarchies
        5. Validating the Model Using the Analyze in Excel Feature
      3. A Second Tabular Model
        1. Data Persistence
        2. Calculated Columns
        3. Hierarchies Spanning Multiple Tables
      4. Putting More on the Table
    2. Chapter 10 A Fancy Table: Tabular BI Semantic Model Advanced Features
      1. Enhancing Our Tabular Model
        1. Table and Column Properties
        2. Adding Images to a Tabular Model
        3. Advanced Relationships and Filtering
        4. Parent/Child Relationships
        5. Multiple Relationships Between the Same Tables
        6. Partitions
        7. Perspectives
      2. DAX Expressions
        1. DAX Operators
        2. The Context for Measures and Calculated Columns
        3. Filtering Aggregates
        4. Table-Valued Functions
        5. Aggregate Functions
        6. DAX Functions for Time Analytics
        7. Additional DAX Functions
        8. Putting DAX Functions to Use
      3. Variables in the Tabular Model
        1. Declaring DAX Variables and Assigning a Value
        2. Using a DAX Variable
        3. Using Return in a Measure
      4. Deploying a Tabular Model
        1. Deploying from SQL Server Data Tools
        2. Completing the Deployment
        3. Deploying from the Analysis Services Deployment Wizard
      5. Another Model Awaits
  12. Part IV Working with a Multidimensional BI Semantic Model
    1. Chapter 11 Cubism: Measures and Dimensions
      1. Building a Multidimensional BI Semantic Model
        1. Creating a Cube
      2. Measures
        1. Measure Groups
        2. Made-Up Facts: Calculated Measures
        3. It Doesn’t Add Up: Measure Aggregates Other Than Sum
      3. Dimensions
        1. Managing Dimensions
        2. Relating Dimensions to Measure Groups
        3. Types of Dimensions
        4. Slowly Changing Dimensions
      4. You Are Special
    2. Chapter 12 Bells and Whistles: Special Features of OLAP Cubes
      1. Where No Cube Has Gone Before
        1. Deploying and Processing
        2. Deploying from SQL Server Data Tools
        3. Deploying from the Analysis Services Deployment Wizard
      2. Additional Cube Features
        1. Linked Objects
        2. The Business Intelligence Wizard
        3. Key Performance Indicators
        4. Actions
        5. Partitions
        6. Aggregation Design
        7. Perspectives
        8. Translations
        9. Creating Translations
      3. More Sophisticated Scripting
    3. Chapter 13 Writing a New Script: MDX Scripting
      1. Terms and Concepts
        1. Where Are We?
        2. Getting There from Here
      2. Putting MDX Scripting to Work
        1. Cube Security
        2. This Year–to–Last Year Comparisons and Year-to-Date Rollups
      3. Extracting Data from Cubes
    4. Chapter 14 Pulling It Out and Building It Up: MDX Queries
      1. The MDX SELECT Statement
        1. The Basic MDX SELECT Statement
        2. Additional Tools for Querying
        3. Additional Dimensions
      2. Additional MDX Syntax
        1. Operators
        2. Functions
      3. Feel the Power
  13. Part V Modeling and Visualizing with Power BI
    1. Chapter 15 Power to the People: Loading Data with Power BI
      1. The Power BI Architecture
        1. Many Manifestations
        2. Power BI Desktop
        3. PowerBI.com
        4. Power BI Content on Premises
        5. Power BI Mobile
        6. Constant Improvement
        7. Obtaining the Power BI Desktop Software
        8. The Power BI Data Engine
      2. Getting Started with Power BI: Gathering Data
        1. The Start Dialog Box
        2. Get Data
        3. Creating Your First Power BI Content
        4. Using Live Connection
        5. Connection Types and Direct Query
      3. Transforming Data During the Data Import
        1. The Power BI Query Editor
        2. A New Sample: World Population
        3. Transformations
        4. Advanced Editor and Power Query Formula Language
      4. Parameters
        1. Putting Parameters to Use
      5. Delivering the Power
    2. Chapter 16 I Can See Clearly Now: Data Visualization with Power BI Reporting
      1. Enhancing Power BI Data Models
        1. Relationships and Intermediate Tables
        2. Formatting and Categories
        3. Hierarchies
      2. Power BI Data Visualizations
        1. Basic Visualization
        2. High Interactivity
        3. Mapping
        4. Additional Visualization Elements
      3. DAX Calculations
        1. Calculated Column: Order Weight
        2. Time Analytics
        3. Context Override: Percent of Total
      4. Additional Power BI Features
        1. Roles
        2. Synonyms
        3. Import and Export
      5. PowerBI.com
        1. Signing In to PowerBI.com
        2. Publishing
        3. Refreshing Data Online
        4. On-premises Power BI Publishing
      6. Special Delivery
  14. Part VI Delivering
    1. Chapter 17 Special Delivery: Microsoft Business Intelligence Client Tools
      1. Front-End BI Tools from Microsoft
        1. Selecting a Front-End BI Tool
        2. A Bit of Knowledge Is a Big Help
      2. Reporting Services
      3. Reporting Services Paginated Reports
        1. Paginated Report Structure
        2. Paginated Report Delivery
        3. Data Regions
        4. Creating a Reporting Services Paginated Report
        5. Reporting Services Mobile Reports
        6. Creating a Reporting Services Mobile Report
        7. Reporting Services Key Performance Indicators
        8. Creating a Reporting Services Key Performance Indicator
      4. Excel
        1. Creating Pivot Tables and Pivot Charts
        2. Pivot Chart
      5. PerformancePoint Services in SharePoint
        1. Creating a Site Collection Using the Business Intelligence Center Template
        2. Creating a Dashboard Using PerformancePoint Services
      6. Putting It All Together
    2. Chapter 18 Let’s Get Together: Integrating Business Intelligence with Your Applications
      1. ADOMD.NET
        1. ADOMD.NET
        2. ADOMD.NET Example
      2. Integrating a Reporting Services Report with an Application
        1. Report Server
        2. Folders
        3. The Web Portal
        4. Deploying Reports Using SQL Server Data Tools
        5. Web Service Access
      3. Great Capabilities, Great Opportunities
  15. Index

Product information

  • Title: Delivering Business Intelligence with Microsoft SQL Server 2016, Fourth Edition, 4th Edition
  • Author(s): Brian Larson
  • Release date: November 2016
  • Publisher(s): McGraw-Hill
  • ISBN: 9781259641497