Contents
Data Warehouse Tutorial For Beginners - Intellipaat 1
Data Warehouse Tutorial For Beginners - Intellipaat
Data comes or extract from multiple sources - clean up and Load the data - Generate the report from that - from the report management take decision
SME will ask the relevant required question
No update in DW database
Terminology
Data
Metadata
Knowledge
OLTP Vs OLAP
DW Architecture
Example
All ATM machines sends data to OLTP - From OLTP historical data goes to OLAP
ODS = Operational Data Source - OLTP and ODS data in sync - Job will sync the data. ODS is a kind of replica of OLTP to reduce OLTP workload
Datawarehouse doesnt have constraints
All constraints to be applied in staging area
SCD
Scenario 1
Data goes from Applications to OLTP to ODS to Staging to DW to Data Mart to Reporting
Data Mart - Report will use either DW DB or Datamart . Data Mart is subset of DW DB - may be less period , may be particular subject area - The Report will connect the concerned Datamart
Data fixing at source - not in DW
???
??? Informatica, SSIS,
Dim Table
Primary Key and Master Data (non measurable data)
Fact Table
PKs of DIMs / FKs and Measurable Columns/data
Types of Schema
Star
Fact connected with all dimensions
Snowflake
Fact table only few of the dim keys connected
Types of Dimensions
Conform/Reusable Dim - ex - Calander or Holidays, Location Dim (city,state,Pincode)
Junck Dim - keep on updating - ex Stock price
Degenerated Dim - ?
Slowly Changing Dim (SCD)
SCD Type 1 - Latest / current data
New record will be inserted
Updated data will be updated without maintaining history
SCD Type 2 - Historical Data
Earlier record’s UpdatedData as system date
Update will be inserted as aNew record with future updated date
SCD Type 3 - Partial History
Only one previous data based on table design available - need to define which column we need to maintain the partial history
Types of FACT - 4
Aditive - star schema
Semi additive fact - snowflake schema
Non Aditive Fact -
Fact Less Fact
Approach
Top Down
Bottom Top
Data Modeling 1:47 starts
Conceptual
Very preliminary level
All the entities in higher level created here (table we call in conceptual as Entity)
Logical
Physical
Interview Questions
General
Fact Table derived from Dim tables
OLTP have dim table as Customer, employee, product etc
Based on Dimension table we create Fact Table adding all PKs from DIM and adding measurable columns
No comments:
Post a Comment