Project 2 Milestone 3: DW Reporting and Visualization
Nick2020
Figures_P13.32g_P13.3.pdf
Chapter 13: Business Intelligence and Data Warehouses Problems Book Title: Database Systems: Design, Implementation, and Management Printed By: nalini chowala ([email protected]) © 2017 Cengage Learning, Cengage Learning
Chapter Review
Problems
1. The university computer lab's director keeps track of lab usage, as measured
by the number of students using the lab. This function is important for
budgeting purposes. The computer lab director assigns you the task of
developing a data warehouse 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.
Use the Ch13_P1.mdb database, which includes the following tables:
• USELOG contains the student lab access data.
• STUDENT is a dimension table that contains student data.
Given the three preceding requirements, and using the Ch13_P1.mdb data,
complete the following problems:
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 Problems 1a and 1b.
d. Define the attributes for each of the dimensions in Problem 1b.
Page 1 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
e. Recommend the appropriate attribute hierarchies.
f. Implement your data warehouse design, using the star schema you
created in Problem 1c and the attributes you defined in Problem 1d.
g. Create the reports that will meet the requirements listed in this
problem's introduction.
2. Victoria Ephanor manages a small product distribution company. Because the
business is growing fast, she recognizes that it is time to manage the vast
information pool to help guide the accelerating growth. Ephanor, who is
familiar with spreadsheet software, currently employs a sales force of four
people. She asks you to develop a data warehouse application prototype that
will enable her to study sales figures by year, region, salesperson, and
product. (This prototype will be used as the basis for a future data warehouse
database.)
Using the data supplied in the Ch13_P2.xls file, complete the following seven
problems:
a. Identify the appropriate fact table components.
b. Identify the appropriate dimension tables.
c. Draw a star schema diagram for this data warehouse.
d. Identify the attributes for the dimension tables that will be required to
solve this problem.
e. Using Microsoft Excel or any other spreadsheet program that can
produce pivot tables, 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. The sample output is shown in the first pivot
table in Figure P13.2E.
Figure P13.2E
Using a Pivot Table
Page 2 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
f. Using Problem 2e as your base, add a second pivot table (see Figure
P13.2E) 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 3D bar graph to show sales by salesperson, by product, and
by region. (See the sample output in Figure P13.2G.)
Figure P13.2G
3D Bar Graph Showing the Relationships Among Agent,
Product, and Region
Page 3 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
3. David Suker, the inventory manager for a marketing research company, wants
to study the use of supplies within the different company departments. Suker
has heard that his friend, Victoria Ephanor, has developed a spreadsheet-
based data warehouse model that she uses to analyze sales data (see
Problem 2). Suker is interested in developing a data warehouse model like
Ephanor's 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.
a. Develop the order star schema.
b. Identify the appropriate dimension attributes.
c. Identify the attribute hierarchies required to support the model.
d. Develop a crosstab report in Microsoft Access, using a 3D bar graph to
show orders by product and by department. (The sample output is
shown in Figure P13.3.)
Figure P13.3
Crosstab Report: Orders by Product and Department
Page 4 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
4. ROBCOR, whose sample data is contained in the database named
Ch13_P4.mdb, provides “on-demand” aviation charters using a mix of
different aircraft and aircraft types. Because ROBCOR has grown rapidly, its
owner has hired you as its first database manager. The company's database,
developed by an outside consulting team, is already in place to help manage
all company operations. Your first critical assignment is to develop a decision
support system to analyze the charter data. (Review the company's
operations in Problems 24–31 of Chapter 3, The Relational Database Model.)
The charter operations manager wants to be able to analyze charter data
such as cost, hours flown, fuel used, and revenue. She also wants to be able
to drill down by pilot, type of airplane, and time periods.
Given those requirements, complete the following:
a. Create a star schema for the charter data.
b. Define the dimensions and attributes for the charter operation's star
schema.
c. Define the necessary attribute hierarchies.
d. Implement the data warehouse design using the design components
you developed in Problems 4a–4c.
e. Generate the reports to illustrate that your data warehouse meets the
specified information requirements.
Using the data provided in the Ch13-SaleCo-DW database, solve the following
problems. (Hint: In Problems 5–11, use the ROLLUP command.)
5. What is the SQL command to list the total sales by customer and by product,
with subtotals by customer and a grand total for all product sales? Figure
P13.5 shows the abbreviated results of the query.
Figure P13.5
Page 5 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
Problem 5 Abbreviated Result
6. What is the SQL command to list the total sales by customer, month, and
product, with subtotals by customer and by month and a grand total for all
product sales? Figure P13.6 shows the abbreviated results of the query.
Figure P13.6
Problem 6 Abbreviated Result
Page 6 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
7. What is the SQL command to list the total sales by region and customer, with
subtotals by region and a grand total for all sales? Figure P13.7 shows the
result of the query.
Figure P13.7
Problem 7 Result
8. What is the SQL command to list the total sales by month and product
category, with subtotals by month and a grand total for all sales? Figure P13.8
shows the result of the query.
Figure P13.8
Problem 8 Result
Page 7 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
9. What is the SQL command to list the number of product sales (number of
rows) and total sales by month, with subtotals by month and a grand total for
all sales? Figure P13.9 shows the result of the query.
Figure P13.9
Problem 9 Result
10. What is the SQL command to list the number of product sales (number of
rows) and total sales by month and product category, with subtotals by month
and product category and a grand total for all sales? Figure P13.10 shows the
result of the query.
Figure P13.10
Problem 10 Result
Page 8 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
11. What is the SQL command to list the number of product sales (number of
rows) and total sales by month, product category, and product, with subtotals
by month and product category and a grand total for all sales? Figure P13.11
shows the result of the query.
Figure P13.11
Problem 11 Result
Page 9 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
12. Using the answer to Problem 10 as your base, what command would you
need to generate the same output but with subtotals in all columns? (Hint: Use
the CUBE command.) Figure P13.12 shows the result of the query.
Figure P13.12
Problem 12 Result
13. Create your own data analysis and visualization presentation. The purpose of
this project is for you to search for a publicly available data set using the
Internet and create your own presentation using what you have learned in this
chapter.
a. Search for a data set that may interest you and download it. Some
examples of public data sets sources are:
• http://www.data.gov
• http://data.worldbank.org
• http://aws.amazon.com/datasets
• http://usgovxml.com/
• https://data.medicare.gov/
Page 10 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
• http://www.faa.gov/data_research/
b. Use any tool available to you to analyze the data. You can use tools
such as MS Excel Pivot Tables, Pivot Charts, or other free tools, such
as Google Fusion tables, Tableau free trial, IBM Many Eyes, etc.
c. Create a short presentation to explain some of your findings (what the
data sources are, where the data comes from, what the data represents,
etc.)
Note
The visualization in Figure P13.13 was created using a data set
downloaded from one of the public sources listed above. A trial
version of Tableau was used to create the visualizations. This simple
example illustrates the type of quick analysis you can do for this
project.
Figure P13.13
Visualization Example Using Tableau
Source: Tableau
Page 11 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
Chapter 13: Business Intelligence and Data Warehouses Problems Book Title: Database Systems: Design, Implementation, and Management Printed By: nalini chowala ([email protected]) © 2017 Cengage Learning, Cengage Learning
© 2018 Cengage Learning Inc. All rights reserved. No part of this work may by reproduced or used in any form or by any means - graphic, electronic, or mechanical, or in any other manner - without the written permission of the copyright holder.
Page 12 of 12Print Preview
8/26/2018https://ng.cengage.com/static/nbreader/ui/apps/nbreader/print_preview/print_preview.html?
