CS408: Data Warehousing: Welcome To Course

Welcome to course
CS408: Data Warehousing

Outlines
• About Me
• Course information
• Teaching team
• Learning outcomes
• Course contents and schedule
• Assessments
• Resources
• Lecture 01
2
Course Information
Course Title: Data Warehousing
Course Code: CS408
Database Systems, Data

Prerequisites:
Structures, Operating Systems
Level: 4
8
Why should you be here?
• Bad decisions can lead to disaster
– Data Warehousing is at
the base of decision
support systems
Acknowledgment - Thanks to Wolf-Tilo Balke and Silviu Homoceanu - TU Braunschweig for the slides 10
• Data Warehousing & OLAP are important
• They help to
– Understand the information hidden
within an organization’s data
• See data from different angles:
product,client,time,geographical area
• Get adequate statistics to get
your point of argumentation
across
• Get a glimpse of the future…
12
Course Contents and Schedule
Week LECTURE LAB
Introduction to course, Introduction to Data
1 • Introduction with lab course, Introduction with Tool
Warehouses
• Database and SQL Revision
2 DW Life Cycle and Basic Architecture
• Lab task based on SQL queries
• PSQL introduction and lab tasks based on PSQL
3 Storage Architecture
• Lab task based on PSQL
• Will follow a Data set in in our labs
4 DW Modeling – Conceptual Model • In this step we will design data warehouse architecture
• Create staging Database in SQL Server 2012.
5 Logical Model • Create Production Data warehouse in SQL Server 2012.
• Data will be extracted from source data and load in staging tables
6 Physical Model
using SSIS
• Different transformations will be applied on the data and data will
7 Project Brief stored in production tables using SSIS and implementation of slowly
changing dimension.
Indexes • OLAP queries writing
8
• Query based tasks will be assigned to perform with in the lab.
Indexes (continued) • OLAP Query Optimizing
9
• Query based tasks will be assigned to perform with in the lab.
10 Optimisation • Multidimensional cube will be design using SSAS
11 Optimisation (continued) • Formulas, aggregation applied on the built cube
12 OLAP Queries • Data visualization and reporting based on Datawarehouse
13 OLAP Queries (continued) • Data visualization and reporting based on Cube
14 Building the DW • Reports and Dashboard management on Powerbi.com

Real-time Data Warehousing • Introduction to Hadoop and other Big Data tools and techniques
15
Project submission • DWH lab revision
16 Big Data – Challenges and Opportunities • Semester Project discussion 13
Assessments
Assessment type Marks weight
Lab Tasks 20%
Mid 15%
Final
35%
Project 30%
14
Recommended Literature
• Building the Data Warehouse

– William H. Inmon
– Wiley, ISBN 978-0-7645-9944-6
• The Data Warehouse Toolkit
– Ralph Kimball & Margy Ross
– Wiley, ISBN 978-1-118-53080-1
Lecture 01
Introduction to Data Warehouse
16
This week
1.What is a data warehouse?

2. Applications and users
What is a data warehouse?
• Basically a very large da ta ba se…
– Not all very large databases are data warehouses,
but all data warehouses are pretty large databases
– Nowadays a warehouse is considered
to start at around 800 GB and goes
up to severalTB
– It spans over several servers and
needs an impressive amount of
computing power
• More specific, a collective data repository
– Containing snapshots of the operational data
(history)
– Obtained through data cleansing (Extract-
Transform- Load)
– Useful for analytics
• Compared to other solutions it…
– Is suitable for tactical/strateg ic focus
– Implies a sm all num ber of transactions
– Implies large transactions spanning over a
long period of time
Some Definitions
• Experts say…
– Ralph Kimball:“a copy of transaction
data specifically structured for query and
analysis”
– Bill Inmon: “A data warehouse is a:
– Subject oriented
– Integrated
– Non-volatile
– Time variant
collection of data in support of management’s decisions.”
Inmon Definition (cont’d.)
• S ubject oriented
– The data in the data warehouse is organized so that
all the data elements relating to the same real-world
event or object are linked together
• Typical subject areas in DWs are
Customer,Product,Order,Claim,
Account,…
• S ubject oriented
– Example:customer as subject in a DW
• DW is organized in this case by the customer
• It may consist of 10,100 or more physical tables, all
related
CUSTOMER
Base customer
Data 2003 - 2005
Customer activity
Base customer
2001 - 2004
Data 2000 - 2002
Customer activity Customer activity

