1. Display a detailed knowledge and systematic understanding of the current trends in data warehousing, business intelligence and data mining.
2. Show a systematic understanding and knowledge of conceptual modelling for data warehouse applications, business intelligence and data mining techniques.
3. Demonstrate a comprehensive knowledge and systematic understanding of essential concepts, principles and tools to build, test and debug a data warehousing and data mining application that provide business intelligence to its users.
• Write a report to evaluate the changes that have taken place since the beginning of data warehousing as a tool to generate competitive advantage via business intelligence.
• Design a small scale data warehouse for a given problem domain through the use of conceptual modelling techniques.
• Produce a small scale prototype Data Warehouse to solve a case study based problem.
Part 1 - Report
Write a 2000-word report that describes a suitable methodology from the literature for the purpose of designing, constructing and testing of a commercial BIDW for a multinational company. You should evaluate the changes that have taken place since the beginning of data warehousing and show how these changes have affected the hardware and software that would be used for such a project over the last 40 years.
Part 2 - Case study
a) Design and build a prototype data warehouse using the data on Spend over £25,000 in the Science and Technology Facilities Council (https://data.gov.uk/dataset/financial-transactions-data-science-and-technology-facilities-council) for the financial year 2012-2013 (April 2012 to March 2013 inclusive).
b) Create an OLAP cube from your data warehouse and perform the following analyses:
• Show the top five expense areas and the total amount spent on these for the year and for each of the four quarters April-June, July-September, October-December and January-March.
• Show the topthreeexpense typesand the totals amount spent on these for the year and for each of the twohalf-years April-September and October-March.
• Show the four suppliers who received the most money and the totals they received for the year and for each of the four quarters April-June, July-September, October-December and January-March.
c) On paper, design a simple dashboard for your data warehouse.
This should be accompanied by a 1000-word report which describes the creation and use of your data warehouse. You should include the following:
• A description of the source data used.
• An explanation of any design decisions you have made.
• Anexplanation of your ETL process, highlighting any particular problems you had to overcome and including examples of any SQL queries you have used.
• A relationship diagram or database schema.
• A description of how the analyses were performed, illustrating the results of the analyses with screenshots.
• Sketches of your dashboard design and the reasons for your design choices.