Dimensional Modelling for Fry's Food Store

Company Background:

Fry's Food & Drug Stores, Inc. is a chain of supermarkets that was founded in the year 1954 in California and was later expanded in Arizona state in USA. The store was bought by Kroger in year 1998 and expanded in other states in USA. The Fry's store is a multi-department store that offers grocery, pharmacy and other general products such as electronics, toys and home goods. Fry's store is currently in 100+ locations and has 18,000+ employees. Each store sells 10,000+ products of various categories such as bakery, dairy, frozen food, pharmacy, liquor and many more. The stores have checkout systems that are managed by Fry's employee or self checkout system. Fry's store also allow online ordering system so that people can order online the required items and collect them later. The store offers membership cards and provides discount to customers. The store also conduct promotional offers on particular dates and distribute discount coupons.

Performance Metrics:

Below are some of the performance metrics that a CEO or manager of Fry's stores will be interested for tracking.

1. Sales value for a particular product across time.

2. Sales value and number for all products at a location across time.

3. Sales value of a particular product/s at a location/region.

4. Sales value of a particular product/s during a day of week.

5. Sales number of products at a particular location.

6. Sales number of products on a promotional offer day.

These performance metrics will help the CEO understand how a particular store is performing at a particular location, how many products are sold every day, which is the highest selling product on regular days and promotional offer day and sales revenue across time.

Dimensional Model:

To get the above mentioned metrics we need to store the transaction data at its most granular layer. In this case, we need to build a transaction snapshot dimension model. The transaction model will help us capture which customer bought a 'x' quantities of a particular product at a particular price at a particular location. This dimension model can be used to capture a snapshot of the sales at the end of each month providing details of the sales amount and quantity of various products across multiple locations. The analyst can also derive the products and sales amount across multiple locations for a particular customer or average the sales amount during promotional offers across time.

Below is a sample dimensional model snapshot for retail store.

References:
1. https://en.wikipedia.org/wiki/Fry%27s_Food_and_Drug
2. https://www.frysfood.com/
3. https://retaildatawarehouse.wordpress.com/2011/05/18/dimensional-data-model/
4. http://learndatamodeling.com/blog/dimensional-data-modeling/