Case Study: Logistics Analytics & Data Visualization

Need

With many years invested in a legacy analytics tool that was no longer supported, our client needed to find a replacement that would allow their business users to continue to analyze and evaluate Key Performance Indicators (KPIs) and to pull reports for organizational activities and data.

The legacy application included approximately 20 business “views” that needed to be converted for use in the new tool.  As their business continued to grow and change, there would be the need to add new data and views, so flexibility and adaptability were important.

The tool itself would need to:

  • Provide high level summaries, multiple types of visualizations, and also be able to drill down into the details.
  • Be web-based so that access to the tool was not hindered by location or worksite.
  • Have a very flexible permission system that would accommodate our customer’s multi-level multi-site management structure without making sensitive data available to the wrong people.
  • Be very user friendly for non-technical business users.  Pushing important data deeper into their organization would be a big win for our customer.
  • It needed to remain flexible and powerful for more advanced users and business analysts.

Solution

We worked with our client to research potential options and chose to implement Pentaho as the solution.  We were responsible for installing and customizing the Pentaho application to meet their business needs.  This included configuring the application to connect to Microsoft Active Directory for authentication as well as writing custom Java plugins for Pentaho in order to incorporate row level filtering for data access.

We designed and built a data warehouse and developed numerous (ETL) processes to organize and move data from their operations database into the data warehouse. We built an ETL framework that shared libraries with the operations database to cut down on development time and prevent errors caused by the ETL data models and operations data models getting out of sync.  Most of the ETL pipeline and data cube configurations are covered by automated tests.  Those tests help us catch errors early in the development process and give us confidence that the data these systems are providing to our users is correct.

As we converted each data view from the old tool to Pentaho, we worked with our customer to make sure the data that was being provided by the view was still relevant and accurate.  Due to this attention to the current & real needs of our customer, their BI landscape changed considerably, for the better.

Today our customer uses Pentaho for most of their business intelligence needs.  We have setup almost 50 different data cubes for their use. Those cubes cover a wide range of domains inside the organization including:

  • Operations
    • Asset meter readings & utilization
    • Asset Maintenance
    • Customer Management
    • Operational Efficiency
    • Fuel Tracking
    • Load Tracking
    • Labor Efficiency
    • Revenue Validation
    • Safety
    • Service Quality
  • Financial
    • Asset Tracking
    • Sales & Revenue
    • P&L
    • Vendor audits
  • Human Resources
    • Staffing Goals
    • Retention

Stats

  • Industry: Transportation/Logistics
  • Similar Project Estimate: $250K – $500K
  • Time Frame: 2 years
  • Skills/Technologies:
    • Data Warehousing
    • Extract Transform Load (ETL)
    • Pentaho BA Server, Data Integration
    • Microsoft SQL Server (operations DB)
    • Postgres SQL Server (data warehouse DB)
    • Python: SQLAlchemy, Jinja2, Nose
    • Java
    • Mondrian
    • XML
    • MS Active Directory integration
    • Microsoft Windows Server