Data Warehouse and Concepts and Design

The Department of Computer Science and Computer Engineering treats plagiarism very seriously.

When it Is detected, penalties are strictly imposed. Students are referred to the Department of Computer Science and Computer Engineering's Handbook and policy documents with regard to plagiarism and assignment return, and also to the section on 'Academic Misconduct' in the unit outline distributed in the first lecture and available on ALMS/Web. No extensions will be given: Penalties are applied to late assignments (5% of total assignment mark given is deducted per day, accepted up to 5 days after the due date only).If there are circumstances that prevent the assignment being submitted on time, an application for special consideration may be made. See the departmental Student Handbook for details.

Note that delays caused by computer downtime cannot be accepted as a valid reason for a late submission without penalty. Students must plan their work to allow for both scheduled and unscheduled downtime. Submission Guidelines: C] No hard-copy of the assignment Is required. Only electronic copy of your assignment is to be submitted to the ALMS at ACCESSED subject web site.

CLC Do not forget to attach a Statement of Authorship to your assignment, sign and date it. You can zip all files in one for the submission since the system does not accept multiple files. CLC Assignment should be typed, not written/drawn by hand. Use the Word Drawing toolbar (or another tool of your choice) to draw figures in your assignment. Entertainment events Data Warehouse Pure Entertainment Is an Innovative Event Management & Entertainment Agency that showcases world's artists and performers to create exciting entertainment for corporate events and major occasions.Pure Entertainment has a dedicated portfolio of both private and corporate events; it manages all types of entertainment events bespangling in entertainment for weddings, major events, festivals, fashion shows, private parties, nightclub appearances, live music, new launch & more.

Its services include professional consultation, full event planning, advertising, artist booking, and ticketing services. It offers bespoke events throughout Australia. The company also offers sound and lighting hire.By making optimum use of the client's budget the of venues and equipment that may be used/rented for entertainment events. Pure Entertainment Agency has access to international singers, actors, entertainers and liberties in all categories available to book for corporate, private, and public events/ tours. All Pure artists have been hand-selected to provide fresh and unique performances.

The company can efficiently provide accurate information with respect to artist pricing and availability.The advertising activity for the events organized by Pure Entertainment: There are different methods or types of advertisements undertaken at Pure Entertainment such as through distribution of flyers or posters an also through other media such as newspapers, television or radio. One of the equines questions that Pure Entertainment wants to address is if an advertisement type is more cost effective than another. The tickets for any performance are sold online via the pure website, as well as from promotion outlets, such as retail stores, cafes, restaurants, shops, and boutiques.Each event may have several performance sessions, such as a play that runs for several days and performs twice per day during this time.

Ticket prices may vary between sessions and for various seating positions, or unallocated standing room, for certain events. The company has a centralized operational database to capture their day-to-day ticket sales. The promotion outlets must be supplied with batches of tickets. Those tickets are allocated to each outlet and are not available to be sold online.

To avoid unsold tickets, outlets are initially allocated minimal size batches of tickets and periodically request more batches on demand. If a ticket is sold online, the customer's details are recorded. Tickets sold via promotional outlets generally do not record the customer details. The database tables that track these processes are given in Appendix A. These tables capture the basic operations of the company including: 0 Ticket sales 0 Ticket distribution to rumination outlets What these tables do not store are details relating to client invoices and promotion outlet commissions.

These details are currently stored in spreadsheets (flat files). Client invoices are printed and sent to the client in the format shown in Figure 1. At the end of each financial year Pure Entertainment distributes commissions to the promotion outlets based on the number of tickets they have sold. The percentage commission paid to each outlet for each event they have promoted over the past 10 years is stored in a spread sheet.

2 Pure Entertainment Invoice No: Invoice Date: Client Company Name: Client Address:Event/Show venue Ticket sales 269856 03 March 2012 Melbourne Theatre Company 35 Bourne SST, Melbourne, 3001 My Fair Lady Royal Theatre Date Ticket Type Quantity sold 12/02/2012 standard 225 12/02/2012 premium 130 12/02/2012 Gold 42 13/02/2012 standard 242 13/02/2012 premium 156 13/02/2012 Gold 17 14/02/2012 standard 285 14/02/2012 premium 146 14/02/2012 Gold 45 Total commission fee: Figure 1: Sample Client Invoice cornerstone $675. 00 $520. 00 $210. 00 $726. 00 $624. 00 $85.

