Prosys Infotech Private Limited, a Microsoft Gold Certified Data Analytics Partner based in Pune, India, is an IT services company focused on providing custom management information system (MIS) solutions using Microsoft BI Technologies. An MIS is a computerized database of financial information organized and programmed in such a way that it produces regular reports on operations for every level of management within a company. Such solutions enable customers to make better informed decisions at the strategic, tactical, and operational levels. Prosys Infotech has developed such solutions in a wide range of verticals including manufacturing, services, telecom, engineering, healthcare, hospitality, shipping, consumer durables, retail, and others.

Prosys Infotech has been a Microsoft partner for the last 10 years with a focus on data analytics and cloud. Prosys Infotech uses the entire Microsoft BI stack including SQL Server, SSIS, SSAS-Tabular, SSRS, Power Query/PowerBI, Azure, and advanced analytics services.

Prosys Infotech helps customers execute turnkey business analytics projects by providing consulting services on BI content, solution design, solution development, implementation, training and post development support, and subsequent maintenance.

Needing to analyze large swaths of data

KFC Africa had previously invested in a few BI initiatives that did not yield the desired analytical edge they were looking to pull from their data. The company wanted to analyze sales data from over 900 restaurants, but its existing BI solutions relied on manual uploads of summarized data submitted by individual stores. This process was extremely tedious and error prone, creating a significant dependency on the person uploading the data. It also made it difficult to track which stores had submitted their data on a regular basis.

The solutions also failed to provide KFC with the granular analysis it was looking for—analysis of which menu items were sold during which hours of the day—as the earlier solution was built on summaries due to the sheer size of the data and the manual process used to collect it. At present, this data is approximately 1TB, with 1.4 billion rows accumulated over the last 2 years, and 2 million rows added each day. To meet its needs, KFC Africa required a modern BI solution to help expand its business and harness this data.

Developing a hybrid Power BI solution

Prosys Infotech suggested that the entire solution be built on SQL Server and suggested two potential solution options:

1. A hybrid approach which improved and supplemented the current structure.
2. A new solution built on Azure.

Since KFC had already built a solution to get data to their local SQL Server, they opted for the hybrid approach.

The solution took transactional data (using SSIS) from the point-of-sale vendors for KFC Africa’s 900 plus restaurants and stored it in a relational data warehouse. This data was then modeled in a SSAS-Tabular cube wherein all the KPIs and metrics were calculated.

Due to the large volumes of data, the SQL database as well as the SSAS database were partitioned for performance and optimize the processing time required.

Power BI was built into the solution as a visualization layer, providing a number of reports that gave KFC Africa insights by geography, franchisee owner, menu, day part, channel of sale, etc. These Power BI Reports were published online and—along with the sourcing of the data itself—are fully automated.

Prosys Infotech built the solution to generate reports to help ensure erroneous or missing data could be identified and corrected. This feature has significantly increased the accuracy of KFC’s data, giving the organization the confidence needed to make strategic decisions based on data and the insights the solution glean.

The solution provided insights into key metrics

The vision and direction of the project was initially put forth by the CEO of KFC Africa. Once the intent was stated, Prosys Infotech discussed the details of the underlying data and reporting strategy with several members of the team, consisting of senior team members including the CEO, CFO, CIO, CMO and other senior team members from sales, marketing, restaurant development, restaurant excellence, operations, finance, and analytics.

Prosys Infotech built several intermediate reports that helped the KFC team reconcile its daily data pulls with the weekly reports submitted by its franchisees. The ability to ingest a huge volume of data on a daily basis and simply identify missing and erroneous data proved to be a huge game changer for the organization.

On the reporting front, the team was able to map the daily data pull to the KFC Calendar—a significant challenge that required the team to create a custom calendar for KFC.

Prosys Infotech then created several reports using both standard and custom visuals. These were shown to the senior stakeholders of the company, and the reports underwent several iterations before being finalized. The ease with which the reports could be changed, new visuals could be added, and new insights generated in Power BI interactively and at the click of a button, represented a significant step for the organization.

These reports provided insights on key growth metrics by menu item, by hour of the day, region, franchisee, and channel. This helped KFC make key decisions regarding which campaigns should be run for specific menu items, when those campaigns should be run, and where best to target them. Also, the drill-down capabilities in the reports informed key decisions on how to improve restaurant performance and account for growth.

Using Prosys Infotech’s solution, KFC Africa was able to get a 360-degree view of store performance in terms of growth, operations, customer satisfaction, and audit scores.

What’s more, KFC was able to map the sales of stores by key operational metrics to discover correlations between sales and operations in an effort to improve in both areas.

For Prosys Infotech, this solution afforded them a degree of visibility into other solutions used by KFC, and provided further opportunities to collaborate with the customer on additional solutions spanning across finance, operations, HR, and the supply chain.