Please enable JavaScript.
Coggle requires JavaScript to display documents.
Week 2: Data Warehouse & OLAP (Characteristics of DW Data (Aggregated,…
Week 2: Data Warehouse & OLAP
What is DW?
A collection of integrated, subject orientated databases designed to support Decision Support systems functions
Characteristics of DW Data
Aggregated
Data stored is not transaction level, but aggregated by business dimensions
Historical
Data updated and stored at some time interval
Integrated
Data collected is integrated
for a unified picture;transformed into a common
schema
Time Variant
Data always includes
timestamp
Non-volatile
Data is historical, does
not change with time
Denormalized
Used to improve query
performance
Subject Oriented
Data organized around subject/business dimensions
Representation of Data in a DW (3 Schemas)
Star Schema
Fact Table related to each dimension table in a many to one relationship through PK and FK
Fact Table located at
the center
Contains measures and
a foreign key for each
dimension table
Constellation Schema
Variation of Star Schema; consists of more than 1 Fact Table
Snowflake Schema
Dimensions are
normalized into multiple related tables
through hierarchies
Hierarchies
Structure made up of 2
or more levels
Enable users to navigate to different levels of granularity (level of detail in a unit of data)
Measures and Dimensions
Measure is a property on which calculations can be made
Dimension is a structure that categorizes measures to enable users to answer business questions
OLAP Operations
Drill Down/Up
navigating among levels of data (most summarized to most detailed
Roll up
computing all data relationships for one or more dimensions
Dice
a slice on 2 or more than 2 dimensions
Pivot
used to change the dimensional orientation of report/query page display
Slice
subset of multidimensional array
Operational Data vs Decision Support Data
Operational Data (OLTP): low granularity, single focus
Decision Support Data (OLAP): high granularity, multiple dimensions
Data Warehousing Process (ETL)
Transformation
converting extracted data to form needed for DW
Load
putting transformed data into DW
Extraction
reading data from 1 or more DB
Types of DW
Operational Data Stores (ODS)
Used as interim staging for DW; contents updated during operations; used for short term decisions
Data Marts
Smaller, focuses on particular subject or dept
Dependent Data Mart
subset; created directly from DW
Independent Data Mart
a small DW designed for strategic biz unit/ dept
Enterprise Data Warehouse (EDW)
Large scale DW used across enterprise for decision support
Types of Facts
Semi-additive
Can be added from some dimensions but not others
Addictive
Can be added (Revenue, Commission)
Non-additive
Facts that cannot be combined to get a grand total (%, ratios)
Types of Dimensions
Normally static, but some change over time; Slowly Changing Dimensions (SCD)
Type 2 SCD
Tracks changes; 4 supplementary attributes must be added
SCD Start Date
SCD End Date
SCD Original ID
SCD Status
Type 3 SCD
Tracks only current and original state; implemented using 2 additional attributes
SCD Start Date
SCD Initial Value
Type 1 SCD
Does not track changes; overwrites previous info with current info results in inaccurate analysis