Dynamic Reporting System for a Leading Retail Franchise Business

Location: Philadelphia

Introduction

A leading retail franchise business with over 300 locations across the US faced challenges in analyzing their operational data. Operating in a highly competitive market, the franchisee required a robust system to efficiently manage and visualize data across various aspects of their business, including sales, inventory, staff management, and customer interactions. To address these needs while maintaining confidentiality, a comprehensive reporting system was developed using AWS and Power BI to enhance data accessibility and decision-making processes.

Problem Statement:

The franchise struggled with analyzing complex operational data from a non-relational database (DynamoDB) with more than 30 interconnected tables. Manual data checks were labor-intensive and error-prone. The existing CRM reports were insufficient, prompting the need for a more effective solution to provide accurate, reliable insights into various operational areas.

Solution Summary

To overcome these challenges, a new reporting system was designed using AWS and Power BI, involving the following steps:

Data Extraction and Loading: A Python script was developed to extract data from DynamoDB and load it into an S3 bucket on AWS, ensuring reliable and scalable data storage.
Data Transfer to Power BI: AWS S3 was used to stage the data, and Power BI connectors were employed to transfer data from S3 to Power BI, facilitating smooth integration and visualization.
Data Transformation and Relationships: Robust joins and relationships among 7-8 tables per report were created, resulting in over 30 comprehensive and accurate reports.
Dynamic Reporting and Visualization: Dynamic, real-time Power BI reports were implemented with advanced visualizations, replacing the old CRM reports and enhancing data accessibility.
Dynamic Data Refresh: A data refresh mechanism was set up using AWS Lambda and AWS Glue to update reports three times daily, ensuring data remained current and accurate.
User-Friendly Design: Reports were designed to be intuitive and presentation-ready, enabling stakeholders to make informed decisions efficiently.

Challenges

Complex JSON Format: DynamoDB's unique JSON format required conversion and standardization through a custom Python script.
Non-Relational Database Complexity: Extracting and organizing data from complex JSON structures involved significant data manipulation.
Handling Duplicate Records: Special logic was developed to manage and remove duplicate records in the JSON data, ensuring report accuracy.
Data Integration: Integrating data from multiple tables while maintaining consistency and accuracy was a challenge.
Performance Optimization: Queries and data processing were optimized to ensure reports performed efficiently with large datasets.
User Training and Adoption: Training was provided to help users transition from the old CRM reports to the new Power BI system.

Benefits

Embedded Report Privacy: Embedded reports with role-based access ensured data security and privacy.
User-Friendly Interface: An intuitive Power BI design improved navigation and data analysis.
Improved Data Visualization: Advanced visualizations made complex data easier to interpret and understand.
Enhanced Data Accessibility: Centralized reports reduced the need for manual checks and streamlined decision-making.
Real-Time Insights: Dynamic updates provided timely information for informed decisions.
Accurate Reporting: Reliable reports, achieved through robust data joins, ensured trustworthy information for strategic decisions.
Efficient Data Handling: Simplified data processing and reduced manual errors.
Optimized Performance: Reports were optimized to load quickly and efficiently, even with large data volumes.
Comprehensive Coverage: The suite of reports provided a complete view of key metrics, offering a thorough understanding of various operational areas.
Scalability: Leveraging AWS services like S3, Lambda, and Glue ensured the system could scale with the business’s growth and handle increasing data volumes effectively.

Conclusion

The development and implementation of the Power BI reporting system, supported by AWS infrastructure, addressed the limitations of the previous CRM reports and the complexities of DynamoDB. By integrating data extraction, transformation, and visualization processes with AWS and Power BI, we delivered over 30 accurate and user-friendly reports. These reports offered dynamic, real-time insights and advanced visualizations, significantly improving data accessibility and decision-making. Despite challenges such as complex JSON formats and data duplication, the new system greatly enhanced performance and provided a comprehensive view of operational metrics, all while maintaining client confidentiality.