Opportunity
Since 1972, The Laurentian Pilotage Authority (“LPA”) has been responsible for administering and providing marine pilotage services in the waters of the Laurentian region, in particular the St. Lawrence River and the Saguenay River. Each year, approximately 8,000 ships transit through these waters. This amounts to about 22,000 assignments annually that the LPA manages through its dispatch centre.
The LPA aims to further exploit its data but is hindered by multiple issues, such as the inability to obtain an overview of its data, limitations in their management, and dependency on a supplier for producing its analytical reports.
It is determined to equip itself with the best analytical, predictive, and decision-making tools to significantly enhance its ad-hoc analysis capabilities related to logistics and operations.
Challenge
A high-level analysis of the data system used by the LPA by Larochelle’s consultants has highlighted various shortcomings:
-
Data Quality
Data quality is impacted by various elements: data duplication within different instances, lack of clarity in data definitions and exceptions, absence of reference data, and lack of data traceability, etc.
-
Data Silos
Data is distributed across two independent on-site ERP (Enterprise Resource Planning) systems. One is the ERP fed by the LPA dispatch centre. The other is the legacy ERP, whose data has not been migrated.
-
Lack of Flexibility in Data Management
Integrating new data sources is not feasible with the current data system. Similarly, adding new types of data, such as definitions for different service and billing seasons or the grouping of charge types, is not possible.
-
Data Accessibility
The person responsible for analysis at the LPA does not have direct access to the data. Additionally, this person lacks the necessary technical knowledge and reporting tools, making them unable to produce their own analysis reports.
-
Reliability of Analysis Reports
The risk of human error significantly impacts the reliability of the reports since they are manually compiled through various Excel files.
-
Timeliness of Analysis Reports
Although already affected by dependency on their supplier, the timeliness of obtaining the reports is further burdened by their execution time.
-
Cost of Report Production
Given the solutions available on the market, the LPA’s report production costs are not justified.
It goes without saying that these elements drastically hinder the LPA’s analysis capabilities, which it aims to address by implementing a market intelligence system enabling it to create its own ad-hoc reports, dashboards, and establish its KPIs.
Solution
One of the first elements to address is the hosting of the new data ecosystem to be created. The LPA does not wish to host its future BI solution on its application server. It also does not consider adding an on-site server due to the lack of qualified personnel to maintain it. Therefore, Larochelle’s consultants propose the implementation of a cloud-based data platform. Since the LPA already uses Microsoft, Azure has been selected to host the new data ecosystem to be created.
As illustrated in the diagram below, the ecosystem consists of a data lake and a data warehouse with tables structured according to the Star Schema principles.
The data sources correspond to those of the ERP in use, as well as the database of the legacy ERP. To integrate these data, Azure Data Factory is used to build the extraction-loading-transformation pipelines, known as the ELT process. This involves extracting and loading the data into the data lake, and transforming them for the data warehouse.
Data integration will be performed automatically at a frequency established by the organization to ensure the reliability of the delivered information.
For data analysis execution, Power BI has been chosen and enables:
- The creation of a data model.
- The creation of reports.
- The creation of prepared datasets.
- The creation of dashboards.
- The creation of ad-hoc queries.
- The exploration of source data.
A training component is included in the mandate, encompassing support for the system administrator and knowledge transfer on the use of analytical models. Our consultants thus ensure the full autonomy of their client in the administration and use of its BI solution.
The data platform has now been operational for several months. Our client is fully satisfied. It marks the end of navigating blindly and the beginning of informed decision-making for the LPA! The LPA have a reliable tool that fully meets the needs of its users, and even more. Relevant and accurate information can be quickly delivered to the LPA’s management. And, when desired, the LPA will have the ability to evolve its solution with additional analysis perspectives, metrics, and other data subjects.
