Interoperability: Mapping Exchange Formats

[BorisDelange]

Summary

This discussion explores interoperability between the INDICATE Data Dictionary and existing OHDSI mapping tools, particularly Usagi (desktop) and Usagi on the Web. The goal is to enable seamless import and export of concept mappings while preserving INDICATE-specific metadata (evaluations, comments, distribution statistics).

To do:

• Define import/export formats compatible with Usagi
• Determine how to preserve INDICATE metadata (evaluations, comments) during export
• Explore integration with Usagi on the Web
• Document workflow for bidirectional mapping exchange

---

Current State

INDICATE Data Dictionary

Currently, the INDICATE Data Dictionary:

• Imports mappings via SOURCE_TO_CONCEPT_MAP.csv format
• Exports mappings via SOURCE_TO_CONCEPT_MAP.csv format
• Stores mappings in a local database with additional metadata

Limitation: Import/export loses INDICATE-specific information:

• Mapping evaluations (approved, flagged, invalid)
• Expert comments
• Distribution statistics (JSON)

Usagi Desktop

Usagi is the standard OHDSI tool for creating mappings between source codes and OMOP vocabulary concepts.

Export formats:

• Usagi format (.csv): Native format for saving/resuming work in Usagi
• SOURCE_TO_CONCEPT_MAP format: For loading into OMOP CDM

Key behavior:

• Only "Approved" mappings are exported to SOURCE_TO_CONCEPT_MAP
• The Usagi format contains additional columns for review status

Usagi on the Web

Usagi on the Web provides a modern web interface with additional export options:

Export Format	Description
Usagi Format	Compatible with Usagi desktop application for continued work
OMOP CDM Format	SOURCE_TO_CONCEPT_MAP table format for ETL processes
Simple Format	Basic CSV with source and target concept information
Athena Template Format	Excel workbook for OHDSI Athena vocabulary submission

Additional options:

• Filter by mapping status (unchecked, flagged, invalid)
• Include review comments

Note: The source code for Usagi on the Web is not publicly available. Contact SiData team for integration details.

---

SOURCE_TO_CONCEPT_MAP Table Structure

The OMOP CDM defines the SOURCE_TO_CONCEPT_MAP table with the following columns:

Column	Type	Required	Description
source_code	varchar(50)	Yes	The source code being translated
source_concept_id	integer	Yes	FK to Source Concept (0 or >2 billion for local codes)
source_vocabulary_id	varchar(20)	Yes	FK to VOCABULARY table
source_code_description	varchar(255)	No	Description for comparing source codes
target_concept_id	integer	Yes	The target Standard Concept
target_vocabulary_id	varchar(20)	Yes	Vocabulary of target Concept
valid_start_date	date	Yes	Date mapping was first recorded
valid_end_date	date	Yes	End of validity (default: 2099-12-31)
invalid_reason	varchar(1)	No	'D' (deleted), 'U' (updated), or NULL

---

Usagi CSV Format Structure

The Usagi format (extracted from Usagi source code) contains the following columns:

Source Code Columns

Column	Description
sourceCode	The source code identifier
sourceName	The source code description/name
sourceFrequency	Occurrence frequency (integer)
sourceAutoAssignedConceptIds	Auto-assigned concept IDs (semicolon-separated)
ADD_INFO:*	Dynamic additional information columns (prefix ADD_INFO:)

Mapping Columns

Column	Description
matchScore	Similarity score (decimal, e.g., 0.85)
mappingStatus	Status: APPROVED, UNCHECKED, FLAGGED, INVALID
equivalence	EQUAL, EQUIVALENT, WIDER, NARROWER, INEXACT, UNREVIEWED
statusSetBy	User who set the status
statusSetOn	Timestamp when status was set
conceptId	Target OMOP concept ID
conceptName	Target concept name (redundant, for readability)
domainId	Target concept domain
mappingType	Type: MAPS_TO, MAPS_TO_VALUE, MAPS_TO_UNIT
comment	Review comment
createdBy	Mapping creator
createdOn	Creation timestamp
assignedReviewer	Assigned reviewer

Key Differences from SOURCE_TO_CONCEPT_MAP

