Data Architecture - CY/SD on Facility and City

Jira item

https://maersk-tools.atlassian.net/jira/software/c/projects/SMDSO/issues/SMDSO-1451?jql=project%20%3D%20%22SMDSO%22%20ORDER%20BY%20created%20DESC

Requirement

Copied from Jira item:

When searching for Cities on MCOM, it must show whether the City has CY Facilities within it. SD has been descoped as MCOM assumes all cities have SDs.
It is only relevant to consider Maersk operational Facilities for this requirement. The customer’s facilities are not in scope for this as from an online booking perspective customers cannot place bookings using other customers facilities.
It is not relevant to indicate whether a Facility is a store door or whether a City has store door Facilities within, as MCOM assumes that all cities available has store doors. This assumption must be on the MCOM side and not an indicator in the master data as it can be incorrect.

Screenshot from MCOM:

The customer will need to specify the exact address later, but that's not a requirement for Maersk to say whether transporting to that location is possible yes or no. In that sense - as far as pricing/routing is concerned - it's not tied to customer facilities.

Considerations

Based on above, it seems that we need to use a different way to keep this information than just stamping Facilities and Cities with CY/SD. It will not be a true representation of the actual ask, e.g. a Maersk operated store door appears to be Maersk operated warehouses. To make it future proof, and be able to have correct indications of Facilities in future, suggest the following approach.

We have in the domain model "includedFacilityTypes" as collection. This can be used to indicate that a facility has a "WAREHOUSE" (typically what a Maersk operated SD would be in Ocean world) and/or a "CONTAINER_YARD" and/or a "CONTAINER_FREIGHT_STATION" etc. This gives the ability to have more than one type for a facility so we do not have to have create duplicate facilities if it offers two or more types.
Facilities can then be set true in "isOperatedByMaersk" so that consumer can filter on whether a Facility is Maersk operated or not. In SMDS all Operational Facilities would be true in this field. But this is added to ensure future proof solution so that we can distinguish the whole facility collection between Maersk operated and non-Maersk operated.
At City level we can then include attribute "hasMaerskOperatedContainerYard" - Derived attribute! Not something that can be maintained. Creating/updating facilities within a City would update the flag.
At City level we include attribute "hasMaerskOperatedStoreDoor" - Derived attribute! Not something that can be maintained. Creating/updating facilities within a City would update the flag - this will not be needed to start with, as Maersk.com assumes that all Cities has SD - so this need not be implemented as part of this requirement.

City level

City data via Event-Carried State Transfer (ECTS)

hasMaerskOperatedContainerYard is a must have
hasMaerskOperatedStoreDoor is a nice to have as Maersk.com assumes all cities has SDs

City data via API

hasMaerskOperatedContainerYard is a nice to have as consumer will use ECST
hasMaerskOperatedStoreDoor is a nice to have as consumer will use ECST and Maersk.com assumes all cities has SDs

Facility level

Facility data via Event-Carried State Transfer (ECTS)

includedFacilityTypes and isOperatedByMaersk is a nice to have as Maersk.com is not consuming this level

Facility data via API

includedFacilityTypes and isOperatedByMaersk is a nice to have as Maersk.com is not consuming this level

Grain

The data on includedFacilityTypes and indication of isOperatedByMaersk must be populated at the Facility level. City level indication of hasMaerskOperatedStoreDoor and hasMaerskOperatedContainerYard must be aggregated from the Facilities within the city.

Performance

The City level attributes are both for performance reasons and ease of use of data so consumer will not have to search all facilities within a city, check the types and derive whether it they are/not operated by Maersk and whether the types present are considered container yards and/or store doors - this would be far too heavy operations for the consumers.

It is very important that the Facility and City data is kept in sync based on changes to Facility level data. This means that when setting the includedFacilityTypes and isOperatedByMaersk for a Facility, the derived City level attributes must be updated accordingly. Likewise is deactivating, or deleting facilities within a city the city level data must be updated to reflect correctly.

Integrations

Involved solutions

It is important to note that the consumers of this data may not be receiving the data directly from SMDS. For example the solution SSIB is using Synergy solution for master data and reference data. Synergy consumes data from SMDS via events, stores it in a Postgres database, and then publishes the data via APIs to http://Maersk.com solutions. It is important for the planning exercise that there is visibility to all the layers and teams that must be involved to have changes done. For the Synergy solution specifically, it will not be allowed to change, as this was a stop gap solution, which means consumers of data coming from there, must build their own consumption solution, merging data with what they get from Synergy or building something new that replaces their usage of Synergy.

