All questions in this assessment refer to the application of business intelligence and data warehousing principles to the scenarios outlined below. You should select the correct scenario matching the last digit of your student number and answer the questions in the context of that scenario.
Choose this scenario if the last digit of your student number is 8 or 9
The organization is a local council. They have a single central head office and local offices in each major town. They have the following sources of data:
a) A CRM system with details of tenants, their contact details, the property they rent, amounts billed, payments made and outstanding amounts.
b) A property tax / business rates billing system containing details of the owner, type of property, property tax band, amounts billed, payments made and amounts outstanding.
c) A planning database, containing details of planning applications, applicants and status of the application.
d) A domestic maintenance system that records details of tenants’ maintenance requests, status of the request, staff assigned, materials used and time taken.
e) Emails and contact messages sent through the council’s website.
The council has a large budget for data warehousing and business intelligence and would like to spend as much time as is necessary to implement the most appropriate BI solution. Their current BI solution is based around independent data marts owned by each business unit.
1. Data Warehouse Design and Management (35 marks)
a) Select a suitable data warehousing architecture for the business described in the scenario. Give your rationale for this choice of architecture. [7 marks]
b) Suggest and describe two additional data sources not listed in the description that you think would add value to the business intelligence extracted to the data warehouse. Ensure you state how the BI would be enhanced by the inclusion of these sources. [10 marks]
c) Design a star, snowflake or fact-constellation schema for the new data warehouse. Your design should encompass all sources listed in the description and the two sources identified in b) above. The design should also include all columns and key relationships. Ensure you state your rationale for the choice of schema design and state the granularity of the data in the proposed data warehouse. [12 marks]
d) Briefly discuss the project management issues that are likely to arise during the project to implement the data warehouse and describe the steps that can be taken to tackle them. [6 marks]
2. Extract, Transform and Load (40 marks)
Suggest an ETL process for the data warehouse schema you designed in question 2. Structure your answer in accordance with the conceptual ETL approach described by Vassiliadis et al. (2001), namely:
a) Identification and reasoning over relevant data. Here you should briefly describe the source data in terms of data types, cardinality and optionality, and state any assumptions you have made about the data sources. [7 marks]
b) Extraction of these data from their respective sources. You should also discuss your approach to staging the data here. [8 marks]
c) Transforming the data for the data warehouse. Here you should describe how you would conform and integrate the source data, noting any particularly difficult aspects of the process. [9 marks]