Assignment Tasks - Prototype Data Warehouse & Technology Review Report
a) Use Dimensional Modelling Technique to design a prototype data warehouse schema. Use the data on invoices for Spend over £25,000 in the Department for Business, Innovation and Skills (https://data.gov.uk/dataset/financial-transactions-spend-data-bis) for the Period April 2014 to Mar 2015 inclusive). Negative values in the AMOUNT column represent refunds and as such are valid and must be kept. For refunds, only amounts of £50 or more are valid."
b) Use SSIS to perform ETL on the invoice data to populate the data warehouse, in doing so you should demonstrate the use of SSIS transformations/expressions and SQL where appropriate.
c) Create an OLAP cube from your data warehouse and perform the following analyses:
1. Show the top three Suppliers and the total amount spent on these for the year and far each of the four quarters e.g. April-June, July-September, October-December and January-March. Ensure that any refunds are appropriately deducted in your calculations.
2. Show Expense Types that have a hag year spend amount less than the average half-year Expense Type spend. For each of the half-years April-September and October-March.
3. Show a monthly tap 10 ranking for spend on Expense Areas. For each Expense area indicate the number of places it has moved (up/down the rankings) since the previous month.
d) Design a simple BI dashboard for your data warehouse, reporting on a specific business theme of your choice.
e) Using a minimum of THREE published academic journal papers as reference sources; write a short technology review report (min 1000 - max 1400 words) on the following topic.
"Contemporary and future applications of Security Analytics or similar technologies".
Attachment:- Assignment Files.rar