Aims:
a) To make a Data Warehouse conceptual design by using Star Schema Modeling.
b) To document all steps throughout the design process.
Submission Guidelines:
a) No hard-copy of the assignment is needed. Only electronic copy of your assignment is to be submitted to the LMS at CSE4DWD subject website.
b) Don’t forget to join a Statement of Authorship to your assignment, sign and date it.
c) You can zip all the files in one for the submission as the system doesn’t accept multiple files.
d) Assignment must be typed, not written or drawn by hand. Utilize the Word Drawing toolbar (or other tool of your preference) to draw figures in your assignment.
Question: Your task is to design a data warehouse for Pure Entertainment by using Star Schema Modeling.
Your design requires encompassing the given steps:
i) First construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Marts.
ii) Design the star schema for any Data Marts you have identified, ensuring your Dimensions are conformed, primary and foreign keys are clearly labeled, and that your attributes are named using verbose textual descriptions.
iii) Create the table with a row for each fact table in your design, indicating the granularity of each fact and a brief justification for selecting that granularity.
iv) Create the table with a row for each dimension table in your design, giving a brief justification for selecting that dimension, and indicating any attribute hierarchies which exist within the dimension.
v) Create the table with a row for each design feature you have used, such as handling of possible null foreign keys, and the inclusion of any fact-less fact tables, degenerate dimensions, role playing dimensions, junk dimensions, outriggers, mini-dimensions, or any other design techniques discussed in the lectures. Provide a brief description of each design feature used (how and where it is used – not the theory behind the concept) and a justification for its use.
vi) Identify which fields from your facts/dimensions are required to answer each of the business questions listed below.
• Which ticket promotion outlets sell the most tickets? Does this vary across different months or event types?
• What ticket batch sizes minimize the likelihood of outlets having unsold tickets whilst maximizing profit?
• For which clients have we provided the most event tickets? How much do we make a year out of our top 5 clients?
• How much commission did we pay to promoters in the last financial year? How does this compare to the past 5 years?
• From which online customer locations do the more expensive event tickets (example: premium and gold) sell?