Feature	Usagi Format	SOURCE_TO_CONCEPT_MAP
Mapping status	Yes (APPROVED, FLAGGED, etc.)	No
Review comments	Yes	No
Match score	Yes	No
Equivalence type	Yes	No
Reviewer tracking	Yes	No
Valid dates	No	Yes
Invalid reason	No	Yes

---

INDICATE Value-Add: Distribution Statistics

INDICATE provides distribution statistics for source concepts as JSON, enabling visual comparison between source and target concepts:

{
  "data_types": "numeric",
  "numeric_data": {
    "mean": 120.5,
    "sd": 15.3,
    "min": 60,
    "max": 250,
    "p5": 95,
    "p25": 110,
    "median": 118,
    "p75": 130,
    "p95": 150
  },
  "histogram": [
    {"x": 60, "count": 150},
    {"x": 80, "count": 2500},
    {"x": 100, "count": 5000},
    {"x": 120, "count": 4200},
    {"x": 140, "count": 1800}
  ],
  "measurement_frequency": {
    "typical_interval": "4 hours"
  },
  "missing_rate": 5.2,
  "temporal_distribution": {
    "start_date": "2018-01-15",
    "end_date": "2024-06-30",
    "by_year": [
      {"year": 2020, "percentage": 25.1},
      {"year": 2021, "percentage": 28.7},
      {"year": 2022, "percentage": 30.4},
      {"year": 2023, "percentage": 15.8}
    ]
  },
  "hospital_units": [
    {"unit": "MICU", "percentage": 22.5},
    {"unit": "SICU", "percentage": 18.3},
    {"unit": "CCU", "percentage": 15.7},
    {"unit": "NICU", "percentage": 12.1},
    {"unit": "PICU", "percentage": 8.9},
    {"unit": "Neuro ICU", "percentage": 7.2},
    {"unit": "Burn ICU", "percentage": 5.4},
    {"unit": "Cardiac ICU", "percentage": 4.8},
    {"unit": "Trauma ICU", "percentage": 3.2},
    {"unit": "Step-down", "percentage": 1.9}
  ]
}

Screenshot placeholder:

This statistical comparison, combined with expert comments, helps non-experts validate mappings.

---

Open Questions

1. Which export formats should INDICATE support?

Current proposal:

Format	Use Case
SOURCE_TO_CONCEPT_MAP	ETL integration (current)
Usagi format	Continue mapping work in Usagi desktop
INDICATE format	Full export with evaluations, comments, statistics

2. How to handle INDICATE-specific metadata?

When exporting to standard formats (SOURCE_TO_CONCEPT_MAP, Usagi), INDICATE metadata is lost:

• Mapping evaluations (approved, pending, flagged)
• Expert comments
• Distribution statistics (JSON)

Options:

• a) Export metadata as separate files (e.g., mappings_metadata.json)
• b) Use extended columns in CSV (non-standard)
• c) Create INDICATE-specific format that preserves all data

---

References

• OHDSI Usagi
• Usagi on the Web
• OMOP CDM SOURCE_TO_CONCEPT_MAP
• The Book of OHDSI - ETL

---

How to Contribute

Please share your thoughts on the open questions above by replying to this discussion. Based on your feedback, the import/export strategy will be updated to ensure maximum interoperability with the OHDSI ecosystem.

[BorisDelange]

We have implemented the following import/export capabilities:

Format	Import	Export	Use Case
Usagi	Yes	Yes	Bidirectional exchange with Usagi desktop for continued mapping work
SOURCE_TO_CONCEPT_MAP	Yes	Yes	Direct integration as base for OMOP ETL processes
INDICATE Data Dictionary	Yes	Yes	Version control via Git, backup/restore of alignments with full metadata
CSV	Yes	No	Import source concepts from any CSV file

Import is available both when creating a new alignment and for adding mappings to an existing alignment.

The INDICATE format preserves all metadata (evaluations, comments, statistics) and is designed for saving alignments to a Git repository, enabling versioning and collaborative review of mapping decisions.

[MaximMoinat]

In this context I would like to mention SSSOM, which is proposed to be implemented in Usagi and there is a group using that standard to define custom concepts.