Search in the document preview
DB 430- Data Warehousing and Data Mining
Problem 1: After leaving , Mr. Darya Khan established a small product distribution company. Because the business is growing fast, Mr. Darya Khan recognizes that it is time to manage the vast information pool to help guide the accelerating growth. Mr. Darya Khan, who is familiar with spreadsheet software, currently employs a small sales force of four people. He asks you to develop a data warehouse application prototype that will enable him to study sales figures by year, region, salesperson, and product. (This prototype is to be used as the basis for a future data warehouse database.)
Using the data supplied in the A3_P1.xls file, complete the following seven problems:
a. Identify the appropriate fact table components. b. Identify the appropriate dimension tables. c. Draw astar schema diagram for this data warehouse. d. Identify the attributes for the dimension tables that will be required to solve this
problem. e. Using a Microsoft Excel spreadsheet, generate a pivot table to show the sales by
product and by region. The end user must be able to specify the display of sales for any given year.
f. Using part e as your base, add a second pivot table to show the sales by salesperson and by region. The end user must be able to specify sales for a given year or for all years and for a given product or for all products.
g. Create a 3-D bar graph to show sales by salesperson, by product, and by region.
The university computer lab's in-charge keeps track of lab usage, measured by the number of students using the lab. This particular function is important for budgeting purposes. The computer lab in-charge assigns you the task of developing a data warehouse in which to keep track of the lab usage statistics. The main requirements for this database are to: Show the total number of users by different time periods. Show usage numbers by time period, by major, and by student classification. Compare usage for different majors and different semesters.
Usethe A3_P2.mdb database, which includes the following tables: USELOG contains the student lab access data. STUDENT is a dimension table containing student data.
Given the three bulleted requirements and using the A3_P2.mdb data, complete Problems a-g.
a) Define the main facts to be analyzed. (Hint: These facts become the source for the
design of the fact table.) b) Define and describe the appropriate dimensions. (Hint: These dimensions become
the source for the design of the dimension tables.) c) Draw the lab usage star schema, using the fact and dimension structures you
defined in Parts a and b. d) Define the attributes for each of the dimensions in Part b. e) Recommend the appropriate attribute hierarchies. f) Implement your data warehouse design, using the star schema you created in
Problem l c and the attributes you defined in Part d. g) Create the reports that will meet the requirements listed in this problem's
Mr. Samandar Khan, the inventory manager for a marketing research company is interested in studying the use of supplies within the different company departments. He has heard that his friend, Mr. Darya Khan, has developed a small spreadsheet based data warehouse model that he uses to analyze sales data. He is interested in developing a small data warehouse model like his friend so he can analyze orders by department and by product. He will use Microsoft Access as the data warehouse DBMS and Microsoft Excel as the analysis tool. (Use A3_P3.mdb as data for this exercise)
a) Develop the order star schema. b) Identify the appropriate dimensions attributes. c) Identify the attribute hierarchies required to support the model. d) Develop a crosstab report (in Microsoft Access), using a 3-D bar graph to show
orders by product and by department.