IT 240 Assignment 9
Create a star schema for the Fitchwood Insurance Company case study below. Be sure to address the time dimension.
Cardinality notation (i.e. 1 - many) is required, although it can be drawn by hand. The fact and dimension tables must be created electronically - no hand drawn submissions.
Fitchwood Insurance Company, which is primarily involved in the sales of annuity products, would like to design a data mart for its sales and marketing organization. Presently, the OLTP system is a legacy system residing on a Novell network consisting of approximately 600 different flat files. For the purposes of our case study, we can assume that thirty different flat files are going to be used for the data mart. Some of these flat files are transaction files that change constantly. The OLTP system is shut down overnight on Friday evening beginning at 6 PM for backup. During that time, the flat files are copied to another server, an extraction process is run, and the extracts are sent via FTP to a UNIX server. A process is run on the UNIX server to load the extracts into Oracle and rebuild the star schema. For the initial loading of the data mart, all information from the thirty files was extracted and loaded. On a weekly basis, only additions and updates will be included in the extracts.
Although the data contained in the OLTP system are broad, the sales and marketing organization would like to focus on the sales data only. After substantial analysis, the ERD shown in Figure 11-25 was developed to describe the data to be used to populate the data mart.
From this ERD, we get the set of relations shown in Figure 11-26. Sales and marketing is interested in viewing all sales data by territory, effective date, type of policy, and face value. In addition, the data mart should be able to provide reporting by individual agent on sales as well as commissions earned. Occasionally, the sales territories are revised (i.e., zip codes are added or deleted). The Last Redistrict attribute of the Territory table is used to store the date of the last revision. Some sample queries and reports are shown below:
• Total sales per month by territory by type of policy
• Total sales per quarter by territory by type of policy
• Total sales per month by agent by type of policy
• Total sales per month by agent by zip code
• Total face value of policies by month of effective date
• Total face value of policies by month of effective date by agent
• Total face value of policies by quarter of effective date
• Total number of policies in force by agent
• Total number of policies not in force by agent
• Total face value of all policies sold by an individual agent
• Total initial commission paid on all policies to an agent
• Total initial commission paid on policies sold in a given month by agent
• Total commissions earned by month by agent
• Top selling agent by territory by month
Commissions are paid to an agent upon the initial sale of a policy. The InitComm field of the policy table contains the percentage of the face value paid as an initial commission. The Commission field contains a percentage that is paid each month as long as a policy remains active or in force. Each month, commissions are calculated by computing the sum of the commission on each individual policy that is in force for an agent.

I closed your other two threads. I'll let the other members eat you alive for being obnoxious enough to register on our forums just to copy and paste a school assignment across multiple sections. You're so lazy you didn't even bother posting "help me with this".

Classic.


Oh, and if you post this in any other forums I will ban you.

sorry never been on a forum just needed help with my hw thats all i have no idea how to do this

if that's true, then any help we give you will be wasted

just drop out of the course now

once have datasource. it will involve ETL. focus on naming convention because to avoid an error while you do the extraction.

what i can see here, too much play with time dimension. You can create this assignment via using SSAS. SSAS will create for you all the star schema and also the hirearchy if you have it. Depending on your design and what kind of output / report did you looking for.