Developing a hybrid Power BI solution
Prosys Infotech suggested that the entire solution be built on SQL Server and suggested two potential solution options:
- A hybrid approach which improved and supplemented the current structure.
- 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.