Question 1
(a) Explain the use of facts, dimensions and attributes in a star schema model.
(b) In relation to a fact table, what does the term granularity refer to? What are the implications of implementing either high or low granularity?
Question 2
(a) Why did SAP introduce the extended star schema? Explain why it is reported to be better than the traditional schema model?
(b) What is the difference between a dimension used in a traditional star schema and Saps' extended star schema?
Question 3
(a) What are slow changing dimensions and describe the three methods that can be used to cater for slowly changing dimensions.
Question 4
(a) When designing an extended star schema what are the design considerations for the Fact Table and Dimensions in terms of size? Why?
Question 5
Case Study
Northwind Pty Ltd sells a variety of food products throughout the world. They want to use the reporting features of SAP's Business Information Warehouse to better understand their sales.
In the Kick-off Meeting the Steering Committee provides the SAP Consultants with the necessary information about reporting requirements for the Northwind prototype to be installed:
The prototype should make it possible to analyze the sales as per the specified reports below.
Instructions: You are required to develop an Extended Star Schema that includes a fact and dimension tables in addition to the necessary Master Data in order to solve the problem outlined above.
Report 1 – Sales per customer
Report 2 – Total value of Sales per category by country
Report 3 – Sales for Boston Crab Meat by Country
The structure of the data available is as follows:
Order
|
Order Date
|
Customer
|
Product
|
Unit Price
|
Quantity
|
Category
|
Country
|
Category
Customer City
Customer
|
Customer ID
|
Customer Name
|
Supplier