1. Data Warehouse Bus Matrix
Business Procedure |
Event |
Event Session |
Ticket |
Promotion Outlet Batch |
Customer |
Ticket Sale Transaction |
Ticket Gross saless |
Ten |
Ten |
Ten |
Ten |
Ten |
|
Client Invoice |
Ten |
Ten |
Ten |
Ten |
Ten |
Ten |
2. Star Schema
3. Coarseness
Fact Table Name |
Fact Granularity |
Brief Justification |
Client Invoice |
Client committee for each Batch |
Atomic – The sum of committee received by the client for each Batch |
Ticket Gross saless |
Ticket Transactions for an Event |
Atomic – Transaction informations for each Ticket |
4. Property Hierarchies
Dimension Table Name |
Brief Justification |
Attribute Hierarchies |
Event |
Every Business Procedure have Event inside informations |
Event name, Type Of Event, Client company Name, Client contact Phone No, Venue Name, Street Address, Suburb, Postcode, Venue director, Venue Manager Phone No |
Event Session |
Description of the Session for the type of event when they are conducted |
Event Session Number, Event Date, Session clip, Venue Name, Manger Phone No |
Ticket |
Description for the Transaction inside informations incorporating Date, Time and dealing inside informations |
Event Session, Event day of the month, Session Time, Seat Number, dealing Date Time, payment Type |
Dimension Table Name |
Brief Justification |
Attribute Hierarchies |
Promotion outlet Batch |
Description sing the batches of tickets on which dates they have distributed to the mercantile establishments |
Client Company Name, Business Name, Street Address, Suburb, Postcode, Contact Name, Contact PhoneNo, Outlet Category Name, Category Description |
Ticket Sale Transaction |
Ticket sale inside informations for each Transaction |
Transaction Date Time, Payment type, payment Category, Credit Card type, ticket Sale Transaction No |
Customer |
Detailss of the Customers who purchased tickets Online |
Customer First Name, Customer Last name, Customer Account No, Street Address, Suburb, Postcode, State, Country, Email Address, Contact Phone No |
Transaction Payment Type |
Detailss related to Transaction payment type and class |
Payment class, Credit card type, Credit card name, Payment type Id |
5. Design Features
Design Feature |
Brief Description |
Brief Justification |
Degenerate Dimension |
To recover the Client Invoice No and Client Invoice Date for mercantile establishment committee |
The Commission inside informations are stored in Flat Files, |
Role drama |
Information sing the Event session day of the month and clip |
Type of event that is being conducted by the clients for the session |
6. Business Questions
& A ; middot ; Which ticket publicity mercantile establishments see the most tickets? Does this vary across different months or event types?
Select Client_Invoice_Fact_Table ( Promotion_Outlet_Batch_Id, Quantity sold, Event_Session_Id, Ticket_Sale_Transaction_No )
What ticket batch sizes minimise the likeliness of mercantile establishments holding unsold tickets whilst maximizing net income? Client_Invoice_Fact_Table ( Event_Session_Id, Ticket_Sale_Transaction_No )
& A ; middot ; For which clients have we provided the most event tickets? How much do we do a twelvemonth out of our top 5 clients?
Client_Invoice_Fact_Table ( Event_Session_Id, Ticket_Sale_Transaction_No ) + Ticket_Sales_Fact_table ( Customer_Id )
& A ; middot ; How much committee did we pay to boosters in the last fiscal twelvemonth? How does this comparison to the past 5 old ages?
Client_Invoice_Fact_Table ( Date_distributed, Event_Session_Id )
& A ; middot ; From which online client locations do the more expensive event tickets ( e.g. premium and gold ) sell?
Ans ) Ticket_Sales_Fact_Table ( Customer_Id, Event_Session_Id )