Assignment 1

Submission Requirements

• All answers must be computer generated (including text and diagrams).
• The hand-in version must be ordered correctly and stapled in the top left corner.
• The hand-in version must include a header page indicating: student name, student number, user id, course number and assignment number.

1. Warmup Question (20 marks)

• Required Setup:

(1) In Windows Explorer, go to the location "C:\win32app\OLAP Services\Bin". Double click on the file "msolap.msc". Please wait for a couple of seconds.

 (2) When the OLAP manager is ready for use, read through the OLAP Manager Tutorial. Only focus on the OLAP Manager Basic Tutorial.
 
 

• Tasks: Follow the steps illustrated in the basic tutorial to create a data cube called Sales. In this process, make the following changes.

(1) In the step of Setting up Your System Data Source Connection, instead of using Tutorial, choose FoodMart or foodmart if it is already existing there. If not, add a new data source called FoodMart or foodmart and select the datebase file from "W:\OLAP_Sample\FootMart.mdb". Please don't use other names. Or you could not connect to the OLAP server.

(2) When you are required to supply the new database name, instead of using Tutorial, use your account id, such as jsmith.

(3) When you are required to set the data source connection, select FoodMart or foodmart instead of Tutorial.

(4) In the step of Adding Measures to the Cube, select the fact table Sales_fact_1998 instead of Sales_fact_1997.

(5) Add yourself as well as zzhang in the step Adding Roles to the Cube. The Role name is called Manager.

• Submission:

(1) Understand how to obtain the metadata about a cube. Submit the metadata for the data cube Sales.

 (2) Understand how to obtain the schema diagram for a cube. Submit the schema diagram for the data cube Sales.
 
 

2. Further Question (60 marks)

• Tasks: Under the same data base name created in Question 1, i.e., your account id, create another data cube called Inventory according to the following specifications.

(1) The fact table is inventory_fact_1998. The measures are: Units Ordered, Units Shipped, Warehouse Sales, Warehouse Cost.

 (2) The dimensions are:

• w_Product using multiple tables product and product_class. The levels for the dimension are all, Product Category, Product Subcategory, Product Name,
• Warehouse using single table Warehouse. The levels for the dimension are all, Warehouse Country, Warehouse State Province, Warehouse City,
• w_Time using single table time_by_day. The levels for the dimension are all, Year, Quarter, Month, Day,
• w_Store using single table store. The levels for the dimension are all, Store Country, Store State, Store City.
(3) The roles for the cube should be yourself and zzhang. The Role name is also called Manager. You can borrow it from cube Sales you created in Question 1.

 (4) The type of the data cube is MOLAP.

• Submission:

(1) Submit the metadata for the data cube Inventory just created.

 (2) Submit the schema diagram for the data cube Inventory.
 
 

Browse the Data Cube Inventory (20 marks)

• Tasks:

 (1) Understand the steps in the basic tutorial on how to browse the data in a data cube.

 (2) Find Warehouse Sales, Warehouse Cost, Units Shipped and Units Ordered for product fruit in the stores in BC and the warehouses in BC in the third quarter in 1998.

 (3) Find Warehouse Sales, Warehouse Cost, Units Shipped and Units Ordered for product Bologna (a kind of meat) in the stores across USA and the warehouses in Seattle in the second quarter in 1998.

 (4) Find Warehouse Sales, Warehouse Cost, Units Shipped and Units Ordered for product Moms Beef Bologna in the stores across USA and the warehouses in Seattle in the second quarter in 1998.

 (5) Find Warehouse Sales and Units Shipped for product meat in the stores across USA and the warehouses in state WA in the second quarter in 1998.



• Submission:

 (1) Submit for each query the steps you took in order to get the result you wanted.

 (2) Submit for each query the result you obtained.

3. Written Question (20 marks)

1. A popular data warehouse implementation is to construct a multi-dimensional database which is also called a data cube. Unfortunately, this may often generate a huge, but very sparse multi-dimensional matrix.

Present an example to show why.

Design an implementation method which can overcome this sparse matrix problem elegantly. Note that you need to explain your data structures in detail and discuss the space needed, how to retrieve data from your structure, and how to handle incremental data updates.

2. In data warehouse technology, a multiple dimensional view can be implemented either by a multi-dimensional database technique (MOLAP) or by a relational database technique (ROLAP).

Briefly describe each implementation technique.

Explain how the following function is implemented by each technique.

1. Generate a data warehouse (including aggregation).

Roll-up.

Drill-down.

Incremental updating.

Which implementation technique (MOLAP or ROLAP) do you prefer, and why?