You are previewing Delivering Business Intelligence with Microsoft® SQL Server® 2012, Third Edition.
O'Reilly logo
Delivering Business Intelligence with Microsoft® SQL Server® 2012, Third Edition

Book Description

Implement a Robust BI Solution with Microsoft SQL Server 2012

Equip your organization for informed, timely decision making using the expert tips and best practices in this practical guide. Delivering Business Intelligence with Microsoft SQL Server 2012, Third Edition explains how to effectively develop, customize, and distribute meaningful information to users enterprise-wide. Learn how to build data marts and create BI Semantic Models, work with the MDX and DAX languages, and share insights using Microsoft client tools. Data mining and forecasting are also covered in this comprehensive resource.

• Understand the goals and components of successful BI

• Design, deploy, and manage data marts and OLAP cubes

• 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

• Use data mining to identify patterns, correlations, and clusters

• Implement time-based analytics

• Embed BI reports in custom applications using ADOMD.NET

Table of Contents

  1. Cover 
  2. Half Title
  3. About the Author
  4. Title
  5. Copyright
  6. Dedication
  7. Contents at a Glance
  8. Contents 
  9. Acknowledgments
  10. The Maximum Miniatures Databases and Other Supporting Materials
  11. Part I Business Intelligence
    1. Chapter 1. Equipping the Organization for Effective Decision Making
      1. Effective Decision Making
      2. Who Is a Decision Maker?
      3. What Is an Effective Decision?
      4. Keys to Effective Decision Making
      5. Are We Going Hither or Yon?
      6. Is Your Map Upside-Down?
      7. Panicked Gossip, the Crow’s Nest, or the Wireless
      8. Business Intelligence
      9. Business Intelligence and Microsoft SQL Server 2012
    2. Chapter 2. Making the Most of What You’ve Got—Using Business Intelligence
      1. What Business Intelligence Can Do for You
      2. When We Know What We Are Looking For
      3. Discovering New Questions and Their Answers
      4. Business Intelligence at Many Levels
      5. The Top of the Pyramid
      6. Mid-Level
      7. The Broad Base
      8. Maximum Miniatures, Inc
      9. Business Needs
      10. Current Systems
      11. Building the Foundation
    3. Chapter 3. Seeking the Source—The Source of Business Intelligence
      1. Seeking the Source
      2. Transactional Data
      3. The Data Mart
      4. Features of a Data Mart
      5. Data Mart Structure
      6. Snowflakes, Stars, and Analysis Services
    4. Chapter 4. Two, Two, Two Models in One—The BI Semantic Model
      1. Online Analytical Processing
      2. The BI Semantic Model
      3. Building OLAP Out of Cubes—The Multidimensional Model
      4. Features of a Multidimensional Model Implementation
      5. Architecture of a Multidimensional Model Implementation
      6. The Parts of a Multidimensional Model Implementation
      7. Building OLAP Out of Tables—The Tabular Model
      8. Features of a Tabular Model
      9. Architecture of a Tabular Model
      10. Choosing an Implementation Method
      11. Tools of the Trade
    5. Chapter 5. First Steps—Beginning the Development of Business Intelligence
      1. SQL Server Data Tools
      2. Visual Studio
      3. Navigating SQL Server Data Tools
      4. SQL Server Data Tools Options
      5. The SQL Server Management Studio
      6. The SQL Server Management Studio User Interface
      7. Don Your Hardhat
  12. Part II Defining Business Intelligence Structures
    1. Chapter 6. Building Foundations—Creating Data Marts
      1. Data Mart
      2. Who Needs a Data Mart Anyway?
      3. Designing a Data Mart
      4. Decision Makers’ Needs
      5. Available Data
      6. Data Mart Structures
      7. Creating a Data Mart Using the SQL Server Management Studio
      8. Creating a Data Mart Using SQL Server Data Tools
      9. Table Compression
      10. Types of Table Compression
      11. The Benefits of Integration
    2. Chapter 7. Transformers—Integration Services Structure and Components
      1. Integration Services
      2. Package Structure
      3. Package Items
      4. Control Flow
      5. Data Flow
      6. Getting Under the Sink
    3. Chapter 8. Fill ’er Up—Using Integration Services for Populating Data Marts
      1. Package Development Features
      2. Give It a Try
      3. Programming in Integration Services Packages
      4. Package Development Tools
      5. Change Data Capture
      6. Change Data Capture Architecture
      7. Loading a Data Mart Table from a Change Data Capture Change Table
      8. Loading a Fact Table
      9. Putting Integration Services Packages into Production
      10. Parameters
      11. The Integration Services Catalog
      12. Managing Packages in the Integration Services Catalog
      13. Executing Packages from the SSIS Catalog
      14. Meanwhile, Back at the BI Semantic Model
  13. Part III Working with a Multidimensional BI Semantic Model
    1. Chapter 9. Cubism—Measures and Dimensions
      1. Building a Multidimensional BI Semantic Model
      2. Creating a Cube
      3. Measures
      4. Measure Groups
      5. Made-up Facts—Calculated Measures
      6. It Doesn’t Add Up—Measure Aggregates Other Than Sum
      7. Dimensions
      8. Managing Dimensions
      9. Relating Dimensions to Measure Groups
      10. Types of Dimensions
      11. Slowly Changing Dimensions
      12. You Are Special
    2. Chapter 10. Bells and Whistles—Special Features of OLAP Cubes
      1. Where No Cube Has Gone Before
      2. Deploying and Processing
      3. Deploying from SQL Server Data Tools
      4. Deploying from the Analysis Services Deployment Wizard
      5. Additional Cube Features
      6. Linked Objects
      7. The Business Intelligence Wizard
      8. Key Performance Indicators
      9. Actions
      10. Partitions
      11. Aggregation Design
      12. Perspectives
      13. Translations
      14. More Sophisticated Scripting
    3. Chapter 11. Writing a New Script—MDX Scripting
      1. Terms and Concepts
      2. Where Are We?
      3. Getting There from Here
      4. Putting MDX Scripting to Work
      5. Cube Security
      6. This Year-to-Last Year Comparisons and Year-to-Date Rollups
      7. Extracting Data from Cubes
    4. Chapter 12. Pulling It Out and Building It Up—MDX Queries
      1. The MDX SELECT Statement
      2. The Basic MDX SELECT Statement
      3. Additional Tools for Querying
      4. Additional Dimensions
      5. Additional MDX Syntax
      6. Operators
      7. Functions
      8. Tabling the Matter
  14. Part IV Working with a Tabular BI Semantic Model
    1. Chapter 13. Setting the Table—Creating a Tabular BI Semantic Model
      1. Preparation for Creating Tabular Models
      2. SQL Server Analysis Services Tabular Instance
      3. Managing the Workspace Database
      4. Creating a Tabular Model
      5. Data Sources for Our Tabular Models
      6. Measures in a Tabular Model
      7. Manually Creating a Time Table
      8. Relationships and Hierarchies
      9. Validating the Model Using the Analyze in Excel Feature
      10. A Second Tabular Model
      11. Data Persistence
      12. Calculated Columns
      13. Hierarchies Spanning Multiple Tables
      14. Putting More on the Table
    2. Chapter 14. A Fancy Table—Tabular BI Semantic Model Advanced Features
      1. Enhancing Our Tabular Model
      2. Table and Column Properties
      3. Adding Images to a Tabular Model
      4. Parent/Child Relationships
      5. Multiple Relationships Between the Same Tables
      6. Partitions
      7. Perspectives
      8. DAX Functions
      9. The Context for Measures and Calculated Columns
      10. Filtering Aggregates
      11. Table-Valued Functions
      12. Aggregate Functions
      13. DAX Functions for Time Analytics
      14. Putting DAX Functions to Use
      15. Deploying a Tabular Model
      16. Deployment Configuration
      17. Completing the Deployment
      18. Can You Dig It?
  15. Part V Mining
    1. Chapter 15. Panning for Gold—Introduction to Data Mining
      1. What Is Data Mining?
      2. Order from Chaos
      3. Tasks Accomplished by Data Mining
      4. Steps for Data Mining
      5. Data Mining Algorithms
      6. Microsoft Decision Trees
      7. Microsoft Linear Regression
      8. Microsoft Naive Bayes
      9. Microsoft Clustering
      10. Microsoft Association Rules
      11. Microsoft Sequence Clustering
      12. Microsoft Time Series
      13. Microsoft Neural Network
      14. Microsoft Logistic Regression
      15. Grab a Pick Axe
    2. Chapter 16. Building the Mine—Working with the Data Mining Model
      1. Data Mining Structure
      2. Data Columns
      3. Data Mining Model
      4. Training Data Set
      5. Mining Model Viewer
      6. Microsoft Decision Trees
      7. Microsoft Naive Bayes
      8. Microsoft Clustering
      9. Microsoft Neural Network
      10. Microsoft Association Rules
      11. Microsoft Sequence Clustering
      12. Microsoft Time Series
      13. Reading the Tea Leaves
    3. Chapter 17. Spelunking—Exploration Using Data Mining
      1. Mining Accuracy Chart
      2. Column Mapping
      3. Lift Chart
      4. Profit Chart
      5. Classification Matrix
      6. Cross Validation
      7. Mining Model Prediction
      8. A Singleton Query
      9. A Prediction Join Query
      10. Data Mining Extensions
      11. Prediction Query Syntax
      12. Types of Prediction Queries
      13. Special Delivery
  16. Part VI Delivering
    1. Chapter 18. Special Delivery—Microsoft Business Intelligence Client Tools
      1. Front-End BI Tools from Microsoft
      2. Selecting a Front-End BI Tool
      3. A Bit of Knowledge Is a Big Help
      4. Reporting Services
      5. Report Structure
      6. Report Delivery
      7. Data Regions
      8. Creating a Reporting Services Report
      9. Excel
      10. Creating Pivot Tables and Pivot Charts
      11. Pivot Chart
      12. PerformancePoint Services in SharePoint
      13. Creating a Site Collection Using the Business Intelligence Center Template
      14. Creating a Dashboard Using PerformancePoint Services
      15. Power View
      16. Setting Up Power View in SharePoint
      17. Creating a Connection to a Tabular BI Semantic Model
      18. Creating a Power View Report
      19. Putting It All Together
    2. Chapter 19. Let’s Get Together—Integrating Business Intelligence with Your Applications
      1. ADOMD.NET
      2. ADOMD.NET Structure
      3. ADOMD.NET Example
      4. Integrating a Reporting Services Report with an Application
      5. Report Server
      6. Folders
      7. The Report Manager
      8. Deploying Reports Using SQL Server Data Tools
      9. Web Service Access
      10. Great Capabilities, Great Opportunities
  17. Index