ETL with Integration Services | Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

By : Abolfazl Radgoudarzi, Reza Rad

4.1 (14)

Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

4.1 (14)

By: Abolfazl Radgoudarzi, Reza Rad

Overview of this book

Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

Credits

Credits

About the Author

About the Author

About the Reviewers

About the Reviewers

www.PacktPub.com

www.PacktPub.com

Preface

Preface

Free Chapter

Data Warehouse Design

Data Warehouse Design

Understanding Business Intelligence

The architecture and components of a BI system

Building the data warehouse

Summary

SQL Server Analysis Services Multidimensional Cube Development

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

Time for action – creating the first cube

Time for action – viewing the cube in the browser

Dimensions and measures

Time for action – using the Dimension Designer

Time for action – change the order of the Month attribute

Time for action – modifying the measure properties

Data Source View

Time for action – creating a Named Calculation

Time for action – using a Named Query

Using dimensions

Time for action – adding a Fact relationship

Hierarchies

Time for action – creating a hierarchy

Multidimensional eXpression, calculated members, and Named Sets

Time for action – writing an MDX query

Time for action – calculated members

Deploying and processing

Time for action – deploying an SSAS project

Time for action – processing the data

Summary

Tabular Model Development of SQL Server Analysis Services

Tabular Model Development of SQL Server Analysis Services

Introducing SSAS Tabular

Developing a tabular project

Time for action – creating a tabular project

Time for action – creating measures

Creating hierarchies

Time for action – creating a hierarchy from a single table

Time for action – creating a hierarchy from multiple tables

Data Analysis eXpression, calculated columns, and measures

Time for action – using time intelligence functions in DAX

Securing the data

Time for action – security in tabular

Storage modes

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

Tabular versus Multidimensional SSAS

Summary

ETL with Integration Services

ETL with Integration Services

Understanding ETL and data consolidation

SQL Server Integration Services

Integration Service projects and packages

Time for action – creating your first SSIS project

The Control Flow tab

Time for action – working with Control Flow tasks

The Data Flow tab

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

Containers and dynamic packages

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

Deploying and executing

Time for action – deploying an SSIS project

Time for action – executing an SSIS package from a catalog

Summary

Master Data Management

Master Data Management

Understanding Master Data Management

Master Data Services

Time for action – configuring MDS

Comparing WebUI with the Excel Add-in

Time for action – installing Excel Add-in

Creating models and entities

Time for action – creating a model and an entity

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

Time for action – change tracking

The entity relationship

Time for action – creating a domain-based relationship

Business rules

Time for action – creating a simple business rule

Working with hierarchies

Time for action – creating a derived hierarchy

Security and permission

Time for action – permission walkthrough

Integration management

Time for action – a subscription view

Time for action – entity-based staging

Summary

Data Quality and Data Cleansing

Data Quality and Data Cleansing

Understanding data quality

Data Quality Services

Time for action – installing Data Quality Services

Knowledge Base Management

Time for action – creating a Knowledge Base

Knowledge discovery

Time for action – knowledge discovery

Data cleansing with Data Quality Projects

Time for action – using Data Quality Project for cleansing

Domain and composite domain rules

Time for action – composite domain rules

Synonyms and standardization

Time for action – creating synonyms and setting standardization

Matching

Time for action – matching policy

Time for action – matching projects

Integrating with MDS and SSIS

Time for action – the DQS cleansing component in SSIS

Summary

Data Mining – Descriptive Models in SSAS

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

Microsoft association rules

Time for action – the Microsoft association rule

Summary

Identifying Data Patterns – Predictive Models in SSAS

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

Predicting data with DMX

Time for action – predicting prospective bike buyers

Microsoft Time Series

Time for action – predicting future sales with Microsoft Time Series

Summary

Reporting Services

Reporting Services

The Reporting Services architecture

Developing a basic report

Time for action – creating our first report using SSRS

Extended report development

Time for action – adding parameters to a report

Printing and page configuration

Time for action – changing a page's properties

Sorting and grouping

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

Expressions

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

Time for action – working with charts in Reporting Services

Deploying and configuring

Time for action – deploying a report

Time for action – using Report Manager

Summary

Dashboard Design

Dashboard Design

The PerformancePoint service

Time for action – configuring PerformancePoint in SharePoint

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

Time for action – creating a dashboard page

Time for action – exploring on-the-fly features

Time for action – working with filters

Power View

Time for action – enabling Power View in Excel

Time for action – creating the first Power View dashboard

Time for action – geographical data visualization using Power View

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

Time for action – using Filter in Power View

Summary

Power BI

Power BI

Self-service ETL with Power Query

Time for action – self-service ETL with Power Query

Power Map

Time for action – data visualization with Power Map

Summary

Integrating Reports in Applications

Integrating Reports in Applications

Designing .NET applications with reports

Time for action – installing AdventureWorks SSRS sample reports

Developing reports in a web application

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

Developing reports in a Metro application

Time for action – working with reports in Metro applications

Working with ReportViewer in a local processing mode

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

Passing parameters to a report

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

Using the results of a mining model in an application

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

Summary

Index

Index

Chapter 4. ETL with Integration Services

As you learned in Chapter 1, Data Warehouse Design, a data warehouse is an integrated database with a specific design, which helps us in data analysis and reporting. Chapter 1, Data Warehouse Design was about designing the data warehouse. However, there is always another step, that is, loading the data into the data warehouse. This requirement brings another component of the data warehousing system into play, which is Extract, Transform, and Load (ETL). The following list explains the function of each process:

Extract: This is the process of fetching data from the source systems
Transform: This is the process in which we apply transformations such as aggregation, joining with other datasets, applying rules, splitting or merging results, lookup, join, pivot, and many others
Load: This process is about loading data into the data warehouse destination tables such as fact and dimensions

There are a bunch of ETL tools that provide useful features...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

Search

Your notes and bookmarks