Data warehousing
This presentation is the property of its rightful owner.
Sponsored Links
1 / 14

Data Warehousing PowerPoint PPT Presentation


  • 89 Views
  • Uploaded on
  • Presentation posted in: General

Data Warehousing. Dr. Vairam Arunachalam. Agenda. Value Chain “Clean” construction of DDW Financial Services Subscription Businesses Insurance Factless fact tables Decision Points in DDW construction. Value Chain. Concept: integrated view of value-adding components of business process

Download Presentation

Data Warehousing

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Data warehousing

Data Warehousing

Dr. Vairam Arunachalam


Agenda

Agenda

  • Value Chain

  • “Clean” construction of DDW

  • Financial Services

  • Subscription Businesses

  • Insurance

  • Factless fact tables

  • Decision Points in DDW construction

Dr. Vairam Arunachalam


Value chain

Value Chain

  • Concept: integrated view of value-adding components of business process

  • Example on Demand side:

    • Finished Good inventory

    • Manufacturing Shipments to Distribution Center

    • Distribution Center Inventory

    • Distribution Center Shipments to Retail Stores

    • Retail Store Inventory

    • Retail Store Sales

Dr. Vairam Arunachalam


Value chain contd

Value Chain (contd.)

  • Example on Supply side:

    • Purchase Orders

    • Receiving

    • (Raw) Materials Inventory

    • Process Control

    • BOM

    • Finished Goods Inventory

    • Manufacturing Plans

Dr. Vairam Arunachalam


Value chain contd1

Value Chain (contd.)

  • Issues related to integration of value chain information (I.e., drill-across):

    • Shared dimensions

    • Differences in physical dimension tables

    • Common dimension tables as a solution

  • Design Principle:

    • All constraints on dimensional attributes must evaluate to exactly the same set of dimensional entities from one db to another in the value chain

Dr. Vairam Arunachalam


Value chain contd2

Value Chain (contd.)

  • Dimensions with reduced detail (e.g., manufacturing lot nos. versus SKUs)

  • Derived dimensions supporting aggregates (e.g., construction of derived roll-up product dimension and fact tables)

Dr. Vairam Arunachalam


Clean construction of ddw

“Clean” construction of DDW

  • Design principle:

    • A master file, usually the source of unique identification, must be maintained on a regular basis. This needs QA on the p-key and other fields.

  • Snowflaking: the good (remember normalization?) and the bad (issue of browsing performance) -- Fig.6.2

  • Demographic minidimensions -- Fig.6-3

Dr. Vairam Arunachalam


Clean ddw contd

“Clean” DDW (contd.)

  • Slowly changing dimensions (implications, pro and con):

    • Type 1 (Overwriting old values; losing ability to track history)

    • Type 2 (Creating an additional dimension record; segmenting history)

    • Type 3 (Creating new fields with new attribute values within original dimension record, while keeping original attribute values; describing history both backward and forward)

Dr. Vairam Arunachalam


Financial services

Financial Services

  • Core fact tables: Household data warehouse (Fig.7.1)

  • Dirty dimensions

  • Semiadditive account balances

  • Heterogeneous products (Fig.7.3):

    • Design principles:

      • create a core fact and core dimension tables for crossing types, and a custom fact and custom dimension tables for querying

      • primary core facts duplicated in custom fact tables

Dr. Vairam Arunachalam


Subscription businesses

Subscription Businesses

  • Accounting concept underlying payments in advance (I.e., deferred revenues)

  • Design principle:

    • Combine transaction-grained fact table with a monthly snapshot-grained fact table in order to get at transaction frequency/timing and earned income in a given period

  • Cable TV sales transaction and sales monthly snapshot databases (Figs.8.1 & 8.2)

Dr. Vairam Arunachalam


Insurance

Insurance

  • Good illustration of several important concepts:

    • business process

    • grain, dimensions (including degenerate and dirty dimensions)

    • core & custom dimension and fact tables

    • transaction & snapshot schemas

    • heterogeneous products

    • slowly changing dimensions

    • minidimensions

Dr. Vairam Arunachalam


Insurance1

Insurance

  • Initial policy transaction and snapshot schemas (Figs.9.1 and 9.3) and claims transaction and snapshot schemas (Figs.9.2 and 9.4)

Dr. Vairam Arunachalam


Factless fact tables

Factless Fact Tables

  • Concept: no measured facts (still useful)

  • Types:

    • event tracking (e.g., which hospital procedures were performed most extensively?)

    • coverage (e.g., which customers did not purchase any products?)

  • Hospital patient procedure schema (Fig.10.2)

Dr. Vairam Arunachalam


Decision points in ddw construction

Decision Points in DDW construction

1. Processes -> fact table identification

2. Grain of fact table

3. Dimensions of fact table

4. Facts

5. Dimension attributes

6. Slowly changing dimensions

7. Aggregations, heterogeneity, minidimensions, queries

8. Historical duration of db

9. Timeframe for data extraction/loading into DW

Dr. Vairam Arunachalam


  • Login