DatabaseSchema.pptVIP

Database Schema Akanksha (HT00-6255Y) E. V. Anantha Lakshmi (HD99-9054A) Sze Eng Koon (HD99-2800H) Material Available at .sg/~szeek/cs6203 Agenda Data warehouse Identifying facts and dimensions Designing fact tables Designing dimension tables Designing starflake schemas Multidimensional schema Data Warehouse Relational database designed for query and analysis Contains historical data derived from transaction data Enables organization to consolidate data from several sources Schema Consists of DB objects such as tables, views, indexes There are many types of schema, e.g., star, snowflake Fact In decision support DW, a number of queries ask about an essential fact E.g., top 20% spending customers Fact: Loyalty card transactions Characteristics Transactions that occurred in past and won’t change Analyzed in many ways by cross-referencing with attributes Fact Table A table that contains facts Contains numeric, additive fields (measurements of the business) It has two types of columns: containing facts foreign keys to dimension tables Dimension A structure that categorizes data to enable end users to answer business questions e.g., Time, Location, Customer Example Video chain store builds DW to analyze sales of its products across all stores over time Effect of promoting one product on sale of related product that is not promoted? Product sales before and after the promotion? Facts sales (units sold or rented) and profits Dimensions products, locations (stores), promotions, time Major Considerations Data warehouse supports business process, not specific queries Must understand what information will be used Challenge: Solution should be effective for a reasonable time period (3-5 years) Design should allow unknown queries to be able to perform Identifying Facts Dimensions Given large entity model Look for elemental transactions in business process Determine key dimensions that apply to facts Check that a candidate fact is not a dimensi