Please enable JavaScript.
Coggle requires JavaScript to display documents.
BI: Data Warehouse & OLAP (W2) (Data Warehouse (DW) (Characteristics…
BI:
Data Warehouse & OLAP (W2)
Data Warehouse (DW)
What is DW?
A collection of
integrated, subject-oriented
databases designed to support DSS functions, where each unit of data is
non-volatile and relevant
to some moment in time.
Characteristics of DW
Time Variant
Non-volatile
Historical
Denormalized
Integrated (combined)
Aggregated
Subject-Oriented
Types of DW
Operational Data Stores (ODS)
Data Marts
Enterprise Data Warehouse (EDW)
DW process (ETL)
Transformation
Load
Extraction
Representation of data in DW
Snowflake Schema
Constellation Schema
Star Schema
Steps in designing DW/ Data Mart
Choosing the Data Mart
Choosing the table granularity
Identifying and conforming the dimensions
Choosing the facts
Storing pre-calculations in the fact table
Rounding out the Dimension table
Choosing the duration of the database
The need to track SCD
Measures and Dimensions
Measure
A property on which calculations (sum, count, average, min & max) can be made.
Dimension
A structure that
categorizes measures
in order to enable users to
answer business questions
. Commonly used dimensions are people, product, place and time.
Dimension Hierarchy
A structure made up of
2 or more levels
of related dimensions
Hierarchy enable users to navigate to different levels of
granularity
within the measures of the DW
Types of dimensions
Type 3 SCD
Do not track entire history of the D members
Tracks only current and original state of D members
Type 1 SCD
Does not track changes
Overwrites previous D info with current D info
Impossible to determine state of the D members in the past
Result in inaccurate analysis
Type 2 SCD
Track changes
4 needed supplementary attributes
-- SCD Original ID
-- SCD Start date
-- SCD End date
-- SCD Status
3 types of Facts
Semi-Additive (some can, some cannot)
[e.g Products can be added, but the time cannot be added]
Non-Additive (Facts that
cannot
be combined)
[ratios, percentage]
Additive (facts that
can
be combined)
[e.g revenue, commissions, etc]