Choose one of the data warehousing / data mart dimensional models listed below. Copy the data model into a MS Word document. In your MS Word document. (You can copy the data model graphic by right-clicking on it. On PCs, the keyboard combination of ctrl-V is the shortcut command for pasting.) In the MS Word document, 1) identify the fact table 2) identify the dimension tables, 3) for each dimension table, identify the attribute(s) that would be matched to an attribute in the fact table, and 4) list four queries that could be produced from this data model. For example, the queries from a data model of real estate sales that could typically be produced by matching a fact table to one or more dimension tables include: a) list of properties sold by each real estate agent, b) total properties sold by state, c) types of properties sold by month and year, d) dollar value of properties sold by each real estate company, e) total homes sold by real estate agent by company by year.

Insurance Data Warehouse / Dimensional Model

3NF Data Model

Dimensional Model

Customers Customers_Demographics Customer_Policies Payments Available_Policies Claims (Car_Policies) (Home_Policies) (Life_Policies) Addresses_ PK Address_ID Address Details Customers PK Customer_ID Reporting Date Customer First Name Customer_Middle_Name Customer_Last Name Gender Date of Birth Address_Details City Country Other_Details Locations_ PK Location_ID Address_ID Location Type_Code Location Details Insurance_Dimensional Model PK Fact_ID FK Address_ID FK Customer_ID FK Data_Level FK Location_ID FK Location Type_Code FK Policy Type Code FK Reporting Date FK Payment Date FK Policy Start Date FK Policy End Date Total_Customer_Count Total_Revenue Total_Settlements Total_Volume Total_Value KPI_Percent_1 Averages, Counts, Totals KPIs, Graphs and Trends Other Derived Figures + Ref_Calendar PK Day Date Day_Number Ref_Data_Levels PK Data_Level Data_Level_Description eg Global Region Ref Location Types PK Location Type_Code Location Type_Description eg Region such as EMEA Ref_Policy Types PK Policy Type_Code Policy Type_Name eg Car, Home, Life