Consider the following dimensions, dimension attributes, and dimension sizes for

Question

Consider the following dimensions, dimension attributes, and dimension sizes for Farm Coop-Insurance. Member(member_ID, Name, Address). On average, there are two members for each policy and item covered by the policy. InsuredItem(Item_ID, Description, Coverage_Type). There is an average of ten covered items per policy. CoopOffice(Office_ID, Address, Manager_name). Each policy is registered and managed by only one office of the cooperative. Policy(Policy_ID, Type). The company has approximately one million policies at the present time. Approximately five percent of these policies experience some change each month. Period(Date_Key, Fiscal_Period). The length of the fiscal period is one month. The decision system reports are supposed to be based on five years of data. Claim(Claim_ID, Claim_Description, Claim_Type). The facts to be recorded for each combination of these dimensions are: Policy_Premium, Deductible, and Monthly_Claim_Total. Design a star schema for this problem. Using the assumptions stated above, estimate the number of rows in the fact table. Estimate the total size of the fact table (in bytes), assuming an average of 5 bytes per field.

Explanation / Answer

Rows in the fact table

5 years

12 months

one million policies

2 members

10 items

So we can

5years * 12 months * one million policies* 2 members* 10 items

The total rows is  1200 million

Total size of the factset table

1200 million * 5 bytes per field * 5 fields= 25* 1200 MB

so size is 30000 MB

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.