A pioneering company in India’s fresh produce supply chain is revolutionizing how fruits and vegetables reach rural communities.
The Client
A pioneering company in India’s fresh produce supply chain is revolutionizing how fruits and vegetables reach rural communities.
Challenges Faced by the Client
The organization faced significant challenges due to the generation of vast amounts of data from various sources, which were hindered by data silos. Data from MySQL databases and Google Sheets remained disconnected, complicating integration and analysis. This resulted in a lack of coherent insights essential for tracking and optimizing key operations, such as inventory management and sales performance.
Complex Data Structures:
Many datasets included complex JSON-formatted columns, adding layers of difficulty to data processing. This complexity made it challenging to extract meaningful insights and slowed down decision-making.
Lack of Real-Time Data Access:
Real-time data was inaccessible, limiting the organization’s ability to respond quickly to business needs. Reporting was done during non-peak hours, often resulting in outdated data when it was most needed for operational decisions.
Manual Data Flattening:
The team faced significant challenges with manual data flattening in PostgreSQL, which was done on a weekly basis. This process consumed valuable time and led to inefficiencies in data storage, as it was slow and prone to errors.
Inefficient Reporting and Analysis:
Reporting was delayed, with data analysis occurring only after a significant lag (the next day). This delay in insights prevented the organization from utilizing the data to its full potential, impacting its ability to make quick business decisions, especially during peak hours.
Real-Time Tracking for Fraud Detection and Efficiency: Enhanced monitoring of driver performance, speed, inventory discrepancies, and route deviations reduced fraud and optimized supply chain operations.
Revenue and Customer Experience Improvements: Real-time dashboards tracked revenue, operational metrics, and delivery progress, ensuring faster deliveries, increased transparency, and improved customer satisfaction.
Solutions
UBTI proposed migrating the client’s data infrastructure to Microsoft Fabric, creating a unified platform for real-time analytics and seamless data integration. Key steps included:
- Real-Time Data Integration
- Ingested streaming data into Microsoft Fabric’s KQL database every 5 minutes during peak hours for real-time analytics.
- Integrated daily and historical data through the Medallion Architecture into the Lakehouse during nightly jobs.
- Provided a comprehensive view of operations for informed decision-making.
- Implemented detailed logging for data loads and sent alerts via email for job failures.
- Automated Data Flattening
- Automated the previously manual and time-consuming process of flattening data in PostgreSQL within Fabric.
- In KQL, flattening was handled automatically, and in the Lakehouse, we used PySpark notebooks to flatten the data.
- Improved efficiency and resolved storage issues using a scalable and optimized format.
- Real-Time Reporting & Dashboards
Built real-time dashboards refreshed every 5 minutes, enabling operational teams to make quick, data-driven decisions, enhancing business agility, and eliminating reporting delays.
Some of the key reports include:
- Terminal Status & Performance:
- Monitor terminal status, active users, operational health, and downtime.
- Highlight terminals missing Full Product Activation (FPA).
- Speed & Tracking:
- Measured average performance speed and displayed the latest data.
- Analyzed tracking accuracy and operational issues.
- Jingle Metrics:
- Tracked total jingle playtime and counted plays per terminal.
- Revenue & Bhumi Blessings:
- Monitored revenue and track received, verified, and new Bhumi blessings.
- Operational Efficiency:
- Measure Operational Time Ratio (OTR) and monitor inventory discrepancies.
- Alerts and Notifications
- Integrated Data Activator to send real-time alerts via Microsoft Teams and Outlook.
- Proactive notifications allowed teams to address inventory and sales issues instantly.
- External Data Sharing with GraphQL API
- Implemented role-based access controls to secure data.
- Developed a GraphQL API to securely share real-time and historical data with external systems.
Enabled flexible data querying and integration while maintaining control over data access.
Solutions Benefits
- Unified Data Management: Fabric provided a centralized data platform, eliminating data silos. This unification of data from various sources allowed for more accurate analysis and reporting.
- Real-Time Data Access and Analytics: With real-time data processing, the organization could access up-to-date information at any time, especially during peak operational hours. The ability to analyze data as it arrived enabled a swift response to business needs.
- Faster Decision-Making: The ability to analyze data in real-time helped operational teams make quicker and more informed decisions. They could now monitor inventory and sales performance dynamically, rather than relying on outdated reports from the previous day.
- Reduced Time and Effort on Data Management: The automated flattening of data eliminated time-consuming manual processes and storage inefficiencies. This allowed the team to focus more on analyzing the data rather than preparing it.
- Enhanced Operational Efficiency: With the Tracking Report and real-time dashboards, teams gained end-to-end visibility into their operations. This transparency allowed them to identify inefficiencies and optimize processes quickly, with real-time alerts ensuring immediate attention to any operational issues.
- Scalability and Flexibility: The scalable architecture of Microsoft Fabric enabled the organization to handle increased data volumes as the business grew, providing the flexibility to adapt to evolving business needs.
- External data Sharing using GraphQL APIs: By leveraging GraphQL APIs, the application team could access flattened data directly for reporting, eliminating the need for additional development efforts.
This successful implementation highlights Microsoft Fabric’s capability to drive business transformation and demonstrates UBTI’s proficiency in delivering outstanding data solutions.
