Part A
You are to construct a star schema for Simplified Automobile Insurance. They already have an operational database from which you will be retrieving the majority of your data. The relevant dimensions, dimension attributes, and dimension sizes are as follows:
Policy: With the following attributes available: PolicyId, PolicyStartDate, PolicyEndDate, PolicyType. The company has approximately 1 million policies at the present time.
InsuredParty: With the following attributes available: InsuredPartyID and InsuredParty FName, InsuredParty LName, InsuredParty DOB, InsuredParty Salary, InsuredParty Position
CoverageItem: With the following attributes available: CoverageItemId, CoverageItemDescription. There is an average of 10 covered items per policy and examples include: comprehensive, collision damage, uninsured motorist, and personal liability
Agent: With the following attributes available: AgentID, AgentName. AgentRank, Agentteritory. Agents sell policies andor additional coverage items to policyholders and There is one agent for each policy and covered item.
Period:Attributes of interest for this time dimension are date, month and quarter.
The goal of your star schema is to capture policy transactions. Examples of these transactions are:
Create policy, alter policy, or cancel policy
Create coverage on covered item, alter coverage, or cancel coverage
The grain of the policy transaction fact table should be one row for each individual policy transaction. The facts to be recorded are: PolicyPremium, Deductible, transaction type, transaction date and effective date.
Create a star schema if you feel that snowflake schema may be more appropriate, feel free to do that
Estimate the number of rows in the fact table, using the assumptions stated previously and the fact that you only expect 5 of your policies to change every month. Keep in mind that your datawarehouse should hold data of the last 5 years.
Estimate the total size of the fact table in bytes, assuming an average of 10 bytes per field.
You decide to add Address information for both the insured party and the agents. Modify your schema to take this into account. Notice that each insured party and agent may have multiple addresses: home address, billing address, e.t.c. Add an address type to capture this information.
Some information of Insured Party and Agents change over time. Also, some Coverage items may become unavailable over time. Modify your design to incorporate slowly changing dimensions. Which type will you use in each dimension?
Notice that some attributes of Insured Party change at a different rate than others salary vs DOB. How can you redesign your schema to avoid repeating the constant information every time the salary or position changes?
Simplified Automobile Insurance Company would like to add a Claims dimension to its star schema. Attributes of Claim are ClaimID, ClaimDescription, and ClaimType. The MonthlyClaimTotal needs to be recorded as a fact. How can you take this into account?
Part B
Part 00
Connect to the database and ExecuteModule10Assignment02scripts.sql
Notice that you have created some tables of the form T that correspond to the tables of the operational database.
You have also created table DateDim, which will be the time dimension in your Datawarehouse.
Part 01
Lets try a query:
For ALL the orders report:
the date an order took place
the id of the products included in the order
the quantity ordered
PRODUCTSTANDARDPRICE
The calculated column: ordersalesprice as PRODUCTSTANDARDPRICE ORDEREDQUANTITY
For those orders that do not have an associated orderline assign a productid 99 and quantity ordered 99, PRODUCTSTANDARDPRICE 0
Part 02
Lets create some tables:
Create a table named ProductDim that holds the products and their product lines, including both ids and names. It should also hold a new column called ProductKey that will be the Primary key.
Insert data into productDim. Do not allow NULL values: substitute possible nulls with Undefined
Hint:
Create a sequence that you will use to populate the ProductKey:
CREATE SEQUENCE productseq START WITH 1;
Write a query that selects the product and productline information from the operational database tables. Within this query you can also use the sequence as:
Select productseq.nextval as productKey, ProductID, ..
From ProductT . ;
Use this query to insert the data as:
Insert INTO productDim
Select . ;
Insert into table ProductDim a new record with:
productid 99, productlineid 99 and Undefined descriptions
Create a table named SalesFact with columns: SalesdateKey, ProductKey, ordersalesprice
Insert data into SalesFact
Hint: Modify the query of Part 01, so that you also select the corresponding DateKey from DateDim instead of orderdate and the ProductKey from ProductDim instead of ProductId.
Be careful, when you are joining the tables you should take into account that sometimes the Product Id is NULL and in these cases you will be using the new Undefined record of ProductDim. Also notice that the aggregation level is per date and per product!
M11
Part 00
In the previous lab assignment you created a mini Star Schema!!
Part 01
1. Modify table ProductDim in order to be able to store historical information according to SCD2.2. Then what will this table look like if we change the finish of the Product with ProductId 20 from walnut to Oak? Implement the update of the table.
Part 02
And now let do some analytics! You now work for the Business Intelligence Department and you are asked to create some reports remember, we will only be using the tables of the star schema
First step is to create a CUBE!
Create a report that contains the sales per product, per month.
Now select only one slice of the cube: the sales per product, per month only for September
Now lets drill!! Report the sales per product, per month, per week Of Year only for September
Part 03 Visualization
And now let do some more analytics! You still work for the Business Intelligence Department and you are asked to create plot reports for the results that you got in part 02. Use the three queries that you created for part 02 and the instructions in the following link to create the charts:
http:www.oracle.comwebfoldertechnetworktutorialsobedbsqldevr40Chart12cChart.htmlLinks to an external site.