Building a Data Warehouse with SQL Server - PowerPoint PPT Presentation

Building a data warehouse with sql server l.jpg
1 / 28

Building a Data Warehouse with SQL Server. Presented by John Sterrett. About Me…. AITP - Pittsburgh. What is a data warehouse?. Building a Data Warehouse with SQL Server. What is Business Intelligence?.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation

Building a Data Warehouse with SQL Server

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Building a data warehouse with sql server l.jpg

Building a Data Warehouse with SQL Server

Presented by John Sterrett

Slide2 l.jpg

  • About Me…

Aitp pittsburgh l.jpg

AITP - Pittsburgh

What is a data warehouse l.jpg

What is a data warehouse?

Building a Data Warehouse with SQL Server

What is business intelligence l.jpg

What is Business Intelligence?

According to Wikipedia BI refers to skills, technologies, applications and practices used to help a business acquire a better understanding of its commercial context.

The following is a definition from Business Intelligence with Microsoft Office PerformancePoint Server 2007

Business intelligence (BI) is more of a concept than a single technology. The goal is to gain insight into the business by bringing together data, formatting it in a way that enables better analysis, and then providing tools that give users power—not just to examine and explore the data, but to quickly understand it.

Slide6 l.jpg

  • Data Warehouse = Business Intelligence

Business problems that bi addresses l.jpg

Business Problems that BIAddresses

Slow-to-execute queries

General system slowdowns

Manual query writing

Disparate data sources

Data is not available to all users

Too much data

What is bi to the users l.jpg

What is BI to the users?

BI is fast to query.

BI improves your return on investment (ROI)

BI is a tool that allows users to find what they need.

Business cases l.jpg

Business Cases

A technology department could analyze work completed for departments and/or clients. This could then be used to estimate future flat fee engagements, establish seasonal hiring, balance overtime.

A medical center could use statistics covering a wide range of lab values and a large number of patients to identify whether a patient currently being treated might be at an elevated risk for a certain types of disease.

Application that’s great for data entry but lacks in depth reporting and drilldown capabilities.

A company who wants to centralize data to a single data source for allowing decision makers the ability to make decisions as needed.

What products are involved l.jpg

What Products Are Involved?

Database Technologies

SQL Server 2005/2008 (Database Engine)

SQL Server Analysis Services (SSAS)

SQL Server Integration Services (SSIS)

User Interface Technologies

SQL Server Reporting Services (SSRS)

SQL Server Management Studio (SSMS)

Performance Point 2007



Slide11 l.jpg


Browse a Cube using Management Studio

Goals for dimensional modeling l.jpg

Goals for dimensional modeling

Understand star schema

Understand dimensional modeling

Understand changing dimensions

Understanding fact (or measure) and cube modeling

Star schema l.jpg

Star Schema…

A star schemaconsists of at least one fact table and a number of dimension tables.

Star Schema is highly recommended schema for SSAS cubes.

Star schema example l.jpg

Star Schema Example

Facts and measures l.jpg

Facts and Measures

Fact table consists of at least two types of data: keys and measures.

Keys are usually surrogate keys that link to the dimension tables.

Measuresare numeric values that are usually additive that express business metrics.

Dimensions l.jpg


Dimensions describe who, what, when, where and why for the facts.

Dimensions should consist of the following data types

Surrogate key.

Primary key of the loaded source(s)

Any additional attributes (columns) that describe the business entity.

Dimension examples l.jpg

Dimension Examples

Slowly changing dimensions scd l.jpg

Slowly Changing Dimensions (SCD)

Hierarchies l.jpg


Hierarchies serve two purposes:

Convenience for end users.

Provides drill down / drill up features

Where do i go from here l.jpg

Where do I go from here?

Create Use Grain Statements

What are the key metrics for your business?

What factors do you use to evaluate those key metrics?

What level of granularity do you use evaluate each factor?

Grain statement examples l.jpg

Grain Statement Examples:

We want to see time worked, hours billed, and cost of work by date, by employee, by department, by location, and by projects.

We want to see sales amount and sales quantity by day, by product, by employee, and by store location.

We want to see average score and quantity of courses taken, by course, by day, by student, by manager, by curriculum, and by curriculum type.

Slide22 l.jpg


Build a Cube

Extracting loading data l.jpg

Extracting & Loading Data

*The process of extracting, transferring and loading data consumes about 75% of the Data Warehouse project.

It is highly recommended to use SSIS for ETL instead of native T-SQL

Basic dimension package l.jpg

Basic Dimension Package

Basic fact package l.jpg

Basic Fact Package

So you are interested in building a data warehouse l.jpg

So… You are interested in building a Data Warehouse

Click here to download a Virtual PC that includes sample Data Warehouses and all of Microsoft’s BI tools.

If you already have SQL Server 2005 and Analysis Services configured click here to download samples (Click here for SQL Server 2008)

Check out this Introduction to Data Warehousing with SQL Server

Slide27 l.jpg






References l.jpg


Foundation of SQL Server 2005 Business Intelligence.

Business Intelligence with Microsoft Office PerformancePoint Server 2007

ACM – Intro to Data Warehousing

  • Login