Data

Data for Facilities, their includedFacilityTypes and isOperatedByMaersk, should be able to be captured in both customer facility implementation and operational facility implementation - however for this implementation only operational facilities are in scope.

City data resides in the Geography product and therefore any update to a City level data, must be performed when updating facilities in real time.

Therefore the two master data products that will need amendment to support this implementation are:

Operational Facility product
Geography product

Facility level data structure

Facility data example with new properties, data is made up! (showing a few properties of the Facility schema only):

[

  {
    "facilityCode": "MYTPPWHS",
    "facilityName": "Tanjung Pelepas Warehouse",
    "isOperatedByMaersk": true,
    "includedFacilityTypes": [
      {
        "facilityTypeCode": "WAREHOUSE"
      }
    ]
  },

  {
    "facilityCode": "MYTPP01",
    "facilityName": "Pelabuhan Tanjung Pelepas",
    "isOperatedByMaersk": true,
    "includedFacilityTypes": [
      {
        "facilityTypeCode": "CONTAINER_YARD"
      },
      {
        "facilityTypeCode": "PORT"
      },
      {
        "facilityTypeCode": "MARINE_CONTAINER_TERMINAL"
      }
    ]
  }

]

City level data structure

City data example showing new properties (showing a few properties of the City schema only):

{
  "cityCode": "TPP",
  "countryCityCode": "MYTPP",
  "cityName": "Tanjung Pelepas",
  "hasMaerskOperatedStoreDoor": true,
  "hasMaerskOperatedContainerYard": true
}

Note - above showing the hasMaerskOperatedStoreDoor is not in scope for this implementation and Maersk.com assumes all cities have SDs.

Current Facility Types

Below shows the current facility types available with indication given of whether they are considered a Store Door and/or a Container Yard.

Facility Type	Facility Type Code	Is Container Yard	Is Store Door	Notes
Airport	FCT_TYPE.AIRPORT
FCT_OPS_TYPE.AIR
Barge Pier	FCT_TYPE.BARGE_PIER	Yes
Bunker site	FCT_TYPE.BUNKER_SITE
Canal Site	FCT_TYPE.CANAL_SITE
Cold Store	FCT_TYPE.COLD_STORE	Yes
Container Freight Station	FCT_TYPE.CFS	Yes
Container Production	FCT_TYPE.CTR_PROD
Cross Border	FCT_TYPE.CROSSBORDER	Yes	Yes	Cross Border activities can be both CY or SD as generally we would offer a SD/SD move for country to country inland movements
Customer's Pool Facility	FCT_TYPE.CUST_POOL_F	Yes
Customs Facility	FCT_TYPE_CUSTOMS.F	Yes
Inland Container Depot	FCT_OPS_TYPE.ICD	Yes
Maintenance and Repair	FCT_TYPE.MAIN_N_REP	Yes
Off-hire depot	FCT_TYPE.OffHIRE_DEP
On/Off Hire Site	FCT_TYPE.ONOFFH_SITE	Yes	Yes
Port City Container Depot	FCT_TYPE.PCC_DEP	Yes
Railhead	FCT_TYPE.RAILHEAD	Yes
Reefer Depot	FCT_TYPE.REEFER_DEP	Yes
Terminal	FCT_TYPE.TERMINAL	Yes
Trucker	FCT_TYPE.TRUCKER	Yes
Warehouse	FCT_TYPE.WAREHOUSE	Yes
WaterWay	FCT_TYPE.WATERWAY
FCT_OPS_TYPE.DEP
FCT_OPS_TYPE.TRANSPORTATION

Experimental data from Pipedream

The following topics are sourced from Retina to Pipedream:

facility_v4
geography_v3

Using Pyspark:

Facility data set was combined with above indictors of whether the types of the Facility is considered CY and/or SD. Facility type with no indicator are considered to be false in both.
Facility has a city name and ISO 2 country code, but no city code.
Geography data set is filtered by geo type City, its city code is obtained from alternative codes array from the RKST code type, and its ISO 2 Country Code is taken from alternative codes array on country level for RKST code.
Facility and City data set is merged based on city name and ISO 2 country code.
Several cities has no facilities within them, the CY and SD indicator for these cities are set to false

Was this page helpful?

Jira item

Requirement

Considerations

City level​

City data via Event-Carried State Transfer (ECTS)​

City data via API​

Facility level​

Facility data via Event-Carried State Transfer (ECTS)​

Facility data via API​