Intro

FoodMart is a fictional supermarket chain with a two-year record of daily transactions, with information about products, customers, stores and regions.

The goal is to build an analytical model out of the data using Excel’s Business Intelligence tools.

Phases

The project went through several phases:

• Connecting to raw data stored in csv files using Power Query
• Building a relational data model to enable analysis
• Defining new calculated columns to slice the data and measures to measure performance over time and by other dimensions, using DAX
• Analyzing data with Pivot Tables

Noteworthy aspects

• Querying raw data: transactions were stored in separate csv files, so it was very useful the connect to folder functionality of Power Query
• Data modeling: the dataset was composed by 2 data tables (Transactions and Returns) and 5 dimentional (or lookup) tables. Lookup tables were connected to data tables through 1-to-many relationship and foreing keys were hidden from client tool for safer use of filters
• Analysis: a huge number of measures were created, from basic explicit measures (i.e. Total Transactions and Total Returns) to complex iterators and time intellingence formulas (i.e. Year-To-Date Revenue and Month-over-month performance)
• Here is an example of visual made with a PivotTable to analyze sales performance by product brand, with a slicer to allow user to select only transactions under some thresholds:
•