Task:
Answer the following two questions. Your answer should be neat and easy to read.
1)
Imagine that a bank is building a data mart surrounding “In branch
transactions” using a star schema design. The fact table contains individual
transactions (fact attribute – amount), and dimensions include: branch,
customer, account type, transaction type (deposit, withdrawal), teller, and
time (grain size of seconds). Suppose the bank has 250,000 customers, 30
branches, 7 different account types, 6 different transaction types, 140
tellers, and plans on keeping data for 5 years time. Further suppose that fact
table records will be 28 bytes each. If the average customer makes 3 transactions
per week,
A) estimate the amount of disk storage
needed for the fact table. If you
believe you need to make further assumptions, please ask me.
B) explain how data scarcity makes creating
the data mart practical.
C) Suppose an aggregate is being created for
transactions by state the branch is in, by account type, by transaction type.
What would the aggregate data look like -
What tables will there be? Show hypothetical sample fact table records
to illustrate.
2)
Imagine that a business is building a data mart surrounding sales through
stores. Suppose the company has 1000 stores organized into 20 districts. One
might hope that an aggregate table by product by district would be 50 times
smaller than an aggregate table by product by store. Why is this unlikely to be
true?