Dimension Modelling for McDonald's

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

This Diagram was created using Visio

I would also like to to add some Fun Facts about McDonald's

Taken from Business Insider

References:

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/transaction-fact-table/

http://www.businessinsider.com/is-chipotle-or-mcdonalds-better-2015-8

Business Intelligence & 

Analysis Products Scan & Evaluation 

Before analyzing the tools I would like to give a simple explanation of as to what Business Intelligence is. It is one of those buzz words that everyone uses but not everyone clearly understands it.

The best definition that I found on the internet is:

Computer-based techniques used in spotting, digging-out, and analyzing 'hard' business data, such as sales revenue by products or departments or associated costs and incomes. Objectives of a BI exercise include (1) understanding of a firm's internal and external strengths and weaknesses, (2) understanding of the relationship between different data for better decision making, (3) detection of opportunities for innovation, and (4) cost reduction and optimal deployment of resources.

1. The steps to achieve Business Intelligence

The best way to learn a new word would be to use it in a sentence hence, use this term in a sentence:

The man used his business intelligence to build up his business's profits and stocks while also bringing down other business competition. 
You could try too!

So getting back to tools, I am going to analyze 5 Tools based on the following criteria’s. Before going into the comparison let me give a brief description of each of these criteria’s:

1. Usability & visualization: This criteria necessitates the ease of use of the tool. Mainly describes how a user can navigate through the tool to get his/her work done. The questions that need to be answered to check whether the tool is easy to use or not are is it easy to learn? Does it support Mobile intelligence? Types of graphs and visualization that can be used? For a tool to be good enough a good score for this criteria is a necessity.
2. Scalability: It is extremely important in terms of identifying how much data the tool can handle. In this world of Big Data this is a very essential factor in picking the right tool.
3. Data Integration: This is essential for a BI tool with to be able to integrate data from different sources. There is a whole lot of unstructured data which needs to be structured and then be used to make better decisions. Hence this criteria needs to have a good score.
4. Cost Effectiveness: This is one of the major drivers behind the decision of which product could be used. Depending on the type of business and the budget the organization has set in spending on these tools. The cost score will be based on the basic and enterprise version of the tool.
5. Customer Support: The score of this criteria is important to identify the level with which the product provides customer service. This could be in the form of answering customer questions, documentation of the product and the methods to use it, quality support and the rate at which the company provides services. In this era of internet it also includes blogs and online support.
6. Infrastructure & Architecture: This category measures how well the tool supports IT infrastructure. Mainly which operating system and which server platforms are supported. It also takes care of important factors like re-usability, caching, zero-footprint, load balancing, fail-over and In-Memory techniques.
7. Predictive Analysis & Data Mining: This criteria examines if and how data and text mining is supported and to what degree. Data mining is widely used to be able to predict behavior of customers, vendors, web visitors whereas text mining is mainly being used to mine Twitter messages.
8. Search & Alerting: This functionality is necessary in order to make searches on large data sets and metadata along with the ability of a tool to alert in case of anomalies or different trends.

 The tools recommended should provide following features for it to be acceptable:

∞    Reporting (KPIs, metrics)

∞    Ad hoc querying

∞    OLAP (cubes, slice & dice, drilling)

∞    Dashboards/scorecards

∞    Operational/real-time BI

∞    Automated monitoring/alerting

   

    These are some well- known tools used in the industry today:

2. Tools available in the market 

    Out of these I will be further analyzing the following tools:

3. Weighted Matrix for the tools

4. Graphical Representation of the Matrix

It is known for its intuitiveness and it makes it very easy regardless of technological know-how of a person. The platform is compared to Excel in terms of its ease of use and it very but is very feature rich. It creates sharable dashboards, interactive reporting and has flexible features and scalability. This is the tool that is used by most of the industries as it is the smartest tool of all. Exactly why it’s ranked first.

           

This product is very useful for companies with major BI needs. The enterprise edition integrates Oracle’s other tools and products. Hence this product can be used for any visualization, reporting or information manipulation. This is ranked second as it understands BI needs of an organization at a higher level.

   

     Fun Fact: The University of Arizona uses this tool

         

This product falls under IBM’s contribution to BI. It is a web based solution to company analytics.  It can be used for any business. It provides the capabilities of creating Dashboards, reports, detailed analysis and scorecarding to allow automation of business metrics to help companies get a better insight into their data.

           

This product is known for easily converting unstructured big data to structured data. This tool is very easy to use and can be used by anyone with beginner level database skills in an organization to make instant visualizations and reports. It also provides location based analysis which is great asset to e-commerce websites.

This is also known for its user friendliness that connects the gap between techy BI tools and traditional productivity apps, It has a clean interface and is capable of most of the features a BI tool should have with visually appealing dashboards in easy to understand format.

    References:

    www.businessdictionary.com/

    www.docurated.com/

    www.softwareinsider.com/

    www.pcmag.com/

    www.google.com/