Learnings

Dimension Modelling for McDonald's

McDonalds, the largest hamburger fast food joint in the world that aims to deliver hamburgers, chicken, French fries, breakfast meals, soft drinks, and deserts at a valued price.

Currently, McDonalds operates as a franchise, affiliation, or a food service restaurant. Restaurants operate as a company, independent entrepreneurs, or by an affiliate. McDonalds has over 36,000 locations serving approximately 69 million customers in over 100 countries per day.

McDonald’s key strategies are to strengthen its alliance with its company, franchisees, and suppliers. By doing so, McDonalds will continue to create new innovations to bring customer satisfaction. Overall, McDonalds aims to continue its modern burger brand by continuing to deliver high-quality food at a world-class experience. Hence, people around the world will continue to say, “I’m Lovin’ It”.

When it comes to tracking performance the main things that the CEO f McDonald's would want to look at is which store sells how much of each food item and the revenue that is generated from these sales. This will help them determine the demand in each area and help them in making decisions like where they should open their next store. Also in terms of Food items as we all know McDonald's customizes their menu according to states and countries they have specials that are served only in that particular region. It is important for them to see if these specials are good enough to generate revenue or not.

Hence to achieve answers to these questions it is a good business decision for them to invest in creating a dimensional model to systematically store data. Now let’s discuss a step by step approach to creating a Dimension Model for McDonald’s

Step 1: Identifying the Business Process

Each company has various business processes but in this scenario we are trying to determine the quantity of each food item sold on a particular day and the revenue that was made from each food item.

Step 2: Identifying Grain

What is grain?

The level at which one wants to see the data at. Hence if one ones to see a company’s sales figures on a daily basis then the granularity is daily but in the case where monthly stats are required then the granularity is monthly.

In this case the grain is:

For a particular store located in a particular city, the number of food items sold on a particular date and the amount generated by selling each of these items.

Step 3: Identifying Dimensions

Dimensions are objects or things, things that are being spoken about. We will start by creating a separate table for each dimension. In this scenario the Dimensions that I have identified are Menus, Food_Items, Employee, Employee Position, Location and the most important Date.

Step 4: Identifying Measures

Measures are values that are estimated in a process. They are quantifiable and mostly numeric. They are stored in a fact table. In this scenario the Cost of the total meal and Quantity are the measures that we will be recording in the Fact Table. In this scenario we will be using a Transaction Fact Table.

"A row in a transaction fact table corresponds to a measurement event at a point in space and time.Transaction fact tables may be dense or sparse because rows exist only if measurements take place.The measured numeric facts must be consistent with the transaction grain" - as explained by Kimball and Ross