Detail 2002 - 2004 Detail 2005 - 2006
• Integ ra ted
– The data warehouse contains data from most or all
of an organization's operational systems and this data is
made consistent
– E.g. gender,measurement,conflicting keys,
consistency,…
operational encoding DW
appl A – M,F M,F
appl B – 1,0
appl C – male,female
appl A – cm cm
appl B – inches
• Non-vola tile
– Data in the data warehouse is never over-written
or deleted - once committed, the data is static, read-
only, and retained for future reporting
– Data is loaded,but not updated
– When subsequent changes occur,a new snapshot record is
written
Operational DW
insert change
delete access
access load
Record-by-record Mass load/
manipulation access of data access
• T im e-va rying
– The changes to the data in the data warehouse are
tracked and recorded so that reports can be produced
showing changes over tim e
– Different environments have different tim e horizons
• associated
• While for operational systems a 60-
to-90 day time horizon is normal,
data warehouse has a
• 5-to-10 year horizon
General Definition
• More general,a DW is a
– Repository of an
org anization’s
electronically stored
data
– Desig ned to
facilitate
reporting and
analysis
Typical Features
• DW typically…
– Reside on computers dedicated to this function
– Run on DB MS such as Oracle, IBM DB2, Teradata or
Microsoft SQL Server
– Retain data for long periods of tim e
– Consolidate data obtained from a variety of
sources
– Are built around their own carefully desig ned data
m odel
Use case
• DW stands for big data volume,so lets take an
example of 2 big com pa nies,a retailer,say
Walmart and a RDBMS vendor,Sybase:
– Walmart CEO:I want to keep track of sales in
all my stores simultaneously
– Sybase consultant:You need our wonderful RDBMS software.
You can stuff data in as sales are rung up at cash registers and
simultaneously query data right in your office
– SoWalmart buys a $1 milion Sun E10000 multi-CPU
server,a $500 000 Sybase license,a book
“Database Design for Smarties”,and build
themselves a normalized SQL data model
Use case (cont’d.)
• After a few months of stuffing data into the tables…
aWalmart executive asks…
– I have noticed that there was a Colgate promotion recently,
directed to people who live in small towns.How much
toothpaste did we sell in those towns yesterday?
– Translation to a query:
select sum(sales.quantity_sold) from sales,products,product_categories, manufacturers,
stores,cities where manufacturer_name =‘Colgate’
and product_category_name =‘toothpaste’ and cities.population < 40 000
and trunc(sales.date_time_of_sale) = trunc(sysdate-1) and sales.product_id =
products.product_id
and sales.store_id = stores.store_id
and products.product_category_id = product_categories.product_category_id and
products.manufacturer_id = manufacturers.manufacturer_id
and stores.city_id = cities.city_id
– The tables contain large volumes of data and the

query implies a 6 way join so it will take some
time to execute
– The tables are at the sam e tim e also updated
by new sales
– Soon after executive start their quest for marketing
information store employees notice that there are times
during the day when it is impossible to process a sale
Any attempt to update the database results in freezing the
computer up for 20 minutes
• In minutes…theWalmart CEO calls Sybase

