Please enable JavaScript.

Coggle requires JavaScript to display documents.

Data Warehousing Concept, COURSE LINK HERE - Coggle Diagram

- - - - ie. a good data warehouse supports your ongoing analysis in a good quality
  - - - database is online, volatile
        
        eg. transactional data
      - data warehouse
        
        -1 INTEGRATION
        
        data source:
        
        INTEGRATE dozens of sources
        
        online datasets
        
        spread sheet
        
        DBs
        
        data in DW is duplicated from the DBs
        
        -2 subject oriented #
        
        DW is built for a specific goal
        
        ie. supporting data driven decision in a department, team
        
        -3 time variant
        
        DW store HISTORICAL data #
        
        -4 Non Volatile
        
        between updating of DW, the data inside stay static
        
        updating cycle can be few hrs, a day, a week...
        
        unlike DBs, each second can have thousands of update
  - - - Past, Present, Future, Unknown
    - - before DW
        
        Data were spreading every where, gathering the data to make decision was tedious and problematic
      - after DW
        
        all the data are integrated at a place, making consistent BI possible
  - - - built upon relational data
        
        Uses RDBMS
        
        like a Swiss Army Knife, being shape and built well for a specific purpose
      - data sources are in a tabular form
    - - built upon non relational data
        
        uses NoSQL
        
        3Vs of Big data
        
        Variety
        
        data sources can be any kinds of data, video, audio, text....
        
        Volume
        
        Velocity
        
        updating really fast
  - - - copy data from DBs, integrate, reorganize to another place
        
        data in DW are transformed (READ-WRITE) #
    - - create a virtual view in existing DBs, not copying
        
        think about the VIEW function in SQL
        
        VIEWs are READ ONLY
        
        not changing the underlying data in DBs, simply query and perform a VIEW using DML
        
        feature
        
        Simple Transformation on data
        
        Small number of Data
        
        Relaxed response time
      - other names
        
        Enterprise Information Integration (EII)
        
        Virtual Data Warehouse
        
        Enterprise Data Access (EDA)
  - - - Data Sources (DBs) ->
        
        ETL ->
        
        Data Warehouse ->
        
        ETL ->
        
        Data Mart ( a specific dataset for a specific analysis
        
        BI
    - - Data Source = Manufacturer
        
        ETL = Shipping
        
        Data Warehouse = Whole seller
        
        ETL = Shipping
        
        Data Mart = Retailer
        
        BI = Consumer
- - - - no data marts
      - data are then ETL by BI application
    - - need High-cross org cooperation
      - need High Data governance
      - make ripple effect
        
        if the application is modified, the end users' view is changed
    - - -1 EDW
        
        Relational
        
        use a DB as a DW
        
        Specialized
        
        use some software the focus on DW to build DW
      - -2 Data lake
  - - - Data in DW are consumed by numerous of dependent DataMarts #
      - ETL from MANY DBs #
    - - two type
        
        Dependent DataMart
        
        ETL from a DW
        
        Uniform data across many data marts (dependent
        
        Independent DataMart
        
        ETL from A DB
        
        little uniform in data across different data marts (independent
      - data in DataMart are then consumed by BI application
    - - Decomposition on the relationship of data
      - Mix-Match tech
      - Bolt-together components
      - Overcome cross-org challenge
      - BAD
        
        inconsistent data
        
        difficult to cross integrate
    - - -1 Architected
        
        DW+DMs
        
        -1 DW -> dependent DMs
        
        -2 front end DM
        
        DMs -> DW
        
        eg. SAP application
        
        DMs only (independent
        
        THINK DM AS a View in DB ( data virtualization
        
        data marts are some what related
      - -2 Non Architected
        
        federated data mart (Independent
        
        Last resort to go to!
        
        data marts are highly independent
  - - - not a Relational Database (RDBMS
      - best for small scale DWs, DMs
      - fast query response time
        
        speed is sensitive when data go large
        
        less flexible data structure than RDBMS
    - - USE RDBMS as DW and CUBE as Datamart
  - - - emphasis on current operational data
        
        NOT focus on past, future but focus on NOW
        
        answer question: "what is happening NOW"
      - Less popular today, because
        
        there are more "faster" DWs
        
        Big Data system also has "Velocity" feature
        
        some Data Lake has ODS as a component
    - - DB
        
        BATCH ETL
        
        DW
        
        STRATEGIC decision
        
        RAPID ETL
        
        ODS
        
        OPERATIONAL decision
  - - - Staging Layer
        
        extract data tables from different DBs and coping for later T uses
        
        Minimum T here, no need to transform, concat tables
        
        next layer is Access Layer ( where user access data
      - Two Types
        
        Non Persistent
        
        after staging layer copies data into access layer, staging layer is being erased to empty
        
        pro
        
        save memory
        
        con
        
        if access layer collapse, the recover needs more time, query the whole DBs again
        
        harder to do Data Quality Assurance (data integrity
        
        Persistent
        
        after staging layer copy data into access layer, staging layer does not erase the data
        
        pro
        
        Accurate in Data Quality Assurance ( data integrity
        
        easier to recover data if access break
        
        con
        
        More storage space consumption
        
        risk of ungoverned access of data
        
        ie. the older data stored in the staging layer may be accessed by people without permit to see the older data
- - - - Extract -> (to staging layer
        
        Transfrom -> (in staging layer
        
        Load -> (to access layer
        
        Access layer
  - - - including error, missing...
  - - - Match same type of information and transform to same data type
    - - Uniformity
        
        Unify label names
        
        Eg.
        titles in DB1 : Vice President Finance
        titles in DB2 : VP. Fin
        
        Unify with same label, eg. VP. Fin
        
        Unify data type
        
        Eg.
        titles in DB1 : CHAR(30)
        titles in DB2 : CHAR(10)
        
        Unify to CHAR(10)
        
        remove duplicate
        
        eg. same student appear in different campuses' DBs
      - Slicing
        
        Slicing on rows or columns
        
        slice the data because the later analysis will NOT NEED the information
      - Cleaning
        
        Remove missing, error (wrong label, not reasonable existing data
  - - - used ELT in Data Lake
        
        eg. Hadoop, AWS S3
    - - Transform the data right before the analysis is performed
    - - Schema definition
        
        the structure for the data
        
        the index, relationship of data
      - Schema on READ
        
        schema is created when the user read(access) the data (in data lake)
      - Schema on WRITE
        
        schema is created when the system write the data into DW
  - - - ETL is performed ONE time ONLY
        
        do right before the DW goes alive
        
        Redo only when DW crushes
      - Extract only Possible USEFUL information
        
        USEFUL = the later analysis will need this pice of information
    - - Incrementally UPDATE the DW
        
        make existing DW up to date
        
        Bringing in new rows and cell values
        
        old information being archived
        
        eg. customers drop from subscription, employees resign
        
        eg. new employees, new product, price change
        
        5 possible approaches
        
        Append
        
        adding new rows
        
        eg. newly hired employee
        
        in place update
        
        updating the existing rows in DW
        
        eg. a existing employee's salary change
        
        Complete replacement
        
        over write the old data with new data
        
        eg. when a employee is promoted, the corresponding row in DW is not updated only by a cell, but the row is replaced by new row
        
        rarely used now
        
        Rolling append
        
        data in DW is preserved for a rolling time window
        
        eg. preserve only 36 months sales data
        
        rarely used now
        
        MIX an Match
        
        The updating of the DW is not necessarily the same
        
        Depends on the nature and the importance of a piece of information, different tables can be refreshed in different frequency
        
        eg. the transaction table can be updated hourly; the store table can be update monthly
- - - - Basic Reporting
        
        Dimensional model
      - Online Analytical Processing (OLAP)
        
        Dimensional model #
      - Predictive/ Exploratory Analytics
        
        Data mining model, specialized model
  - - - Dimension means "SLICE" or "GROUP"
        
        the context of a measurement
    - - When answering a business question, we need certain piece of information which is a measurement sliced by dimensions
        
        eg.
        
        What is the average annual faculty salary BY rank, department, academic year
        
        dimension = rank, department, academic year
        
        measurement = average annual faculty salary
    - - different types of dimension architectures,
        differ in how they slice dimensions into table
        
        SNOW Flake schema
        
        one dimension table contains only one dimension
        
        more table joins are needed
        
        each dimension table is farer from (more joins) fact table
        
        fewer redundant information
        
        save space
        
        not repeating the upper level of hierarchy in rows
        
        Normalized data
        
        STAR schema
        
        one dimension table contains multiple dimensions (in a branch of hierarchy
        
        Hierarchy of Dimension eg.
        
        the product category
        
        the product family
        
        the product
        
        less table joins are needed
        
        each dimension table is only one step from the fact table
        
        more redundant information
        
        each row needs to repeat the higher level dimension in the hierarchy
        
        Denormalized data
  - - - fact is numeric and quantifiable measurement #
        
        fact = measurement
        
        eg. salary, number of students
        
        Dimension is the context of fact
        
        eg. number of employees in revenue department
    - - FACT != Fact table
        
        Facts are stored in fact table
      - FACT in DW != Logical fact
        
        Eg.
        I watching my laptop screen is a logical fact, but not a FACT in DW
      - Dimension != Dimension table #
    - - Addible fact
        
        Facts that can be added up (performing some calculations across the rows
        
        eg.
        salary -> total salary
        population -> total population
      - Non-Addible fact
        
        facts that can not be added up across the rows
        
        eg.
        student's GPA (adding up all GPA is meaningless
        
        usually some already calculated value (derived variable
        
        eg. margins, ratios, percentage, calculated average
        
        try to avoid store non addible facts in a fact table
        
        HOW?
        
        store underlying component
        
        eg. store the female population but not the percentage of female in population
        
        ie. avoiding derived variable!
        
        if derived variable is stored for easy access
        
        just perform minimal calculation!
      - Semi-Addible fact
        
        periodical snapshot
  - - - dimensional data = Facts + Dimensions
  - - - The way to connect tables (perform JOINTs)
    - - Primary key, PK
        
        a key uniquely identify a row in a table
        
        eg. Customer_ID in the customer table
      - Foreign Key, FK
        
        a key that is PK in other table
        
        eg. the Customer_ID in the transaction table
      - Natural Key, NK
        
        the key that can be used as PK
        
        eg. Faculty_ID
        
        it's usually the PK in the table in the DBs where the DW extract from
        
        eg. when we extract the customer table in the DB, put the data into DW, in the DW, we also get a column as Customer_ID
        
        BUT in the DW, we don't necessarily use Customer_ID (PK in DB) as a PK in DW
      - Surrogate Key, SK
        
        a key we create to be PK in a table
        
        even though there is already an NK we can use as PK
        
        do this for Data Integrity in DW
        
        Its value is MEANINGLESS, the only purposed is to UNIQUELY identify a row
        
        eg. in the customer table, we have an NK: Customer_ID, but we don't use Customer_ID; Instead, we create a meaningless random number: Customer_Key to be the PK of the table
        
        IN DW, the best practice is to use SK to be a PK
- - - - a dimension table should contain as much as possible for the dimension it represents
      - dimensions provide context for fact (measurement)
    - - FLAT dimensions
        
        a hierarchy is flattened into a table
        
        used the lowest level dimension as PK
        
        eg.
        
        Dimension Hierarchy: College > Department > Faculty
        
        in Faculty_DIM table
        
        - Faculty dimension:
        Faculty_Key (PK)
        Faculty_ID
        Faculty_L_name
        Faculty_F_name
        Gender
        Rank
        - Department dimension:
        Dept_ID
        Dept_name
        Dept_founded_year
        - College dimension:
        College_ID
        College_name
        Col_founded_year
        Dean
    - - Hierarchy Dimension
        
        each level in a hierarchy is a dimension table, used PK, FK to JOIN
        
        eg.
        
        Dimension Hierarchy: College > Department > Faculty
        
        in Faculty_DIM table
        
        - Faculty dimension:
        Faculty_Key (PK)
        Faculty_ID
        Faculty_L_name
        Faculty_F_name
        Gender
        Rank
        Dept_Key (FK)
        
        FK connects to one level higher in the Hierarchy
        
        in Department_DIM table
        
        - Department dimension:
        Dept_Key (PK)
        Dept_ID
        Dept_name
        Dept_founded_year
        College_Key (FK)
        
        FK connects to one level higher in the Hierarchy
        
        in College_DIM table
        
        - College dimension:
        College_Key (PK)
        College_ID
        College_name
        Col_founded_year
        Dean
        
        No FK in this table since it's the highest level in a Hierarchy, ie. TERMINAL Dimension
  - - - Fact Table stores facts (measurements), numeric value
    - - Transaction Fact Table
        
        record facts from transactions
        
        Store measurement in transaction-GRAIND
        
        USE Fact + Dimension FK to provide context
        
        Eg.
        
        Tuition_Bill_FACT:
        Tuition_Bill_amount
        Bill_id (NK from DB)
        Student_KEY (FK)
        Date_KEY (FK)**
        
        Date_Key is a Surrogate Key (PK) to identify a specific date in Date_DIM
        
        PK here is a Composite Key
        Student_Key + Date_Key
        
        Here even the NK can uniquely identify row, we still use two SK as PK
        
        if meet the rules, more than one facts can be stored in a fact table
        
        RULES
        
        -1 Fact available at same grain (level of detail)
        
        level of detail = the dimensions you use to add context for that measurement
        
        -2 Facts occur simultaneously
        
        eg.
        
        Wrong Eg.
        Tuition_Payment_FACT
        Fact_1: tuition_billed_amount
        Fact_2: tuition_payment_amount
        
        R1 :check: both of them use student_key, date_key as context
        R2 :green_cross: the tuition_billed_amount happened earlier than the tuition_payment_amount
        (billed_amt is the amt school charges, the payment_amt is the amt school received)
        
        Billing and Payment are actually two different business process
        
        2 more items...
        
        Correct Eg.
        Tuition_Activity_FACT
        Fact_1: tuition_billed_amt
        Fact_2: activities_fee_billed_amt
        
        R1 :check: two bill happen on same grain, ie. same student, same semester...
        R2 :check: two bill happen simultaneously
        
        BUT
        
        Wrong Eg.
        Tuition_Activity_FACT
        Fact_1: tuition_billed_amt
        Fact_2: activities_fee_billed_amt
        
        1 more item...
      - Periodic Snapshot Fact Table
        
        track a certain measurement at a certain time interval
        
        time is also a dimension, so a certain time interval = a locked dimension
        
        a measurement is just some calculation we make on the numeric data (fact)
        
        two types of Periodic Snapshot fact
        
        Aggregation of "regular" transaction
        
        eg. weekly revenue
        
        DIM: Lot_DIM, Date_DIM
        FACT: Week_Revenue_Snapshot_FACT
        
        Week_Revenue_Snapshot_FACT:
        Weekly_Revenue
        Lot_Key (PK,FK)
        Week_Key (PK,FK)
        
        Revenue is the sum (aggregation) of the transaction amount
        
        Levels that are not related to "regular" transaction
        
        eg. daily water level of a reservoir
        
        DIM: Reservoir_DIM, Week_DIM
        FACT: Week_Water_level_Snapshot_FACT
        
        Water level is just some periodically measured sensor number
        
        the difference between two is only how do we get the number, structure of the fact table is the same
        
        Semi-Addictive Fact
        
        in Snapshot Fact table, the Facts are usually the Semi-Addictive Fact
        
        in some dimensions can be added up, but in some dimensions can't be
        
        eg.
        
        Weekly meal plan balance of a student
        
        adding up one student's all balance is meaningless
        
        adding up all students' weekly balance in certain week is insightful
      - Accumulating Snapshot Fact Table
        
        track a progress of a business process through formally defined stages
        
        what is the fact here?
        
        the elapsed time spend in each stage
        
        Include both completed and in-progress business process
        
        can also track other measurements as process proceeds
        
        the relationship between Fact table and Dimension table becomes Many to Many
        
        eg.
        
        student financial aid application process
        
        process:
        submit application -> application screening ->
        preliminary decision -> final decision ->
        payment made to student's account
        
        between process, there are days spent in this step
        
        dimensions: student_DIM, date_DIM, college_emp_DIM
        
        Fact_Table
        Fact
        days_spent_screening
        days_spent_preliminary_decision
        days_spent_final_decision
        days_spent_payment_made
        dimension_date
        application_submitted_Date_Key (PK,FK) application_screening_start_Date_Key (PK,FK) preliminary_decision_made_Date_Key (PK,FK) final_decision_made_Date_Key (PK,FK) payment_made_Date_Key (PK,FK)
        dimension_employee
        application_screening_Emp_Key (PK,FK) preliminary_decision_made_Emp_Key (PK,FK) final_decision_made_Emp_Key (PK,FK) payment_made_Emp_Key (PK,FK)
        Other Trackable Fact
        amt_submitted
        amt_screening
        amt_preliminary_decision
        amt_final_decision
        amt_paid
        
        video
      - Factless Fact Table
        
        1.record OCCURRENCE of a transactions that has no measurement
        
        record coverage or eligibility relationships
        
        ie. USED when we want to TRACK AN EVENT
        
        the "fact" is no longer a column in the fact table, but the "COUNTS"
        
        eg. track students registering online webinar in a semester
        
        track what?
        
        which student register
        
        for which webinar
        
        the date registering happened
        
        the date of the webinar
        
        dimension: student_DIM, date_DIM, webinar_DIM
        
        Webinar_Register_fact_table
        student_Key(PK, FK)
        webinar_Key(PK, FK)
        register_date_Key(PK, FK)
        event_scheduled_date_Key(PK, FK)
        Webinar_Attend_fact_table
        student_Key(PK, FK)
        webinar_Key(PK, FK)
        event_held_date_Key(PK, FK)
        
        note: PK, FK only, no "fact"
        
        we also can put some fact into the table, eg.
        
        Webinar_Attend_fact_table
        student_Key(PK, FK)
        webinar_Key(PK, FK)
        event_held_date_Key(PK, FK)
        Days_between_register_attend
        
        but the essence of the Factless fact table is still "COUNT" of OCCURRENCE
        
        Video
- - - - "historical data in DW" != "historical data of DBs"
      - the Idea is that we some day probably want to see what did we do in this DW
  - - - In-place update ETL
        
        Use the new value (of a row, cell) to replace the old value
        
        the replaced old value disappears forever
      - eg.
        
        in student_DIM, we have a typo, make John Doe's birthday 02/17/2012, which has a correct date of 02/17/2002
        
        we replace the cell 02/17/2012 with 02/17/2002, and don't store the OLD data 02/17/2012, so the 02/17/2012 disappear after this update
      - Used majorly for error correcting
    - - Create a new row in dimension table for each version of new data
        
        most robust
        
        existing rows (old data) remain as-is
        
        as new row created in Dimension table, in Fact table, the corresponding row with the FK is added
        
        Use for some randomly change while the OLD data stays MEANINGFUL
      - eg. in Student_DIM, one student move his residency from PA to CO
        
        In Student_DIM
        create a new row for the student, using CO, give a new SK
        
        in Student_Loan_FACT
        Use the new SK to create a new row with its corresponding fact ( remaining loan_amt)
        Create a new column "expire_date_Key" to track which row is the "newest information"
        
        When analyzing, if the dimension we want to analyze relates to the dimension with multiple version of fact, use NK
        
        eg. the avg loan amount by students, use Student_ID, instead of Student_Key
        
        video
    - - use columns in dimension table for old data
        
        use for largely affecting change (affecting many rows
        
        eg. the reorganizing of department
      - eg. a company is going to slice its division for sales rep from North, South to East, Central, West
        
        in Sale_Rep_DIM
- - - - not all the data in DBs should be ETL to DW
    - - when building dimension table, we construct the Key, then we know what will be used to provide context for facts
    - - eg. in snow flake schema, the higher level dimension should be processed first
  - - - Identify which rows in DBs are NEW to DW
        
        how?
        
        Timestamp in rows
        
        database log
        
        scan on DBs and Compare
        
        last option
    - - data preparation #
        
        data transformation, cleaning
        
        process new dimension rows, check existence
        
        if the row is new
        
        append, with newly assigned SK
        
        if the row is old (already exist
        
        1 more item...
    - - if there is a type 2 SCD happened in previous iteration of updating, use NK to identify rows we need to perform SCD
        
        eg.
        
        At time t-3 in Student_DIM,
        we update John Doe's residency with SCD 2 #
        
        At time t in Student_DIM, we found an error on John Doe's birth day, so we perform type 1 SCD
        
        Now at time t, there are 2 rows of John Doe, one is original, one is added by type 2 SCD at Time t-1
        
        We use NK(Student_ID) to perform Type 1 SCD on 2 rows for John Doe
    - - append new rows with new information, assign new SK
    - - when there is a new fact, we need to be very careful about "which version" of dimension we want to use as the context of this fact
        
        Use the effect_date, expire_date, current_ver_label in Dimension table to determine
        
        eg.
        
        use the above John Doe example
        
        now we want to assign a FK for fact of John Doe
        
        if the fact is at t+1, then the FK should use the SK we create in time t
        
        if the fact is at t-2, then the FK should use the SK we create in time t-3