Description:
Part 1 - Prototype Data Warehouse
a) Use Dimensional Modelling Technique to design a prototype data warehouse using the data on invoices for spending over £1,000 in the Department for Work and Pensions (https://data.gov.uk/dataset/financial-transactions-data-dwp ) for the Period 2012-2013 (April 2012 to April 2013 inclusive). Negative values in the AMOUNT column represent refunds and as such are valid and must be kept. For refunds, amounts of £250 or more are valid.
b) Use SSIS perform ETL on the invoice data to populate the data warehouse, in doing so you should demonstrate the use of SSIS filters/expressions and SQL where appropriate.
c) Create an OLAP cube from your data warehouse and perform the following analyses:
1. Show the top three Sub-Categories and the total amount spent on these for the year and for each of the four quarters e.g. April-June, July-September, October-December and January-April.
2. Show the top five expense areas and the totals amount spent on these for the year and for each of the two half-years April-September and October-April.
3. Show the four suppliers who refunded the most money and the totals they repaid for the year and for each of the four quarters April-June, July-September, October-December and January-April.
d) On paper, design a simple dashboard for your data warehouse.
This should be accompanied by a 1500-word report which describes/evaluates the creation and use of your data warehouse. Your report should include the following:
i. A description of the source data used.
ii. An explanation of any design decisions you have made.
iii. An explanation of your ETL process, highlighting any particular problems you had to overcome and including examples of any SQL queries you have used.
iv. A relationship diagram or database schema.
v. A description of how the analyses were performed, illustrating the results of the analyses with screenshots.
vi. Excel screenshots of your dashboard design and the reasons for your design choices.
All parts
The report should be appropriately referenced and should include the appropriate tables of contents, tables of figures and tables of tables. Any figures should have titles and identification labels. The document should also be spellchecked and grammar-checked.