tech support
• Walmart CEO: WE TYPE IN THE TOOTHPASTE
QUERY AND OUR SYSTEM HANGS!!!
• Sybase support: Of course it does! You built an on-
line transaction processing (OLTP) system. You
can’t feed it a decision support system (DSS)
query and expect things to work!
– Walmart CEO:!@%$#.I thought this was the whole point of SQL
and your RDBMS…to query and insert simultaneously!!
– Sybase support:Uh,not exactly.If you’re reading from the database,
nobody can write to the database.If you’re writing to the database,
nobody can read from the database.So if you’ve got a query that takes
20 minutes to run and don’t specify special locking instructions,
nobody can update those tables for 20 minutes.
– Walmart CEO: It sounds like a bug.
– Sybase support: Actually it is a feature. We call it
pessimistic locking.
– Walmart CEO: Can you fix your system so that it doesn’t
lock up???
– Sybase support: No. But we made this great loader tool
so that you can copy everything from your OLTP system
into a separate Data Warehouse system at 100 GB/hour
• After a while…
OLTP vs. DW
• OLTP (OnL ine T ransaction P rocessing):

– Also known under the name of operational data,
it represents day-to-day operational business activities:
• Purchasing,sales,production distribution, …
– Typically for data entr y and retrieval
transaction processing
– Reflects only the current state
of the data
OLTP vs. DW (cont’d.)
• OLAP (OnL ine A nalytical P rocessing):

– Represents front-end analytics based on a
DW repository
– It provides information for activities like
• Resource planning,capital budgeting,marketing initiatives,…
– It is decision oriented
• Properties
Operational DB DW
Mostly updates Mostly reads
Many small transactions Queries long, complex
MB-TB of data GB-PB of data
Raw data Summarized data
Clerical users Decision makers
Up-to-date data May be slightly outdated
• Consider a norm a lized da ta ba se for a store

– The tables would look like this…
Shipment
Invoice Invoice
Invoice_line_item
Customer
Customer_ID (PK) Invoice_number (PK) Invoice_number (FK)

Name Date Item_seq_number
Address Customer_ID (FK) Product_ID (FK)
City Status_code (FK) Units
Postal_code Total Unit_cost
Sales_tax
Shipping_charge
Sales_unit
Sales_unit_D
(PK) Name
Product Address
Status City
Product_ID (PK) Postal_cod
Status_code (PK) Name e
Status Description Telephone_number
Cost Email
Sales_unit_ID (FK)
• If we were to set up a DW for that store,we
would start by building the following star schema
Customer Product
Customer_ID (PK) Product_ID (PK)
Name Name
Address Description
City Cost
Postal_code Sales_facts
Product_ID (FK)
Customer_ID (FK)
Time_key (FK)
Sales_unit_ID (FK)
Sales_unit Total Time_period
Sales_tax
Sales_unit_ID (PK) Shipping_charge Time_key
Name (PK)
Address City Description
Postal_code Day
Telephone Fiscal_week
Fiscal_period
Fiscal_year
• B a sic insig hts from comparing OLTP and DWs

– A DW is a separate (RDB MS ) installation that
contains copies of data from on-line systems
• Physically separate hardware may not be absolutely
necessary if you have lots of extra com puting power,
but it is recommended
– With an optim istic locking DBMS you might even
be able to get away for a while with keeping just one
copy of your data
• There is an essentially different pattern of

ha rdwa re utiliz a tion between on-line
and analytical processing
Operational Data warehouse
Applications of DW
• Typica l questions which can be answered
with DW & OLAP
– How much did sales unitA earn in January?
– How much did sales unit B earn in February?
What was their combined sales amount for the first
quarter?
• Answering these questions with S QL -queries is
difficult
– Complex query formulation necessary
– Process is likely to be slow due to complex joins and
multiple scans
Applications of DW (cont’d.)
• Why such questions can be answered better with
a DW?
– Because in a DW tables are rearranged and
pre- ag g reg ated (known as computing cubes)
e.g. Years, weeks, days etc.
• The tables arrangement is subject oriented,usually some
sta r schem a
• A DW is the base repository for front-end
a na lytics
– OLAP
– KDD (Knowledge Discovery in Databases) a
data mining process
– Data visualization
– Reporting
• OLAP is a form of information processing and

