This milestone uses the implemented database that is the output of Milestone 2 to produce reports and visualizations implemented with SQL Rollup queries. This is the final step in Project 2 and provides a hands-on application of the concepts related to using a data warehouse for addressing users' questions. It involves writing the SQL queries needed to retrieve the data and thinking from a user/management perspective to determine the best visualizations for presenting this data.
The complete SQL needed to address management’s key objectives using the fact and dimension tables of the data warehouse are created along with a description of the results suitable for presentation to management.
8) Write the SQL that will be needed to answer the three most important questions using yourdata warehouse. This requires writing 3 SQL statements.
For each question, show the query and sample results of executing the query using your implemented database from Milestone 2.
These sql statements should use the ROLLUP/CUBE features of SQL for processing dimension data.
This site has a summary of the ROLLUP and CUBE statements that is useful: https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68003/rollup_c.htm
(https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68003/rollup_c.htm)
This script is from the textbook has sample rollup and cube SQL: DW-OLAP-SQL.txt
9) Create a visualization for your first management question.
(Note: Figures P13.3.2G (page 615) and P13.3 (page 616) show single fact tables with multiple dimensions.
This assignment provides an opportunity to demonstrate the value of your data warehouse from Project 2 Milestones 1 and 2. It involves exporting data from your data warehouse to Excel (or Tableau or other graphing package of your choice), then using the graphing tools to create simple visualizations of your data. Simple visualizations are excellent for communicating effectively with users and managers.
.
10) Create a visualization for your second management question. (Note: it must be different thanthe first and ideally use different fact or dimension tables).
11) Create a third visualization, it must be different than the first two and should use different factor dimension tables.
