Chapter 4 Tutorial Modeling Data Warehouse A data

Chapter 4 Tutorial

Modeling Data Warehouse • A data warehouse is based on a multidimensional data model which views data in the form of a data cube • A data cube allows data to be modeled and viewed in multiple dimensions o Dimension tables o Fact table contains measures and keys to related dimension tables

Cont. 1. Star schema: A fact table in the middle connected to a set of dimension tables. 2. Snowflake schema: represents dimensional hierarchy by normalizing the dimension tables. • save storage • reduces the effectiveness of browsing 3. Fact constellations: Multiple fact tables share dimension tables

Q 3 • Suppose that a data warehouse consists of the three dimensions time, doctor, and patient, and the two measures count and charge, where charge is the fee that a doctor charges a patient for a visit. (a) Enumerate three classes of schemas that are popularly used for modeling data warehouses. 1. 2. 3. Star schema Snowflake schema Fact constellation schema

Q 3 cont. (b) Draw a schema diagram for the above data warehouse using one of the schema classes listed in (a). Using a star schema.

Q 3 cont. Star Schema time_key day_of_the_week month quarter year patient_id patient_name phone # address gender doctor Fact Table time_key doctor_id doctor_name phone # address gender patient_id Charge Count Measures

Q 5 Suppose that a data warehouse consists of the four dimensions, date, spectator, location, and game, and the two measures, count and charge, where charge is the fare that a spectator pays when watching a game on a given date. Spectators may be students, adults, or seniors, with each category having its own charge rate. Draw a star schema diagram for the data warehouse.

Q 5 cont. Star Schema date_id day month quarter year spectator Sales Fact Table date_id spectator_id location_id game_id game_name description producer game_id Charge Count spectator_id spectator_name phone # address Status Charge rate location_id phone # Street city province country