Gradient

Consolidating ERP and BI Systems for International Logistics

Practice Areas

  • Data Engineering
  • Strategic Consulting

Business Impacts

  • Improved ERP system capacity
  • Improved BI capacity
  • Streamlined, self-service BI reporting

Challenges

  • Consolidating disparate ERP and reporting solutions across six business areas
  • Data warehouse to serve over 300 predefined and ad hoc reports
  • Parallel ERP system replacement and BI development
  • Introduction of self-service BI

Technologies

  • Airflow
  • Docker
  • MSSQL
  • Power BI

Background

Our client is the leading delivery service provider in Hungary and functions as a regional headquarters in Eastern Europe. Accordingly, business decisions and IT developments will affect companies in six countries. Thanks to continuous business development — by introducing new services, ensuring high service quality — and industry growth in customer demand for delivery services, our client experienced a rapid rise in the number of parcels to be delivered. International logistics, including the need for international coordination of deliveries and the increase in parcel quantities placed considerable demands on the client’s data and BI infrastructure and revealed the inefficiency of having separate ERP system instances and reporting solutions for each country.

To ensure deep, accurate and timely insights for decision-makers, our client identified three critical areas for development: creating a unified operational framework, consolidated management and reportability of international financial accounts and the rapid and uninterrupted flow of information to cross-border support areas.

Challenge

Our client needed to replace their fragmented, country-specific ERP and reporting environment with an integrated ERP service and build a comprehensive BI solution, complete with self-service capabilities, upon the new ERP foundation. To ensure accurate tracking of the entire delivery process for every package, the BI solution needed to be in line with the end-to-end processes in the centralized ERP. The client also required a new data warehouse to provide consolidated delivery data to all six countries, serve more than 300 reports and support ad hoc instances in addition to predefined reports. Finally, historical data had to be available for each country, with segregated visibility and availability of business data at the country and organizational unit level.

Solution

The Starschema team analyzed each business area’s reporting requirements and mapped the source system operation and data model in parallel. We designed and engineered an on-premises data warehouse with a data lake to provide centralized, cost-effective storage of data from multiple systems and a data mart tailored to the client’s environment. Compared to pulling data directly from a data lake or a data warehouse, the data mart provides optimal performance by aggregating data to serve a specific reporting need.

The solution enables the daily loading of new data from the source systems into the data warehouse and improves data load scheduling and monitoring by leveraging Airflow technology. With Airflow, the client can continuously load data into the data lake layer of their data warehousing solution, even in the event of a change in the source systems’ data model, such as the introduction of a new table. This improves the BI system’s ability to serve new reporting needs. In addition to setting up data sourcing for the new BI solution, we migrated all historical data from the previous ERP environment.

To further improve the solution’s longevity and accommodate ad hoc reporting, our client chose to implement Tabular models. Compared to “native” report development, Tabular models enable more flexible querying of data with lower response times by pre-calculating dimensions, hierarchies and measures. To promote the adoption of an in-house standard reporting tool and prepare the client’s analysts to transition to self-service reporting, Starschema’s business analysts developed the required reports and dashboards, organized a series of data model presentations and conducted Power BI training for core users at both headquarters and other regions.

Outcome

The project introduced a comprehensive upgrade to our client’s existing BI environment, with a unified ERP and reporting solution. Over 300 reports and multiple dashboards were ready for business use from day one. The solution enables the daily loading of accurate, granular data from the six countries into the data lake layer and provides access to comprehensive historical data from the last two years. The BI training, coaching and support services helped key users to perform tasks autonomously — following self-service BI principles — and supported by both the new Tabular models and the custom data mart, resulted in more effective reporting. After the project’s conclusion, the client extended their engagement with Starschema to further develop and optimize the solution.

Telco Location Data Monetization

A global telecommunications company opened a new revenue stream and made it profitable in just two years.

IoT Data Lake on Azure Infrastructure

A leading European logistics company increased profits by using vehicle telemetry data to optimize routes and driver performance.

Better BI Decisions through Automated Reporting

To meet the demands of a rapidly evolving tobacco market, our client wanted to improve their reporting process for more accurate and timely insights. Their existing reports required a lot of manual work to prepare, and their quality regularly fell short of management’s expectations. To ensure that their BI environment would better serve the decision-making process, they reached out to Starschema for consulting services.