Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide电子书

Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

Table of Contents

Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Instant updates on new Packt books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Time for action – heading

What just happened?

Reader feedback

Customer support

Downloading the example code

Downloading color versions of the images for this book

Errata

Piracy

Questions

1. Data Warehouse Design

Understanding Business Intelligence

The architecture and components of a BI system

The data warehouse

Extract Transform Load

Data model – BISM

Data visualization

Master Data Management

Data Quality Services

Building the data warehouse

Dimensional modeling

Fact or measure

Dimension

The Fact table

Grain

The star schema

An example of Internet sales

FactSales

The customer dimension

DimDate

DimProduct

DimStore

Types of facts

The Factless Fact table – The Bridge table

Types of dimensions

Slowly Changing Dimension

SCD type 0

SCD type 1

SCD type 2

Summary

2. SQL Server Analysis Services Multidimensional Cube Development

Why is OLAP required?

Understanding the SQL Server Analysis Services engine

Developing your first cube

Time for action – creating an Analysis Services project

What just happened?

Time for action – creating the first cube

What just happened?

Time for action – viewing the cube in the browser

What just happened?

Dimensions and measures

Time for action – using the Dimension Designer

What just happened?

Time for action – change the order of the Month attribute

What just happened?

Time for action – modifying the measure properties

What just happened?

Data Source View

Time for action – creating a Named Calculation

What just happened?

Time for action – using a Named Query

What just happened?

Using dimensions

Time for action – adding a Fact relationship

What just happened?

Hierarchies

Time for action – creating a hierarchy

What just happened?

Multidimensional eXpression, calculated members, and Named Sets

Time for action – writing an MDX query

What just happened?

Time for action – calculated members

What just happened?

Deploying and processing

Time for action – deploying an SSAS project

What just happened?

Time for action – processing the data

What just happened?

Summary

3. Tabular Model Development of SQL Server Analysis Services

Introducing SSAS Tabular

Developing a tabular project

Time for action – creating a tabular project

What just happened?

Time for action – creating measures

What just happened?

Creating hierarchies

Time for action – creating a hierarchy from a single table

What just happened?

Time for action – creating a hierarchy from multiple tables

What just happened?

Data Analysis eXpression, calculated columns, and measures

Time for action – using time intelligence functions in DAX

What just happened?

Securing the data

Time for action – security in tabular

What just happened?

Storage modes

Time for action – creating a model with the DirectQuery storage mode

What just happened?

Tabular versus Multidimensional SSAS

Summary

4. ETL with Integration Services

Understanding ETL and data consolidation

Staging

SQL Server Integration Services

Integration Service projects and packages

Time for action – creating your first SSIS project

What just happened?

The Control Flow tab

Time for action – working with Control Flow tasks

What just happened?

The Data Flow tab

Time for action – loading customer information from a flat file into a database table with a Data Flow Task

What just happened?

Containers and dynamic packages

Time for action – looping through CSV files in a directory and loading them into a database table

What just happened?

Deploying and executing

Time for action – deploying an SSIS project

What just happened?

Time for action – executing an SSIS package from a catalog

What just happened?

Summary

5. Master Data Management

Understanding Master Data Management

Master Data Services

Time for action – configuring MDS

What just happened?

Comparing WebUI with the Excel Add-in

Time for action – installing Excel Add-in

What just happened?

Creating models and entities

Time for action – creating a model and an entity

What just happened?

Time for action – creating an entity with data from the Excel Add-in

What just happened?

Time for action – change tracking

What just happened?

The entity relationship

Time for action – creating a domain-based relationship

What just happened?

Business rules

Time for action – creating a simple business rule

What just happened?

Working with hierarchies

Time for action – creating a derived hierarchy

What just happened?

Security and permission

Time for action – permission walkthrough

What just happened?

Integration management

Time for action – a subscription view

What just happened?

Time for action – entity-based staging

What just happened?

Summary

6. Data Quality and Data Cleansing

Understanding data quality

Data Quality Services

Time for action – installing Data Quality Services

What just happened?

Knowledge Base Management

Time for action – creating a Knowledge Base

What just happened?

Knowledge discovery

Time for action – knowledge discovery

What just happened?

Data cleansing with Data Quality Projects

Time for action – using Data Quality Project for cleansing

What just happened?

Domain and composite domain rules

Time for action – composite domain rules

What just happened?

Synonyms and standardization

Time for action – creating synonyms and setting standardization

What just happened?

Matching

Time for action – matching policy

What just happened?

Time for action – matching projects

What just happened?

Integrating with MDS and SSIS

Time for action – the DQS cleansing component in SSIS

What just happened?

DQS integration with MDS

Summary

7. Data Mining – Descriptive Models in SSAS

An introduction to data mining

The Microsoft Decision Tree algorithm

Time for action – creating a data mining solution with the Microsoft Decision Tree algorithm

What just happened?

Microsoft association rules

Time for action – the Microsoft association rule

What just happened?

Algorithm parameters

Summary

8. Identifying Data Patterns – Predictive Models in SSAS

Finding the best algorithm

Time for action – finding the best mining model with Lift Chart and Profit Chart

What just happened?

Predicting data with DMX

Time for action – predicting prospective bike buyers

What just happened?

Microsoft Time Series

Time for action – predicting future sales with Microsoft Time Series

What just happened?

Summary

9. Reporting Services

The Reporting Services architecture

Developing a basic report

Time for action – creating our first report using SSRS

What just happened?

Extended report development

Parameters

Time for action – adding parameters to a report

What just happened?

Printing and page configuration

Time for action – changing a page's properties

What just happened?

Sorting and grouping

Time for action – applying ordering and grouping on the data rows

What just happened?

Expressions

Time for action – changing the background color of data rows based on expressions

What just happened?

Adding charts

Time for action – working with charts in Reporting Services

What just happened?

Deploying and configuring

Time for action – deploying a report

What just happened?

Time for action – using Report Manager

What just happened?

Summary

10. Dashboard Design

The PerformancePoint service

Time for action – configuring PerformancePoint in SharePoint

What just happened?

The Dashboard Designer utility

Time for action – creating your first dashboard with PerformancePoint Dashboard Designer

What just happened?

The dashboard pages

Time for action – creating a dashboard page

What just happened?

PPS dashboard's on-the-fly features

Time for action – exploring on-the-fly features

What just happened?

Filters

Time for action – working with filters

What just happened?

PerformancePoint Wrap Up

Power View

Time for action – enabling Power View in Excel

What Just Happened?

Time for action – creating the first Power View dashboard

What just happened?

Map

Time for action – geographical data visualization using Power View

What just happened?

Scatter chart

Time for action – visualizing time-based information with a scatter chart

What just happened?

Filtering data

Time for action – using Filter in Power View

Wrapping up Power View

Summary

11. Power BI

Self-service ETL with Power Query

Time for action – self-service ETL with Power Query

What just happened?

Power Map

Time for action – data visualization with Power Map

What just happened?

Summary

12. Integrating Reports in Applications

Designing .NET applications with reports

Time for action – installing AdventureWorks SSRS sample reports

What just happened?

Developing reports in a web application

Time for action – working with reports in web/Windows applications

What just happened?

Processing modes

ReportViewer in Web and Windows

The ReportViewer toolbar

Developing reports in a Metro application

Time for action – working with reports in Metro applications

What just happened?

Working with ReportViewer in a local processing mode

Time for action – designing reports and working with the local processing mode

What just happened?

Passing parameters to a report

Time for action – changing a report configuration with a ReportViewer Object through code behind

What just happened?

Using the results of a mining model in an application

Time for action – running DMX queries from a .NET application

What just happened?

Summary

Index