'Overview This assignment provides a hands-on application of the concepts related to creating and populating a data warehouse. It involves a series of activities starting with thinking from a user/management perspective to determine the most important questions in a business situation, through design of a data warehouse, writing SQL to extract data from existing operational databases and transforming it into the data warehouse format, to the SQL that uses the data warehouse to answer the questions proposed. A complete data warehouse with a snowflake schema should be designed to address key management objectives in a common business situation, e.g., hotel industry. Two operational databases for that domain are evaluated and a series of SQL procedures written to extract, transform and load data from the two operational databases into the data warehouse. SQL needed to address management’s key objectives using the fact and dimension tables of the data warehouse are created along with a description of the results suitable for presentation to management. A detailed project report should be submitted on the due date. This project is to be done individually. This may require additional reading and research. This project will require more than 24 hours of work time to complete. To complete assignment, you should complete the following activities: 1) Review the project description carefully. 2) Read Chapter 13 Business Intelligence and Data Warehouses 3) Review the slides and recorded lectures for Chapter 13 4) Complete the activities listed below under submission requirements. Assignment Resources 1) Recorded lecture on Chapter 13; 2) Recorded lecture slides on Chapters 13; 3) The business situation description provided below; 4) ER diagrams for two operational databases for the business situation to review: Corp1ERD-Revised-Ayyaz.jpgView in a new window , Corp2ERD-Revised-Ayyaz.jpgView in a new window 5) Data dictionaries for two operational databases for the business situation to review: Corp1Data Dictionary.xlsPreview the documentView in a new window , Corp2Data Dictionary Revised.xlsPreview the documentView in a new window 6) Slides from the DW project Web Ex: DataWarehouseProjectOverview.pptxPreview the documentView in a new window 7) IBM Website Description of Fact Table Grain: http://www.ibm.com/support/knowledgecenter/SS9UM9_9.1.0/com.ibm.datatools.dimensional.ui.doc/topics/c_dm_design_cycle_2_idgrain.html (Links to an external site.)Links to an external site. Business Situation Description: You work for a large corporation that has just purchased 2 hotel and resort corporations each consisting of over 100 hotels. Each Corporation operates a custom database. You are provided the data dictionary and ER diagrams for the two operational databases. Management would like you to design a data warehouse that allows them to achieve the most competitive advantage possible. (Note: The databases you will evaluate come from student groups in another class responding to the Hokie Resort problem. This problem also is provided for your review.) Submission Requirements When completed, please upload your documents under the “Assignments†tab in Canvas. Please use a document naming convention that allows us to determine your name. EXAMPLE: “LastName_FirstName_Project2.docx†. 1 Review the submission Hokie Resort situation description to understand the problem domain(see Business Situation Description below) 2 Write the three most important questions that management must answer to achieve a competitive advantage in the hotel market. Submit the questions and a brief (1-2 pages) explanation/justification of why these are the most important questions. (10 points) 3 Design a Data Warehouse Star or Snowflake schema that is sufficient for addressing these questions. Submit an ER Model of your data warehouse schema. (10 points) See this example of a schema from the Hokie Hospital problem: HospitalDWSchema.vsdxView in a new window Example as pdf: Visio-HospitalDWSchemav2.pdfPreview the documentView in a new window 4 Implement your Data Warehouse Star or Snowflake schema in MySQL or other DBMS. Submit MySQL of other DDL that implements your data warehouse schema. (10 points) 5 Populate MySQL or other DB with sufficient rows to demonstrate your Data Warehouse This will require you to create rows for all dimension (including time) and fact tables in your Data Warehouse. You must insert sufficient rows in the DW to be able to execute the ROLLUP queries for the final step in this assignment. (10 points) This script is from the textbook and loads a sample data warehouse: DW-DBINIT.SQLPreview the documentView in a new window 6 Analyze the ER diagram and data dictionary from both of the operational databases to determine if the two operational hotel databases have the data needed for your data warehouse. For each DB, create a mapping that shows the tables from that DB that are used to create rows in your data warehouse tables. For each data warehouse table, describe how the operational data is aggregated to create a row in the table. Submit your mapping and aggregation summary in the following format. DatawarehouseTable Operational DB Table Aggregation/Sum PatientDim Corp1: Patient No aggregation, each row is an instance in the DW. PatientDim Corp2: Customer No aggregation, each row is an instance in the DW. MediationsFact Corp1: Prescriptions Count and average amount of drug given are created. MediationsFact Corp2: Drugs Provided Count and average amount of drug given are created. (Note: If an operational database does not contain the data needed for your data warehouse design, then propose revisions to the existing tables in the DB or define additional tables to be populated in the DB so that it will contain the data needed for your data warehouse). (5 points) 7 Write the SQL necessary to extract and process the data from the two operational databases so that it will be suitable for your elements of your data warehouse. The mapping you made in question 4 should help in this process. This requires writing SQL procedures that include SELECT statements from the operational DBs and INSERT INTO the data warehouse tables (Note: you do not have to make the procedures work). You should extract and load data for two of your dimension tables and one of your fact tables from each operational DB. In addition, you should show the population of the time dimension and include it on your fact table rows. Pay attention to the correct grouping and aggregation necessary to transform the operational data into the form needed for your data warehouse. An example of the procedure file is provided from a previous semester using the Hokie Hospital problem in this file: . Your task is to make a similar procedure that will extract the data from the operational databases into your data warehouse design. Submit your procedures with a .sql file extension so that we can review them within Notepad++. While any logical organization of procedures is acceptable (provide a brief justification/rationale for others), the preferred approach is to create one SQL procedure for each dimension table and one for the fact table from your data warehouse. These procedures should access both operational DBs and be as simple as possible. (15 points) Find an example of the procedures needed here: Dimension table: Q5_SampleProcedure_Hokie_Hospital_DimensionV2.sqlPreview the documentView in a new window Fact table: Q5_SampleProcedure_Hokie_Hospital_FactV2.sqlPreview the documentView in a new window 8 Write the SQL that will be needed to answer the three most important questions using your data warehouse. This requires writing 3 SQL statements/procedures. (10 points) These sql statements should use the ROLLUP features of SQL for processing dimension data. This script is from the textbook has sample rollup and cube SQL: DW-OLAP-SQL.txtPreview the documentView in a new window * You should download and install Notepad++ or other editor that helps with SQL syntax. Business Situation Description: You work for a large corporation, Hokie Resort Hotels, that has just purchased 2 hotel and resort corporations each consisting of over 100 hotels. Each Corporation operates a custom database. You are provided the data dictionary and ER diagrams for the two operational databases. Management would like you to design a data warehouse that allows them to achieve the most competitive advantage possible from these acquisitions. The two operational databases were designed to meet the following situational requirements. Hokie Resort Hotels has grown rapidly over the past several years with the acquisition of Club Med chain and several very high luxury hotels worldwide. They have been adding more rooms, more extension wings on buildings, and whole new buildings creating large complexes of facilities serving a wide variety of needs. They desperately need support to keep track of rooms, facilities, reservations, guests, the usage of allied services, and billings. Such support would enable them to better serve their customers and guests and make more efficient use of their facilities and staff. The success of Hokie Resort Hotels is due in large measure to the team effort felt by all employees. They are collectively motivated by a desire to best serve the customer. However, as the operation grows this objective becomes threatened. The staff needs rapid access to complete, and current information to adequately serve the needs of their guests and hosts, and fairly respond to their demands. There are several different kinds of rooms in a hotel complex. Most are set up for sleeping with one or two double beds (which may be regular size, extra long, queen size, or king size). Any given room could have two different types of beds. Some sleeping rooms have a minimal amount of extra space, some have an extra large open area with chairs and tables for meeting, etc., or for extra rollaway beds. All sleeping rooms have toilet and bath facilities, telephone, television, closets, and drawers. All sleeping rooms are designated as smoking or nonsmoking. A sleeping room is rated according to the number of sleeping guests it can accommodate (assuming adults, with adjustments made for small children). A suite consists of a sleeping room with an additional, separated room for meeting or working. However, there is only a single hallway access door to the suite. If there are separate hallway access doors to adjacent rooms (whether sleeping rooms or meeting-only rooms), the rooms are considered two separate rooms and can be allocated separately. Some rooms are meeting rooms only without sleeping facilities. Some meeting rooms have toilet facilities and some do not. Meeting rooms are rated according to their seating capacity assuming the guests would be seated around tables. Since an outside courtyard or a pool patio could also be the site of an event, they are treated as meeting rooms. A sleeping room may be adjacent to at most one meeting room. A meeting room may have one or two adjacent sleeping rooms. In addition, some sleeping rooms are adjacent to another sleeping room with a private-access door between them. Some meeting rooms are very large, such as a ballroom seating up to 10,000 persons. Some have large movable walls to divide them into multiple smaller rooms. All movable walls have a door. Each of the smaller rooms has a separate designation along with an indication of which rooms it is adjacent to. Two rooms are considered adjacent if there is a door between them. Many of the rooms can serve multiple functions. For example, a suite could be assigned as a sleeping room or as a meeting room only. In a pinch, a meeting room could be used as a sleeping room (with rollaway beds), but only if it has toilet facilities. Some rooms also have a bed which folds up into the wall, turning it into a meeting room. A room with permanent beds cannot be assigned as a meeting room. There are many more rooms in a hotel complex than is possible for any one person to remember. Moreover, at any given time a room may be undergoing renovation or reconstruction, or not be made up and cleaned. The computer must keep track of all room relationships and availabilities. Ideally and eventually, the computer system should enable the staff to see a graphical representation of the layout of a room and its facilities, and to zoom out to include adjacent rooms. Each room has its own base rental rate per day. Sleeping rooms are allocated on a daily basis from 4 P.M. until 12 noon the next day. Earlier or later extensions are granted depending on when housekeeping gets the room ready or is available to get the room ready. Longer extensions entail a surcharge (a flat fee) on the daily rate. Meeting rooms are scheduled on an hourly basis throughout the day and evening. The usage times are generally designated as: breakfast, morning, lunch, afternoon, supper, evening, and sometimes night. Each noneating usage is charged at the half-day rate with discounts for multiple noneating usages. One noneating usage slot is granted free of charge for each paid eating usage of the meeting room. A paid bar is considered an “eating†usage of the room. Meeting room charges may also be reduced or waived based on the number of guests staying in a hotel as a result of the event being held in the meeting room. Designating the location of the various facilities follows some pattern. A hotel complex consists of multiple buildings, each with multiple floors and multiple wings. Room numbers are assigned uniquely only within wings which have a variety of alphanumeric designations. Wings are designated uniquely within buildings, which are also named. A room number consists of one or two digits designating the floor or level, followed by two digits designating the room number on the floor. Wings differ by proximity to indoor or outdoor swimming pools, proximity to parking garage, and handicapped access. Often a whole floor of a wing is designated as nonsmoking. These factors are often important to parties making reservations. Customers of a hotel include: guests, who stay overnight in sleeping rooms, and hosts, organizations or individuals who host meetings or meals in meeting rooms. It is sometimes necessary to distinguish the billing party who is responsible for making payment, from the party using the facilities. For a meeting room there is only one party responsible for making payment. However, if multiple guests stay in a sleeping room, the billing can be split up any way the guests agree, as long as there is some responsible party. Furthermore, a guest may switch from one room to another during the visit, and still receive a single (composite) bill for the visit. In some cases of a split billing, the room may be separately charged to a billing party, while any phone calls and other usages are charged to the guest occupying the sleeping room. Much of the information to be retained in the computer system surrounds events. An event is a meeting or other gathering of persons requiring the use of one or more rooms. Often guests are at a hotel to attend an event and are to be so affiliated. Each event will have a host who is either a guest or a billed party (or both). An event has a duration, and several facilities will be used in conjunction with the event. A scheduled event may also record estimated attendance and an estimated number of guests. Reservations are made up to two years in advance (or more for major functions) for meeting and sleeping rooms. When making a reservation, the customers express their needs and desires in terms of: bed type and size, number of guests, location, proximities, smoking or nonsmoking, etc. Specific room assignments may not be made until the date draws close in time. (This is necessary because of the unpredictability of specific room availability due to breakdown, repair, and renovation. Even then, last minute changes are necessary due to unexpected breakdowns and the desire to accommodate changes in guest plans). Rooms are assigned based on a unique, sequential number assigned to each wing. Reservations are filled with the lowest numbered wing(s) and the lowest available room number(s) that meets the guests’ stated requirements. In making or changing reservations, the hotel staff must have accurate and current information on the actual and projected usage of all facilities. Advance deposits are sometimes required depending on the qualifications of the customer. The qualifications are a function of their past history with the hotel chains, their cooperativeness, their flexibility in negotiating the usage of facilities, their promptness in making payment, etc. In an effort to be responsive to customer needs and demands, hotel staff is given considerable latitude in making decisions and assessing charges. This must be done in the light of the total relationship the customer has with the hotel chain – including past visits or events, and the complete spectrum or usage on the current visit. For example, when a customer checks into one of the public restaurants or the health club with a couple of guests, it is desirable for the staff to know that they are responsible for renting the ballroom for three days and hosting 200 guests who are paying to stay at the Hokie Hotel. There must be a responsible party to be billed for all usage of facilities and services in a hotel. Charged services include sleeping room usage, meeting room usage, meals (or drinks) in meeting rooms or delivered to sleeping rooms or served in a public restaurant, telephone calls, general extra charges for room service (delivery and set up), or business services (photocopying, computer time or equipment rental, printing, fax), charges from retail shops in a complex or for services (such as a masseuse in the health club or racquetball court fees). Each charged service is recorded on a transaction transmittal form (and should eventually be entered directly into the computer system). All charges must be recorded in a timely fashion so that an up-to-date rendering of a guest’s total bill is always available. For example, if a guest ate in the restaurant and made a phone call just before leaving, those charges should be reflected on the final billing when they check out just a few minutes later. Billed charges must be recorded with sufficient detailed information to enable the responsible party to verify the charges. The hotel also records expected or authorized charges, and ordered services in an effort to anticipate the level of accumulated charges and avoid any surprises. This information may be gathered as part of the reservations process. The billed party is generally a hotel guest staying in one of the sleeping rooms. A billed party may also be an outsider, a local host living at home, or some organization. It is possible for a billed party to be responsible for the charges of multiple guests. Even if the responsible party is an organization, it is still necessary to have the name of an individual who is acting on behalf of (under the authority of) the organization. In making reservations and during actual usage, it is important to keep track of who is assigned where and who is using what facilities. At all times it is important for the hotel staff to know how to contact a guest or host at the hotel, at least to the extent possible with all the information which is available to or voluntarily given to the hotel. This means keeping track of the sleeping room they are currently assigned to (if any), the organizational event (meeting, conference, sponsor, etc.) they are attending (if any), their own organizational affiliation, that is, the organization which they work for. Each guest receives a plastic card with a PIN. As they move about within the hotel, they have the option of running the card through readers indicating their current location. The card is used to gain access to sleeping and meeting rooms. As such, the hotel always knows when someone uses the card to gain access to a room. Readers are placed in various facilities such as every meeting room, restaurants, and health clubs. In this way, the guest may, at their discretion, use the card to inform the computer of their current location. Each card reader has two slots coming (entering) and going (leaving the facility). Cleaning and repair personnel also have magnetic cards to record when a particular room becomes available. When it is important to guests that they can be contacted, the hotel can be kept informed through several facilities. Guests can leave outgoing messages on their telephone (as with a home answering machine) informing callers and the hotel of their whereabouts or when they will be available. At their option, a guest may request that information concerning their whereabouts be kept confidential – not to be revealed to any callers. Of course, hotel staff can always take a message and relay it to the guest, who can then decide whether or not to respond to the call. Assessment Your submission document will be checked for correctness and completeness. Connecting Assignments This assignment provides a complete perspective on the design of data warehouse. It uses ER and SQL concepts learned in prior assignments. '