Dimensional Modeling

The TU Hotel chain is a small chain with properties throughout Pennsylvania, Arizona, Florida, and New York. They have a central database to store and track guest reservations. In 2008, they put cafes in many of their hotels, called “Café in the Hotel.” They have an order-tracking system that relays customer orders from the wait staff to the kitchen.

TU Hotels would like to use the data they have collected to better understand the performance of their hotels and cafes. They also have access to a database from the online review site “HotelComplainer.com.”

Your task is to design two dimensional data marts using data from those three databases. You will plan the star schema for each data mart by choosing the dimensions, facts, and attributes from the data contained in those databases. The relational schemas of those databases are on the following page.

NOTE: You will not actually come up with answers to these questions since you have no data. You’ll just be designing the star schemas that can address these questions about the business.

The questions to be addressed by each data mart are listed in the table below. You should include only the data fields you need in each data mart, but the data marts can contain some of the same data (i.e., the same fields can appear in both data marts).

To complete the exercise, you’ll need to perform the following steps:

1) Identify the main business event for each data mart. This will be the fact. Ask yourself, “What is the basic business event that generates the performance metric (revenue)?”

2) Identify the attributes associated with the fact. Ask yourself, “How is the business event quantified (measured)?”

3) Identify the dimensions and their attributes. Ask yourself, “What data characterizes the various aspects of that business event?”

4) Either sketch the star schema or make a list of the dimensions and the fact and their attributes. You can use the examples in the slides as a guide.