00 $855. 00 $584. 0 Pure Entertainment wishes to perform detailed analysis of their company's reference and have decided that a simple reporting feature built on top of their operational database will not be adequate. Not only will it compromise the efficiency of their operational database, they also wish to incorporate their flat file data into their analysis. They have decided to commission a data warehouse to assist them in addressing the following business questions: 0 What type of entertainment events are the most popular? Has this always been the case or has there been a shift in recent years?Are there certain times of the year when more events are held? Are certain types of events more common at particular times of the ear? Which ticket promotion outlets sell the most tickets? Does this vary across different months or event types? Who is the most popular performer among those singers, actors, entertainers or celebrities in past 5 years (more ticket sales)? What ticket batch sizes minimize the likelihood of outlets having unsold tickets whilst maximizing profit? What outlets sell more of the different ticket types? For which clients have we provided the most event tickets? How much do we make a year out of our top 5 clients? What ticket types most often sell to full capacity? What are the best ratios of the efferent ticket types for sustained profit mastication? Which venue sections, rows, and groups of seat numbers sell the quickest and to full capacity? How can the ticket types be allocated to venue sections, rows, and groups of seat numbers, such that sustained profit is maximizes? How much commission did we pay to promotion outlets in the last financial year? How does this compare to the past 5 years? What is the main location of those people (customers) purchasing tickets online? What event types are they attending? What ticket types are they purchasing?Pure Entertainment has also decided they need the data warehouse to support data analysis by dados groupings. Your Task: (100 marks total) Your task is to design a data warehouse for Pure Entertainment using Star Schema Modeling.

Your design needs to encompass the following steps: (I) (it) First construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Marts. (10 marks) 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. 40 marks) Create the following table with a row for each fact table in our design, indicating the granularity of each fact and a brief Justification for choosing that granularity. (12 marks). Fact table name Fact granularity Fact table type Brief Justification Create the following table with a row for each dimension table in your design, giving a brief Justification for choosing that dimension, and indicating any attribute hierarchies that exist within the dimension.

(12 marks).Dimension table name Brief justification Attribute hierarchies Create the following 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, generate dimensions, role playing dimensions, Junk dimensions, outriggers, intermissions, 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 (16 marks).Design feature Brief description Brief Justification 4 Identify which fields from your facts/dimensions are required to answer each of the business questions listed below.

(10 marks) o 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? O For which clients have we provided the most event tickets? How much do we make a year out of our top 5 clients? O How much commission did we pay to promoters in the last financial year?How does this compare to the past 5 years? O From which online customer locations do the more expensive event tickets (egg. Premium and gold) sell? Lecturer's Notes: 0 0 0 It is not enough to Just draw the shapes of the facts and dimensions in the data warehouse/ data marts. Each fact/dimension should be impolitely defined, with keys and facts/attributes clearly described; All information from the given business problem should be recognizable in your final data warehouse; Your final data warehouse should be able to answer all business questions listed.If one question cannot be answered using your proposed solution, it means the solution is not completely correct and you have to rethink it; 5 Appendix A: Pure Entertainment Operational Database Tables Table: Event This table eventuate appendectomies type Event client (FAKE) venue (PC) Attribute Description This is the operational database's primary key for the Event table. The name of the event.

A description of the event. The type of the event (Play, Musical Theatre, Opera, Rock Concert, Comedy) The foreign key that links the event to the client company organizing the event.This is the key of the venue. Table: Eventfulness This table holds data that describes each performance session for the entertainment event. Attribute Name ventilation's (PC) bumptiousness's eventuate sometime venue (PC) Attribute Description Primary key Event performance session number Date of the performance. May be multiples for the same performance.

Time of the performance session. May be multiples for the same performance. This is the key of the venue. Table: Performers This table holds names of possible performers to Pure Entertainment' event, that is the event Performers.Attribute Name Performed (PC) Performance Performer Type Prearrangement's Counterespionage Satisfactoriness Considerateness's suburb postcode country Attribute Description This is the operational database's primary key for the Performers table. The name of the Performers' company.

Singers, actors, entertainers or celebrities etc. The name of the Performer 's agent contact. The contact phone number of the agent's contact The fax number of the client's contact. The street number, street name, and street type (SST, Rd, etc) of the company's address.The suburb component of the company's address.

