Background:

For this assignment you will be using the clearwater traders scenario $-$ by the way. you can tell it is an e$-$business from the fields in the customer entity $-$ a userid and password. See Brightspace for the clearwatersql.

The situation is that you are a data analyst recently hired by Clearvater. They have approached you to construct the structure of a Data warehouse$/$Data Marts that can be used to quickly and accurately answer their what$-$if questions about how to better run their business. You have been provided with design documentation as CREATE statement code in clearwatersq$].$ You must now provide tested scripts for constructing the structure of the SSOTs for identified stakeholder$($s$),$ and any necessary $5$cript$(s)$ for initial migration and$/$or subsequent refiresh of the data viewable from the SSOTs.

Assignment Four Specific Scenario

Who: The Sales manager of Clearwater Traders

What: A report on sales by product, including gross revenue and quantity.

Why: The sales manager will use informatios from the report to make the following monthly and quarterly decisions:

Staffing at various sales locations

Product lines to discontinue$/$promote

Regional$/$outlet incentives to sales staff

Regionalyoutlet incentives to customers

Recommendations to procurement regarding colars, quantity, sales forecasts

Perform the following analysis work:

Analyze the given information including the structure and data content of the ckanwater traders database. Speculate and storyboard the report$|$s$|$ the sales manager would like to see.

Create the Clearwater database in its own resaurce schema. Consider the database your anly source of data. Design a single first normal form "staging area" for the necessary measurables and dimensions to satisfy all of the reports:

Create a resource schema in your dev machine's database instance.

Create the target staging area in the resaurce schema:

Create a user with the privileges necessary to perform an SQL statement that will cagy the necessary processed data from the Clearwater bables located in the Resourtet Schema named Clearwater to the staging area.

Design and test the SQL necessary to move the data.

Assignment Requirements statement:

As a result of the six steps of anslysis above, you must supply:

The storyboards and your justification of them based on case information and your awn experience:

A testing plan to werify $($a$)$ that the SQL works and $($b$)$ that only the ETL wer can perform the migration; and

An explanation and justification to satisfy the following question: Would you create the staging area ss a materialied view $($CREATE TABLE tablename as subquery$)$ or as a dynamie view $($CREATL VIEW viewname as subquery$).$