OHDSI Home | Forums | Wiki | Github

Move local mappings from Source_to_concept_map to Concepts

Is there any guidance on how to move your local mapping from Source_to_concept_map to the Concepts table? There’s a lots of talk about how STCM is an old artifact, but little on how to accomplish it. Or does everyone just make it up themselves?

Standard guidance might include:

  • Which STCM fields map to which Concept fields

  • What relationships are required in the Concept_relationships table?

  • Do you need to create a domain_id? vocabulary_id? concept_class_id? With values in their related tables?

  • Any other relationship mappings that are not required, but helpful?

  • Guidance on how to create the >2,000,000 concept_id.
    **Create it in the STCM table?
    **Create it in the ETL?
    **Use identity column in Concept?
    **Use a sequence

  • Guidance on how to maintain the >2B records in the Concept table

  • Guidance on how to protect >2B records from deletion in vocabulary update
    **Store in separate table?
    **Use STCM table as a staging table

  • Pros and cons of using both STCM and Concept?
    **STCM for ETL
    **Concept for Atlas compatibility

I’m sure there are other considerations I haven’t considered in my considerations yet.

Hello @roger.carlson ,

I’d be happy to share the process we use in Colorado. Perhaps we can talk about this subject on an upcoming Healthcare Systems call and harvest the community’s knowledge and processes?

That would be great. Would that be the right forum to publish such guidance?

Published guidance will need to be documented with the CDM documentation. Are you volunteering to write up the guidance :slight_smile:

Er, well, that is… Look over there! It’s Haley’s Comet!

I not averse to working on that.

1 Like

If anyone has ideas in this regard, please post them here. They can be a basis for further discussion.


I would be happy to serve as a reviewer of that guidance. Or even contributed specific pieces as well. Feel free to assign me in Github.

1 Like

Hey on these lines,
We have a process where we have mapping tables in Usagi format + few additional columns, for mutiple vocabualries.

Then some R code that takes all these Usagi+ tabels and transform that into concept, concept_relationship, concept_synomim and appends that to the tables downloaded from athena.

Code also includes some checks along the way.

If some one is interested we can share it. It is a bit raw still, but soon may be able to show.

We would very much appreciate the opportunity to try your r scripts. I’m working with @roger.carlson on this project. I’ll send my email in a direct message. Ty!

@roger.carlson @MaximMoinat @Sanjay_Udoshi @Javier

The next Healthcare System call is Monday, March 27th at 10am Eastern Time on Teams. Please join us and let’s discuss this further. It would be great if @Javier is able to join to show & discuss his R script.

Thanks Melanie. @MPhilofsky . This was a great discussion and very enlightening. @Javier , thanks for sharing your process. Both of your experiences have been helpful.

Going forward here, I’m going to re-ask the questions in my original post one at a time (and in a different order) that incorporate ideas from the WG call and let others share their thoughts. Maybe we can put this all together in a guidance.

Question: What is the overall process for putting local mapping codes into Concept and Concept_Relationship?

  1. Usagi_stage
    – Usagi CSV files as source of truth
    – Add additional information fields in Usagi
    – Maintain mappings in Usagi
    – Store >2B concept_ids in Usagi
    – Load into Concept and Concept_Relationship with R scripts.

  2. Table_stage
    – Map using Usagi or Athena
    – Add additional information columns (concept_id, domain, relationship_ids (from and to), etc.)
    – Store mappings in secure tables, one for each mapping. Maintain as needed.
    – Create >2B concept_id in stage tables.
    – Load into Concept and Concept_Relationships from the same file. (ids pre-created in stage tables)

  3. STCM_stage
    – Map using Athena, Usagi, or manual.
    – Store in the Source_to_concept_map table.
    – Copy into Concept_stage and Concept_relationships_stage, creating >2B concept_ids.
    – Use SQL and Concept table to supply additional information (domain)
    – Append to Concept and Concept_relationships

Are there any others?