Leveraging Power BI to develop a data analytics and reporting solution for a Kenyan ICT company

Leveraging Power BI, the data was cleansed and regularized and an option to expand the dataset was also provided in case of potential scaling. Data enrichment has helped create the following reports and dashboards:

• Comparative analysis based on timestamps for monthly, daily, and quarterly data
• Cohort analysis for revenue collected from Single Business Permit (SBP), E-Cess, Parking Pass, and Public Service Vehicles (PSV) passes
• Report generation from revenue dashboards for penalty services as well as daily or long-term permits
• Consistent monitoring to ascertain top-performing revenue services, top-performing agents, and top-performing Kenyan counties, sub-counties, wards, and zones

All the other data processing capabilities and enhancements to the system are detailed below:

Calibration for Gaps in Data

The biggest challenge that was overcome by the team was the unavailability of historical data to build conceptual and logical models. Since forecasting cannot be done without historical data, the team developed a workaround strategy. Using Power BI, various conservative and aggressive scenarios were developed, compared, and contrasted to produce the most appropriate data models for the current data. Continuous testing of the database was carried out to check for data mismatches and mistakes in the various layers of the workflow.

Roll Down Authorizations

Access to various data groups was given on the basis of roll-down authorizations. Authorization was determined by an entity’s ranking in the hierarchical order of operations. Full access was made available only to governors, deputy governors, and high-level executives from the finance ministry. Certain specific users now have access to view county-specific data, while a super admin decides the drill-down levels of access only to emails of the authorized domain.

Scheduled Automation

A well-crafted data flow was implemented for migrating the FinTech provider’s database to the Power BI database. Particular intervals of the Kenyan standard time would trigger the refreshing of each report formulated from the above-mentioned database connection. The standard interval for report generation and updation was set at a 2.5 hours frequency. Based on the filters set by the end-user, the reports generated can be data contextual, taking dynamic data inputs for customized reports.

Service and Revenue Forecasting

The data enrichment team applied upper-bound and lower-bound possibilities to increase the accuracy of service and revenue forecasts. By taking the uncertainty range into consideration the team minimized the processing time and the number of processes involved in forecasting to help with faster parameter searching across the forecasts. Goals and estimates can also be set for exploring more avenues for revenue generation.

High-Level KPIs

Around 45 high-level KPIs were implemented across 10-11 pages of each type of data representation or report. Based on certain feedback from the service provider, different co-related KPIs were clubbed together where needed. This allows a view of the top-performing revenue streams enabling the respective higher-ups to formulate better revenue generation strategies. The system can zero in on agents generating the most revenue and present data for the ward or county they are allocated to.