1. Requirements: Identify current and future decision-making needs and information requirements of the organisation. Determine the number of dimensional models required (with justification).
2. SQL: Compile SQL statements, based on given information and ER diagrams, to address five decision making needs identified above. Use of advanced SQL syntax is required as this task aims to assess your knowledge of SQL.
3. Design: Follow Kimball’s dimension design process to create the identified models. Revise each model by applying the fact table and dimension table techniques. Provide a data dictionary (a description of each table in each model outlining the purpose, attributes and data types).
4. Demonstration: Demonstrate how each dimensional model can be used to respond to current and future business questions and decision-making scenarios. Articulate how the dimensional model can lead to effective decisions.
A professionally written report on the dimensional design activities undertaken for Olympus. This can be compiled as a series of topics of the abovementioned ‘to do’ list or formatted as a design document.
The report should be compiled in Microsoft Word, font size 11.
Report content should not exceed 8-10 pages (including diagrams). Diagrams have to be in the main body as it is a design document. If the number of pages are insufficient, you may include the data dictionary as an appendix.
You are free to make any assumptions with justification. Include these in the report, where applicable. Make realistic assumptions on any information (schema or business requirements) that may be missing in the above description.