Datalake, CFO reports and ML insights and KPIs
a. Introduction:
- Customer Background:
- The customer is a major player in shipping logistics, comprising multiple subsidiary companies and a financial group. They require an improved logistics system to support their shipping services.
- Challenges:
- Currently, they use an outdated QlikView report directly connected to the database, struggling with performance and data consistency. The data is updated only once a day. The infrastructure is entirely on-premises, and there are no internal resources to develop improvements.
- Their current business reporting solution is an outdated QlikView report that directly connects to their database. It causes performance and consistency issues. Data can only be updated once per day. The infrastructure is hosted entirely on premise. Customer has no internal capacity to implement fixes and improvements.
b. Solution:
- Implementation:
- The project begins with creating a DataLake to serve as the “single source of truth” for operations reports and machine learning (ML) forecasts for sales and purchases. Key steps include:
- Collecting, cleaning and transforming raw data
- Merging data from multiple sources
- Creating ETL pipelines with dataflows to merge data from different customers
- Redesigning reports and dashboards to meet the current needs of the Financial Department and CFO
- Our team consists of:
- One Software Architect expert in Microsoft services and Power BI (PBI)
- One PBI Developer
- One Data Scientist
- All team members are certified Microsoft experts in PBI reports and dashboards.
- Features Used:
- DataLake creation, data transformation and combination, pipeline creation with dataflows, and Power BI report and dashboard redesign.
c. Results:
- Quantitative Benefits:
- The new reporting system increased efficiency of the Financial Department. Before the project, detecting delayed payments was difficult. Now, overdue payments across the group are much easier to detect, reducing total open delayed payments by 14%.
- The entire process is documented.,. Historical data is utilized for sales and purchase recommendations and customer classification through ML models.
- Data is now updated eight times per day (with future plans for hourly updates using Premium capacity)The improved data update frequency allows the company to make timely and informed decisions.
- Qualitative Benefits:
- Data extracted from CRM Navi (Dynamics) is loaded cleanly into a reusable model (Azure Cloud). The cloud infrastructure provides easy connectivity and integration with other solutions.
- All steps from data extraction to presentation in dashboards and reports are well-documented, using Microsoft services, making future modifications and extensions straightforward.
d. Conclusion:
- Summary:
- We migrated the analytics solution to the cloud for better performance and connectivity, creating an infrastructure that is easy to maintain and scale with Microsoft Fabric, Azure Services, and Power BI. The new Lakehouse provides a single source of truth, enabling ML analysis of financial data.
- Future Plans:
- The first year focused on understanding the database, creating a common model, and migrating old QlikView reports. In the next phase, we plan to use Fabric Premium Capacity to enhance usage and explore premium features, particularly for large datasets. Future steps include running models for supplier forecasts and classifying customers/suppliers as good or bad payers to make informed recommendations.