How We Solved a Multi-System Data Challenge Without Duplicating Business Logic

Introduction

Cruise operators manage thousands of passenger interactions every day, from operational announcements to personalized service offers. While these communications often rely on in-cabin TV systems, the underlying technology powering them is far more complex than it may appear.

A typical business case for the Passenger communication consists of the following steps:

1. Selecting Passengers eligible for the communication
2. Searching for cabins where these Passengers are staying
3. Sending messages to the TV channel

The “while onboard” part means the communication occurs while Passengers are located on a cruise ship, and data local to the ship is particularly involved in the process meaning the communication solution must be deployed in the ship environment to access the data. It means each ship runs its own campaign management system instance.

For a broader industry perspective, see our previous articles on operational intelligence at sea and the role of hybrid cloud in the travel experience economy.

The problem: diversity of environments

Despite the seemingly straightforward Business case steps, there is a catch: while logical requirements are the same for all passengers on the whole fleet, environments are not the same from a physical perspective. In fact, each ship is a separate environment and it can contain:

1. One of multiple Passenger management systems (storing cabin, PoS data etc)
2. One of multiple reservation systems
3. One of multiple messaging systems

The main issue here is the 1st point: it means that the structure of the Passenger data mart varies from ship to ship, which makes the “Select eligible Passengers” part far from straightforward, as SQL queries are completely different for different data marts. More than that, different systems use different Database Engines for storing data (MS SQL or MySQL in our case). And the complexity was further increased by an ongoing migration: the company is slowly migrating all ships from one system to another, which was a complicated task itself, taking multiple years to achieve.

However, implementing each business case for each data mart structure, while being the simplest solution at first glance, would result in a significant increase in development and support costs for the same number of implemented business cases.

‍

The solution: custom Data Access Utility

As we have noticed, despite the different physical data mart layout (tables, join conditions, etc), stored data is the same from the logical perspective. All data mart structures hold Cabin information, Passenger information and Cruise information, for instance. So the idea was to make a tool that will allow the code to obtain the data according to the logical meaning, not to the physical structure. The approach is close to what modern ORM frameworks do, but not the same: those tools are oriented toward single-entity processing and entity mapping, while we needed to work with batches of Passenger data records.

We called the resulting tool the Data Access Utility or DAU. This is a Python-written library built with SQLAlchemy and Pandas, enabling abstraction of batch data access by providing a Data Map object.

‍

The Data Map is a complex structure describing the database contents. It consists of the following parts:

1. Physical data description: information about table names, field names and field types
2. Logical data description: information about business entities and attributes located in existing tables and fields

This data is saved in JSON format. In the JSON there are the following types of objects:

1. Fields - physical fields stored in the Data Map, each with corresponding unique table ID
2. Tables - physical tables stored in the Data Map, with unique identifiers added
3. Table relations - objects describing physical relation between DB tables, featuring 2 table IDs and join condition (simply by 2 equal fields or more complex condtitions)
4. Entities - logical business entities (e.g. Passenger, Cruise or Cabin), corresponding to key fields
5. Entity relations - logical relations between entities (e.g. for one Cabin there can be many Passengers)

Having this data available, the DAU can traverse the object tree to dynamically generate and execute the appropriate physical SQL queries.

The key point here is that logical queries do not interact with the actual Data Mart structure - only with logical entities defined at the Data Map level.

‍

It allowed us to do the following for our 2 Data Marts:

1. Inspect both Data Mart structures from the existing Business Case perspective, keeping track of the logical data items needed for the implementation (e.g. many cases require the Passenger first name)
2. Make two separate Data Maps, containing completely different physical object descriptions, while keeping the same logical IDs for needed data items
3. Build a single implementation for each Business Case using DAU queries instead of SQL queries

It resulted in having a stable set of business cases built on top of data loaded and transformed by the Data Access Utility. As the implementation language is Python, the DAU “business” queries return Pandas data frames, just as a raw SQL query would.

The outcome: streamlining campaign management

The Customer has a large number of onboard Passenger communications (both marketing and informational) needed to be conducted on certain occasions: amenity offers based on passenger’s behavior, warnings about schedule changes, information about tendering ports for those who are debarking from the ship. It resulted in a high total number of business scenarios, being close to 20.

Having “only” 20 implementations instead of 40 resulted in the following business-measurable goals:

1. The code base is significantly smaller and easier to maintain, having all DB operations standardized and logged in the same way
2. The campaign management design is not affected by the migration, leaving the headache of maintaining 2 Passenger management systems to technical team, allowing campaign managers to focus on actual business scenarios
3. The campaign management downtime needed for Passenger management systems upgrade is close to zero, making the upgrade process faster

The development and testing of the Data Access Utility took about 40 man-days of work potentially saving hundreds of man-days of future marketing scenarios development and maintenance.