#2 - Dimensional model for FedEx Express

This is the second post of my blog for MIS 587 Business Intelligence course in the Eller College of Management. In this blog, I am going to talk about FedEx corporation and the air cargo transportation industry. I will be explaining what kind of metrics the CEO of FedEx will be interested in capturing and thus describe the dimensional model that suits best for their business model.

FedEx Corporation is an American global courier delivery services company headquartered in Memphis, Tennessee. The name "FedEx" is a syllabic abbreviation of the name of the company's original air division, Federal Express, which was used from 1973 until 2000. The company has expanded from its original focus on ground and air-based services to include home delivery options and a retail division. Internationally, the company primarily competes with DHL Express, UPS and TNT Express. In April 2015, the company reached an agreement to purchase its Dutch rival TNT Express, subject to regulatory and shareholder approval. The acquisition is expected to be completed in the first half of 2016.

FedEx is organized into many operating units but for the purpose of this exercise, I will be concentrating on its original service, the FedEx Express. FedEx is the the original overnight courier service of FedEx Corporation, providing next day air service within the United States and time-definite international service. FedEx Express operates one of the largest civil aircraft fleets in the world and the largest fleet of wide bodied civil aircraft; it also carries more freight than any other airline.

Understanding the air cargo transportation business

Before we design the dimensional model for FedEx, we need to understand about the air cargo transportation business process. The above diagram shows the major actors of the specific industry. The shipper ships the shipment through FedEx by approaching an agent who does the booking and generates an Air Waybill (AWB). The air waybill is the most important document issued by a carrier either directly or through its authorized agent. It is a non-negotiable transport document. It covers transport of cargo from airport to airport. By accepting a shipment an IATA cargo agent is acting on behalf of the carrier whose air waybill is issued. The shipment is carried by the freight forwarders to the airports where export customs take over. The Ground Handling Agent is responsible for transporting the shipment in and out of the flight. On reaching the destination, freight forwarders carry the shipment and deliver it to the consignee. 

Metrics of interest to track

The most important metrics to track for this particular business is the shipped goods status and location. Every time the goods changes its location, the new location must be entered into the system for effective tracking. In order to measure the net revenue generated in each AWB, all receivables and payables need to be calculated. FedEx receives the shipment cost from the shipper directly or the IATA agent. They need to pay 5% commission fees to the IATA agent and another 3% to GHA. Provided that there are no interline OALs (Other Airlines) for the particular shipment, deducting the commission charges from the gross revenue will give the net revenue. If there are interline airlines involved in the shipment, then the revenue gets prorated based on the distance traveled by each airline. For simplicity, we consider only one FedEx flight present in the entire shipment process. The CEO might be interested in knowing the daily, weekly, bi-weekly and monthly net revenue generation for FedEx. Such companies have their pre-defined accounting periods and this decides the date range that needs to be considered for revenue accounting. Invoicing and payments are also handled and thus the CEO might also be interested in participant outstanding reports and distribution of dunning letters.

Use of Dimensional Model

Dimensional model can be used to streamline this process easily. The Accumulating Snapshot type of dimensional model is the most appropriate type here since there are pre-determined milestones in the entire air cargo transportation process. This type of dimensional model approach is needed since the row of the fact table needs to be updated every time the location of the goods change to track its current location. The grain definitely is each AWB and the granularity can be tracked for every movement of the goods and also calculate gross and net revenue. The possible dimensions are the different actors: shipper, IATA agent, GHA, freight forwarders, flight and consignee along with the Date dimension.

The different facts that need to be tracked are current location, chargeable weight, rate, weight charges, valuation charges, Gross Revenue, Host Revenue, Net Revenue, total receivable amount and total payable amount to form a very simple dimensional model. The data should correspond to each AWB and can be aggregated or rolled-up according to any date to get aggregated reports for senior management. The dimensional model has been designed using a star schema as shown below.

References -

1. Wikipedia
2. Unisys Logistics Management System
3. FedEx Official SIte