OHDSI Home | Forums | Wiki | Github

Move local mappings from Source_to_concept_map to Concepts

CDM Builders
#1

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.

How to add a custom vocabulary to the OMOP vocabulary table?
#2

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?

#3

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

#4

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

#5

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

#6

I not averse to working on that.

1 Like
#7

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

Thanks!

#8

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
#9

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.

#10

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!

#11

@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.

#12

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

#13

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.

#14

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?

Methodology for Converting Source-To-Concept-Map to Concept/Concept_Relationship (2 billionaires)
t