thus needs to provide timely,accurate and
understandable information
– timely is however a relative term:
• In OLTP we expect an update to go
through in a matter of seconds
• In OLAP the time to answer a query
can take m inutes, hours or even longer
• There are many flavors of OLAP
– ROLAP, DOLAP, MOLAP, WOLAP, HOLAP,…
• KDD (Data Mining)
– Constructs m odels of the data in question
• Models can be viewed as high level summaries of the
underlying data
ID Name ZIP Sex Age Income Children Car Spent
12 Peter 38106 M 35 € 55,000 2 Mini Van € 210.00

15 Gabriel 38100 M 32 € 56,000 0 SUV € 30.00
… … … … … … … … …
122 Claire 38106 F 21 € 42,000 0 Coupe € 50.00
– Based on this example a query returns the data that
fulfills the constraints
• SELECT * FROM CUSTOMER_TABLE WHERE
TOTAL_SPENT > €100;
– Data mining might return the following set of rules
for customers spending more than €100:
• IF AGE > 35 AND CAR =‘MINIVAN’ THEN TOTAL SPENT
> €100
• IF SEX =‘M’ AND ZIP = 38106 THEN TOTAL SPENT >
€100
– It answers questions like
• Which products or customers are more profitable
• Which outlets have sold the least this year
– In consequence it motivates decisions like
• Which products should have their production increased
• Which customers should be targeted for special
promotions
• Which outlets should be closed
Who is the user?
• Users of DW are called DS S a na lysts
and usually are business persons
– Their primary job is to define and discover
information used in corporate decision-m aking
– The way they think
• “Give me what I say I want,and then I can tell you what I
really want”
• They work in explorative manner
Who is the user? (cont’d.)
– Typical explorative line of work
• “Ah! Now that I see what the possibilities are,I can tell
what I really want to see.But until I know what the
possibilities are,I cannot describe exactly what I want…”
– This usage has fundamental effect on the way a DW

is developed
• The classical system developm ent life cycle
assumes that the requirements are known at the start of
design
• The DSS analyst starts with existing requirements,but
fa ctoring in new requirem ents with time
Summary
• Data Warehouse - Introduction
– DW Definitions
– OLTP vs. DW
– Applications of DW
Next lecture
• Data Warehouse Life Cycle

CS408: Data Warehousing: Welcome To Course

Uploaded by

Copyright:

Available Formats

CS408: Data Warehousing: Welcome To Course

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

CS408: Data Warehousing: Welcome To Course

Uploaded by

Copyright:

Available Formats

Welcome to course

CS408: Data Warehousing

Course Title: Data Warehousing

Course Code: CS408

Database Systems, Data

13 OLAP Queries (continued) • Data visualization and reporting based on Cube

14 Building the DW • Reports and Dashboard management on Powerbi.com

Assessment type Marks weight

Lab Tasks 20%

• Building the Data Warehouse

Introduction to Data Warehouse

1.What is a data warehouse?

Customer activity Customer activity

– The tables contain large volumes of data and the

• In minutes…theWalmart CEO calls Sybase

• OLTP (OnL ine T ransaction P rocessing):

• OLAP (OnL ine A nalytical P rocessing):

Many small transactions Queries long, complex

MB-TB of data GB-PB of data

Raw data Summarized data

Clerical users Decision makers

Up-to-date data May be slightly outdated

• Consider a norm a lized da ta ba se for a store

Customer_ID (PK) Invoice_number (PK) Invoice_number (FK)

• B a sic insig hts from comparing OLTP and DWs

• There is an essentially different pattern of

Operational Data warehouse

• OLAP is a form of information processing and

ID Name ZIP Sex Age Income Children Car Spent

12 Peter 38106 M 35 € 55,000 2 Mini Van € 210.00

– This usage has fundamental effect on the way a DW

• Data Warehouse Life Cycle

You might also like