The postcode component of the company's address. The name of the Performer 's agent contact. Table: Client This table holds data relating to Pure Entertainment' clients, that is the event organizers. Attribute Name client (PC) kleptomaniac's stewardesses suburb postcode kleptomaniac's clientContactPhoneNo classifications Attribute Description This is the operational database's primary key for the Client table. The name of the client's company. The street number, street name, and street yep (SST, Rd, etc) of the company's address.

The suburb component of the company's address. The postcode component of the company's address. The name of the client's contact. The contact phone number of the client's contact The fax number of the client's contact. Table: Venue This table holds data relating to the venues where the entertainment events are held. Attribute Name Attribute Description 6 venue (PC) eventuate stewardesses suburb postcode appendicitis Vandenberg monographers venue.

The street number, street name, and street type (SST, Rd, etc) of the venue's address. The suburb component of the venue's address.The postcode component of the venue's address. The number of people the venue can hold.

The name of the manager of the venue. The phone number of the venue manager. Table: Seat This table holds data relating to each seat in a venue. Not all venues will have allocated seating.

Attribute Name sternum (PC) venue (PC) ventilation Attribute Description This is the seat number (a combination of the row and number). This is the venue to which the seat belongs. Note: different venues may have the same seat numbers, so venue is part of the primary key.The section (egg. A, B, C .

.. ) of the venue where the seat is located Table: Tickets This table holds data relating to the ticket types for seats (or whole rows or sections) in a venue that sometimes attract higher priced tickets. Attribute Name deceptively (PC) decontaminate deconstructionists Attribute Description Primary key for ticket type Short name for the ticket type, egg. Standard, premium, gold, standing room A description of the ticket type Table: EventSessionTicketPrice This table holds the event session ticket price for each ticket type.

Attribute Name ventilation's (PC) deceptively (PC) ticketing Attribute Description Foreign key for the Eventfulness table Foreign key for ticket type The individual ticket price for a particular ticket type for an event session Table: Ticket This table holds data relating to each ticket available for sale for an event session Attribute Name disencumber (PC) ventilation's (PC) deceptively (FAKE) sternum (FAKE) promotionOutletBatchlD (FAKE) disenfranchisement's (FAKE) Attribute Description This is the number of the ticket (the ticket number is unique for each event only).This is the event session for which the ticket is for. Foreign key for category of ticket seating This is the number of a seat at the event venue. It is a foreign key linking to the Seat table. Batch ID if the ticket was distributed to a promotion outlet.

Foreign key to the Oversimplification's table This foreign key links to the Declassification's table to show which transaction the ticket was bought in.Table: Oversimplification's This table holds data relating to batches of tickets distributed to promotion outlets Attribute Name promotionOutletBatchlD (PC) event (FAKE) Attribute Description Primary key This is the event for which the ticket batch is 7 providentially (FAKE) distributive Foreign key linking to the Pretentiously table. Date when batch was distributed to the promotion outlet Table: Declassification's This table holds data relating to each transaction for ticket sales.Attribute Name disenfranchisement's (PC) transcendentalism customer's (FAKE) fundamentally (FAKE) Attribute Description This is the transaction number for the sale.

This is the date and time that the transaction took place This is the id of the customer who bought the ticket (only valid for Online purchases - otherwise null). It is a foreign key linking to the Customer table. This foreign key links to Transcendentalist's. Table: Customer This table holds data relating to those people who buy tickets online room Pure Entertainment .

Attribute Name accoutrement's (PC) stationmasters customers Name stewardesses suburb postcode state country mainlanders connectedness Attribute Description This is the operational database's primary key for the Customer table. The first name of the customer. The last name of the customer. The street number, street name, and street type (SST, Rd, etc) of the customer's address.

The suburb component of the customer's address. The postcode component of the customer's address. The state component of the customer's address. The country component of the customer's address. The customer's email address.The customer's contact phone number.

Table: Pretentiously This table holds data relating to the outlets that sell tickets. Attribute Name providentially (PC) businessman stewardesses suburb postcode contaminate connectedness contacting promotionOutletCategorylD (FAKE) Attribute Description This is the operational database's primary key for the table. The name of the promoter's business. The street number, street name, and street type (SST, Rd, etc) of the promoter's address.

The suburb component of the promoter's address. The postcode component of the promoter's address. The name of the romper's contact.