Implementation Of A Data Warehouse And a Star Schema Diagram

I have uploaded files regarding the task but below is a description of the core tasks to be completed. It must be completed at a university level as it is for a univeristy assignment. Specify 2 suitable DW reports (queries). A star schema for the Data Warehouse to support your 2 queries specifying:- • the tables and key and non-key attributes for the DW, • attribute definitions. List the anomalies, differences and issues between the 3 data sources that will affect you taking the data from them and loading it into your Data Warehouse. For each case you identify, make a recommendation to overcome it. (This could involve data cleansing, transformation). Using QSEE, forward engineer the star schema database you have designed. Create and run a script to create the data warehouse tables initially, this script will be run once only, it sets up the tables. Include the QSEE generated script(s) as part of your upload. Implementation of the ETL (Extract, Transform, Load) programs: Your oracle account is effectively the ‘data-staging’ area for the exercise. In this assignment you are going to create the data warehouse schema, populate it with data from the data sources (xls spreadsheets). Include a short ‘overview document’ describing your implementation and referencing the scripts you have written (.sql) and tested (evidenced by spool(.lst) files). Include these files as part of your upload (there is no need to print them out). Implementation involves: 1. ETL script 1 – Initial Insert Load the xls files into Oracle – describe how you did this. Create a script to populate the data warehouse from the provided data sources, this script will be run once only. 2. ETL script 2 – On going maintenance Create a script to insert into the DW the new data that has accumulated in the 3 data sources for 2009/10. 3. Create an interface to provide the reports for your Data Warehouse. (For example using SQL or apex tools). Data Mining Describe a scenario where data mining could be used in the assignment case study, ie. a Data Warehouse for recorded crimes. Use examples to describe your scenario, you may wish to discuss data that hasn’t been provided by this case study but would be useful in terms of data mining. (word limit: 300 words).