Sign in  |  Register
Advanced search
Publication Cover

CITATION

Larson, Brian. Delivering Business Intelligence with Microsoft SQL Server 2012 3/E. US: McGraw-Hill Osborne Media, 2012.

Delivering Business Intelligence with Microsoft SQL Server 2012 3/E

Authors: Brian Larson

Published:  March 2012

eISBN: 9780071759397 0071759395 | ISBN: 9780071759380
Open eBook
  • Delivering Business Intelligence
  • About the Author
  • About the Technical Editors
  • Contents at a Glance
  • Contents
  • Acknowledgments
  • The Maximum Miniatures Databases and Other Supporting Materials
  • Part I: Business Intelligence
  • Chapter 1: Equipping the Organization for Effective Decision Making
  • Effective Decision Making
  • Who Is a Decision Maker?
  • What Is an Effective Decision?
  • Keys to Effective Decision Making
  • Are We Going Hither or Yon?
  • Is Your Map Upside-Down?
  • Panicked Gossip, the Crow’s Nest, or the Wireless
  • Business Intelligence
  • Business Intelligence and Microsoft SQL Server 2012
  • Chapter 2: Making the Most of What You’ve Got—Using Business Intelligence
  • What Business Intelligence Can Do for You
  • When We Know What We Are Looking For
  • Layout-led Discovery
  • Data-led Discovery
  • Discovering New Questions and Their Answers
  • Business Intelligence at Many Levels
  • The Top of the Pyramid
  • Highly Summarized Measures
  • Higher Latency
  • Mid-Level
  • Summarized Measures with Drilldown
  • Some Latency Acceptable
  • The Broad Base
  • Measures at the Detail Level
  • Low Latency
  • Maximum Miniatures, Inc.
  • Business Needs
  • Current Systems
  • Manufacturing Automation
  • Order Processing
  • Point of Sale
  • MaxMin.com
  • Accounting
  • Building the Foundation
  • Chapter 3: Seeking the Source—The Source of Business Intelligence
  • Seeking the Source
  • Transactional Data
  • Difficulties Using Transactional Data for Business Intelligence
  • The Data Mart
  • Features of a Data Mart
  • Not Real-Time Data
  • Consolidation and Cleansing
  • Data Mart Structure
  • Measures
  • Dimensions
  • The Star Schema
  • Attributes
  • Hierarchies
  • The Snowflake Schema
  • Snowflakes, Stars, and Analysis Services
  • Chapter 4: Two, Two, Two Models in One—The BI Semantic Model
  • Online Analytical Processing
  • The BI Semantic Model
  • Building OLAP Out of Cubes—The Multidimensional Model
  • Features of a Multidimensional Model Implementation
  • Multidimensional Database
  • Preprocessed Aggregates
  • Easily Understood
  • Architecture of a Multidimensional Model Implementation
  • Relational OLAP
  • Multidimensional OLAP
  • Hybrid OLAP
  • The Parts of a Multidimensional Model Implementation
  • Data Sources
  • Data Source Views
  • Proactive Caching
  • XML Definitions
  • Building OLAP Out of Tables—The Tabular Model
  • Features of a Tabular Model
  • Tabular Structure
  • Quick to Implement
  • Architecture of a Tabular Model
  • Cached Mode
  • Direct Query Mode
  • Choosing an Implementation Method
  • Tools of the Trade
  • Chapter 5: First Steps—Beginning the Development of Business Intelligence
  • SQL Server Data Tools
  • Visual Studio
  • Project Organization
  • Editing and Debugging Tools
  • Source Code Management Integration
  • Navigating SQL Server Data Tools
  • Creating a Solution and an Analysis Services Project
  • SQL Server Data Tools Windows
  • Window Management
  • SQL Server Data Tools Options
  • Fonts and Colors
  • Default File Locations
  • The SQL Server Management Studio
  • The SQL Server Management Studio User Interface
  • The SQL Server Management Studio Windows
  • The SQL Server Management Studio Projects
  • Don Your Hardhat
  • Part II: Defining Business Intelligence Structures
  • Chapter 6: Building Foundations—Creating Data Marts
  • Data Mart
  • Who Needs a Data Mart Anyway?
  • Legacy Databases
  • Data from Nondatabase Sources
  • No Physical Connection
  • Dirty Data
  • Designing a Data Mart
  • Decision Makers’ Needs
  • Available Data
  • Data Mart Structures
  • Measures
  • Dimensions and Hierarchies
  • Attributes
  • Stars and Snowflakes
  • Learn By Doing—Designing the Maximum Miniatures Manufacturing Data Mart
  • Creating a Data Mart Using the SQL Server Management Studio
  • Learn By Doing—Creating the Maximum Miniatures Manufacturing Data Mart Using the SQL Server Management Studio
  • Creating a Data Mart Using SQL Server Data Tools
  • Learn By Doing—Creating the Maximum Miniatures Sales Data Mart Using SQL Server Data Tools
  • The Sales Data Mart
  • Table Compression
  • Types of Table Compression
  • Row Compression
  • Page Compression
  • Learn By Doing—Enabling Table Compression
  • The Benefits of Integration
  • Chapter 7: Transformers—Integration Services Structure and Components
  • Integration Services
  • Package Structure
  • Drag-and-Drop Programming
  • Control Flow Tasks
  • Connection Managers
  • Precedence Arrows
  • Data Flow
  • Package Items
  • Control Flow
  • Control Flow SSIS Toolbox Organization
  • Containers
  • Control Flow Items
  • Maintenance Plan Tasks
  • Data Flow
  • Data Flow SSIS Toolbox Organization
  • Data Flow Sources
  • Source Assistant
  • Data Flow Transformations
  • Data Flow Destinations
  • Destination Assistant
  • Getting Under the Sink
  • Chapter 8: Fill ’er Up—Using Integration Services for Populating Data Marts
  • Package Development Features
  • Give It a Try
  • Learn By Doing—Using Integration Services to Populate the Maximum Miniatures Manufacturing Data Mart Dimensions
  • Programming in Integration Services Packages
  • Variables
  • Expressions
  • Event Handlers
  • Package Development Tools
  • Import/Export Wizard
  • Logging
  • Transactions
  • Checkpoints
  • Package Debugging
  • Change Data Capture
  • Change Data Capture Architecture
  • Enabling Change Data Capture
  • All Changes versus Net Changes
  • The Change Table
  • The Validity Interval
  • Loading a Data Mart Table from a Change Data Capture Change Table
  • The CDC Initial Load Package
  • The CDC Incremental Load Package
  • Loading a Fact Table
  • Learn By Doing—Adding a Second Fact Table to the Manufacturing Data Mart
  • Learn By Doing—Populating the Fact Tables in the Manufacturing Data Mart
  • Putting Integration Services Packages into Production
  • Parameters
  • Associating Properties with Parameters
  • The Integration Services Catalog
  • Deploying SSIS Projects from SQL Server Data Tools
  • Importing Packages into the SSIS Catalog
  • Learn By Doing—Deploying the Packages to a Server
  • Managing Packages in the Integration Services Catalog
  • Utilizing Environments
  • Setting Permissions
  • Executing Packages from the SSIS Catalog
  • Execution Dashboard Reports
  • Meanwhile, Back at the BI Semantic Model
  • Part III: Working with a Multidimensional BI Semantic Model
  • Chapter 9: Cubism—Measures and Dimensions
  • Building a Multidimensional BI Semantic Model
  • Creating a Cube
  • Learn By Doing—Building a Multidimensional BI Semantic Model for the Manufacturing Data Mart
  • Measures
  • Measure Groups
  • Measure Groups and Dimensions
  • Granularity
  • Made-up Facts—Calculated Measures
  • Creating Calculated Measures
  • It Doesn’t Add Up—Measure Aggregates Other Than Sum
  • Semiadditive Measures
  • Aggregate Functions
  • Learn By Doing—Working with Measures and Measure Groups
  • Dimensions
  • Managing Dimensions
  • Learn By Doing—Cleaning Up the Dimensions in the Max Min Manufacturing DM Cube
  • Relating Dimensions to Measure Groups
  • Learn By Doing—Relating Dimensions in the Max Min Manufacturing DM Cube
  • Types of Dimensions
  • Fact Dimensions
  • Parent-Child Dimensions
  • Role Playing Dimensions
  • Reference Dimensions
  • Data Mining Dimensions
  • Many-to-Many Dimensions
  • Slowly Changing Dimensions
  • Type 1 Slowly Changing Dimensions
  • Type 2 Slowly Changing Dimensions
  • Type 3 Slowly Changing Dimensions
  • Loading the Max Min Sales DM Data Mart
  • You Are Special
  • Chapter 10: Bells and Whistles—Special Features of OLAP Cubes
  • Where No Cube Has Gone Before
  • Deploying and Processing
  • Deploying
  • Processing
  • Deploying from SQL Server Data Tools
  • Learn By Doing—Deploying the MaxMinManufacturingDM Project Using SQL Server Data Tools
  • Deploying from the Analysis Services Deployment Wizard
  • Learn By Doing—Deploying the MaxMinSalesDM Project Using the Analysis Services Deployment Wizard
  • Additional Cube Features
  • Linked Objects
  • Creating a Linked Measure
  • The Business Intelligence Wizard
  • Key Performance Indicators
  • Creating a Key Performance Indicator
  • Key Performance Indicators and Client Software
  • Learn By Doing—Adding a KPI to the Max Min Manufacturing DM Cube
  • Actions
  • Types of Actions
  • Creating an Action
  • Creating Drillthrough Actions
  • Creating Reporting Actions
  • Actions and Client Software
  • Partitions
  • Measure Groups and Partitions
  • Multiple Partitions for a Measure Group
  • Learn By Doing—Creating Multiple Partitions from a Single Table
  • Partitions and Storage Options
  • Learn By Doing—Setting Storage Options for a Cube Partition
  • Writeback
  • Aggregation Design
  • Manual Aggregation Design
  • Perspectives
  • Creating Perspectives
  • Translations
  • Creating Translations
  • More Sophisticated Scripting
  • Chapter 11: Writing a New Script—MDX Scripting
  • Terms and Concepts
  • Where Are We?
  • Cells
  • Tuples
  • Levels
  • Shortcuts
  • Expression Errors
  • Default Members
  • Sets
  • Range Operator
  • Name Attributes
  • Learn By Doing—Default Members, Level Names, and the NameColumn Property
  • Getting There from Here
  • The Starting Point—The CurrentMember Function
  • Relative Position Within a Hierarchy Level—The Lag, Lead, NextMember, and PrevMember Functions
  • Immediate Relative Position Between Hierarchy Levels—The Children, FirstChild, FirstSibling, LastChild, LastSibling, Parent, and Siblings Functions
  • Distant Relative Position Between Hierarchy Levels—The Ancestor, Cousin, and Descendants Functions
  • Putting MDX Scripting to Work
  • Cube Security
  • Learn By Doing—Setting Security Within an OLAP Cube
  • This Year-to-Last Year Comparisons and Year-to-Date Rollups
  • The ParallelPeriod Function
  • The YTD Function
  • The SUM Function
  • Learn By Doing—Time-Based Analytics
  • Extracting Data from Cubes
  • Chapter 12: Pulling It Out and Building It Up—MDX Queries
  • The MDX SELECT Statement
  • The Basic MDX SELECT Statement
  • Opening a SQL Server Management Studio MDX Query Window
  • The Very Basics
  • Query Dimensions
  • Slicer
  • Filtering with the FILTER Function
  • The NON EMPTY Statement
  • Additional Tools for Querying
  • The WITH Statement
  • CROSSJOIN
  • TOPCOUNT/BOTTOMCOUNT
  • Aggregates
  • Additional Dimensions
  • Pages, Sections, and Chapters
  • AXIS
  • Additional MDX Syntax
  • Operators
  • Comment
  • Logical
  • Functions
  • Dimensional
  • Logical
  • Member
  • The AGGREGATE Function
  • Data Analysis
  • Tabling the Matter
  • Part IV: Working with a Tabular BI Semantic Model
  • Chapter 13: Setting the Table—Creating a Tabular BI Semantic Model
  • Preparation for Creating Tabular Models
  • SQL Server Analysis Services Tabular Instance
  • Managing the Workspace Database
  • Learn By Doing—Preparing SQL Server Data Tools for Tabular Model Development
  • Creating a Tabular Model
  • Data Sources for Our Tabular Models
  • Learn By Doing—Creating the Maximum Miniatures Sales Tabular BI Semantic Model
  • Measures in a Tabular Model
  • Learn By Doing—Adding Measures to the Maximum Miniatures Sales Tabular BI Semantic Model
  • Manually Creating a Time Table
  • Learn By Doing—Adding a Time Table to the Maximum Miniatures Sales Tabular BI Semantic Model
  • Relationships and Hierarchies
  • Learn By Doing—Adding Relationships and a Time Hierarchy to the Maximum Miniatures Sales Tabular BI Semantic Model
  • Validating the Model Using the Analyze in Excel Feature
  • Learn By Doing—Viewing the Model
  • A Second Tabular Model
  • Data Persistence
  • Learn By Doing—Creating the Maximum Miniatures Manufacturing Tabular BI Semantic Model
  • Calculated Columns
  • Learn By Doing—Using Calculated Columns
  • Hierarchies Spanning Multiple Tables
  • The LOOKUPVALUE Function
  • The RELATED Function
  • Learn By Doing—Using the LOOKUPVALUE() and RELATED() Functions
  • Putting More on the Table
  • Chapter 14: A Fancy Table—Tabular BI Semantic Model Advanced Features
  • Enhancing Our Tabular Model
  • Table and Column Properties
  • Adding Images to a Tabular Model
  • Images Stored in a Table
  • Images Referenced as URLs
  • Learn By Doing—Enhancing the Maximum Miniatures Manufacturing Tabular BI Semantic Model
  • Parent/Child Relationships
  • Path(PrimaryKeyField, ParentForeignKeyField)
  • PathItem(PathField, PathLevel)
  • LookUpValue(ReturnValueField, PrimaryKeyField, PrimaryKeyValue)
  • Creating a Parent/Child Hierarchy
  • Multiple Relationships Between the Same Tables
  • Partitions
  • Defining Partitions
  • Processing Partitions
  • Perspectives
  • Creating Perspectives
  • DAX Functions
  • The Context for Measures and Calculated Columns
  • The Default Context for Calculated Columns
  • The Default Context for Measures
  • Aggregate Functions in Calculated Columns
  • Filtering Aggregates
  • CALCULATE(Expression, Filter1, Filter2, …)
  • ALLEXCEPT(TableName, TableFieldReference1, TableFieldReference2, …)
  • ALL(TableNameOrTableFieldReference1, TableFieldReference2, …)
  • Table-Valued Functions
  • RELATEDTABLE(TableExpression)
  • CALCULATETABLE(TableExpression, Filter1, Filter2)
  • Aggregate Functions
  • DAX Functions for Time Analytics
  • Putting DAX Functions to Use
  • Creating a Key Performance Indicator
  • Role-Based Security
  • Deploying a Tabular Model
  • Deployment Configuration
  • Completing the Deployment
  • Learn By Doing—Enhancing the Maximum Miniatures Manufacturing Tabular BI Semantic Model
  • Can You Dig It?
  • Part V: Mining
  • Chapter 15: Panning for Gold—Introduction to Data Mining
  • What Is Data Mining?
  • Order from Chaos
  • Making Connections
  • Making Predictions
  • Tasks Accomplished by Data Mining
  • Classification
  • Regression
  • Segmentation
  • Association
  • Sequence Analysis
  • Probability Predictions
  • Steps for Data Mining
  • Problem Definition
  • Data Preparation
  • Training
  • Testing
  • Deployment
  • Data Mining Algorithms
  • Microsoft Decision Trees
  • Function
  • Tasks
  • Microsoft Linear Regression
  • Function
  • Tasks
  • Microsoft Naïve Bayes
  • Function
  • Tasks
  • Microsoft Clustering
  • Function
  • Tasks
  • Microsoft Association Rules
  • Function
  • Tasks
  • Microsoft Sequence Clustering
  • Function
  • Tasks
  • Microsoft Time Series
  • Function
  • Tasks
  • Microsoft Neural Network
  • Function
  • Tasks
  • Microsoft Logistic Regression
  • Function
  • Tasks
  • Grab a Pick Axe
  • Chapter 16: Building the Mine—Working with the Data Mining Model
  • Data Mining Structure
  • Data Columns
  • Data Mining Model
  • Data Column Usage
  • Training Data Set
  • Cube Slice
  • Learn By Doing—Defining a Data Mining Model
  • Mining Model Viewer
  • Microsoft Decision Trees
  • Microsoft Tree Viewer—Decision Tree Tab
  • Microsoft Tree Viewer—Dependency Network Tab
  • Microsoft Generic Content Tree Viewer
  • Microsoft Naïve Bayes
  • Microsoft Naïve Bayes Viewer—Dependency Network
  • Attribute Profiles
  • Attribute Characteristics
  • Attribute Discrimination
  • Microsoft Clustering
  • Cluster Diagram
  • Cluster Profiles
  • Cluster Characteristics
  • Cluster Discrimination
  • Microsoft Neural Network
  • Microsoft Association Rules
  • Itemsets
  • Rules
  • Dependency Network
  • Microsoft Sequence Clustering
  • Microsoft Time Series
  • Reading the Tea Leaves
  • Chapter 17: Spelunking—Exploration Using Data Mining
  • Mining Accuracy Chart
  • Column Mapping
  • Select the Mining Models and Prediction
  • Select the Test Data
  • Viewing the Mining Accuracy Charts
  • Lift Chart
  • The Lift Chart with No Prediction Value Specified
  • The Lift Chart with a Prediction Value Specified
  • Profit Chart
  • Creating a Profit Chart
  • Classification Matrix
  • Viewing the Classification Matrix
  • Cross Validation
  • Viewing the Cross Validation
  • Mining Model Prediction
  • A Singleton Query
  • Creating a Singleton Query
  • Learn By Doing—Creating a Singleton Query
  • A Prediction Join Query
  • Creating a Prediction Join Query
  • Learn By Doing—Creating a Prediction Join Query
  • Data Mining Extensions
  • Prediction Query Syntax
  • SELECT Clause
  • FROM Clause
  • PREDICTION JOIN Clause
  • WHERE Clause
  • ORDER BY Clause
  • Types of Prediction Queries
  • PREDICTION JOIN
  • NATURAL PREDICTION JOIN
  • Empty Prediction Join
  • Singleton Query
  • Learn By Doing—Creating a Query in the SQL Server Management Studio
  • Special Delivery
  • Part VI: Delivering
  • Chapter 18: Special Delivery–Microsoft Business Intelligence Client Tools
  • Front-End BI Tools from Microsoft
  • Selecting a Front-End BI Tool
  • What Can Be Supported in Your Organization?
  • Who Will Be the Primary Developers?
  • Does the Tool Provide the Necessary Capabilities in an Efficient Manner to Fulfill Current and Likely Future Needs?
  • A Bit of Knowledge Is a Big Help
  • Reporting Services
  • Report Structure
  • Data Definition
  • Report Layout
  • Report Definition Language
  • Report Delivery
  • Report Manager Website
  • Subscription Delivery
  • Web Service Interface
  • Data Regions
  • The Table Template for the Tablix
  • The Matrix Template for the Tablix
  • The List Template for the Tablix
  • The Chart Data Region
  • The Map Data Region
  • Using Data Regions
  • Creating a Reporting Services Report
  • Learn By Doing—Creating a Matrix Report
  • Excel
  • Creating Pivot Tables and Pivot Charts
  • Connecting to the Data
  • Layout
  • Learn By Doing—Creating an Excel Pivot Table
  • Pivot Chart
  • Learn By Doing—Creating a Pivot Chart
  • PerformancePoint Services in SharePoint 2010
  • Creating a Site Collection Using the Business Intelligence Center Template
  • Learn By Doing—Preparing SharePoint to Allow the Use of PerformancePoint Services
  • Creating a Dashboard Using PerformancePoint Services
  • Learn By Doing—Creating KPIs and a Dashboard Using PerformancePoint Services
  • Power View
  • Setting Up Power View in SharePoint 2010
  • Learn By Doing—Configuring SQL Server Reporting Services for SharePoint Integrated Mode Operation
  • Creating a Connection to a Tabular BI Semantic Model
  • Learn By Doing—Creating a Report Data Source
  • Creating a Power View Report
  • Learn By Doing—Creating a Power View Report
  • Putting It All Together
  • Chapter 19: Let’s Get Together—Integrating Business Intelligence with Your Applications
  • ADOMD.NET
  • ADOMD.NET Structure
  • AdomdConnection
  • AdomdCommand
  • AdomdDataReader
  • CellSet
  • ADOMD.NET Example
  • Setting a Reference
  • Retrieving a CellSet
  • Integrating a Reporting Services Report with an Application
  • Report Server
  • Folders
  • The Report Manager
  • Deploying Reports Using SQL Server Data Tools
  • Learn By Doing—Deploying a Report
  • Deploying a Single Report
  • Web Service Access
  • Learn By Doing—Displaying a Report in an Application
  • Managing Reporting Services Through Web Services
  • Great Capabilities, Great Opportunities
  • Index