OHDSI Home | Forums | Wiki | Github

source_to_concept_map table


I have a question about mapping our systems codes to standard-concepts. If we use the Usagi tool to create the map (to a standard concept in the CONCEPT table), I understand that we append to the source_to_concept_map table (one record for each source-code). But in looking at the table documentation, I don’t understand: “If there is no source_concept_id available because the source codes are local and not supported by the Standard Vocabulary, the content of the field is 0 (zero, not null) encoding an undefined concept. However, local Source Concepts are established (concept_id values above 2,000,000,000).”

Do we create a CONCEPT table record for each of our codes or use “0” as the source_concept_id?


(Mui Van Zandt) #2

Yes, you will need to create 2 billion concept Id’s for source concept values that do not exist in the standard vocabulary. This will allow you to search and query using the source_concept_id and not have to rely on the source_concept_value.

(Sigfried Gold) #3

Hi @mvanzandt or other vocab people. I was trying to figure out what to do with some local concepts that would need new records in the concept table.

I haven’t found this in the OHDSI book, but is the documentation that Dev2121 referenced (https://www.ohdsi.org/web/wiki/doku.php?id=documentation:cdm:source_to_concept_map) still valid? I.e., it’s fine to add concepts to the concept table locally as long as they get concept_ids greater than 2B?


(Katy Sadowski) #4

Hopping on this thread as well; I have the same question. Is it recommended to create new concepts for all source codes mapped via source_to_concept_map? Since the Book of OHDSI doesn’t mention it and the Wiki’s advice is unclear I’d love to see what others are doing for this. Thanks :slight_smile:

(Don Torok) #5

Yes, it is fine to add concepts as long as the get concept_ids greater than 2b. The question is how are these concepts going to be used in the CDM. Aside from columns that end in ‘…source_concept_id’, the CDM expects ‘Standard’ concepts, concept.standard_concept = ‘S’, for columns that reference concepts.

(Katy Sadowski) #6

Thanks Don! It seems that adding the source codes as concepts could potentially simplify the ETL, in that for the code mapping step, we would only need to look at the concept table rather than both concept and source_to_concept_map. I shall confer with my ETL engineers :slight_smile:

(Don Torok) #7

I don’t want to lead you down the wrong path. I do not see how adding custom concepts can eliminate the need to use either the source to concept map or concept relationship tables for the code mapping step.

(Katy Sadowski) #8

Sorry I was unclear - in order to to eliminate looking at source_to_concept_map we also need to add the new “Maps to” and “Mapped from” relationships to concept_relationship. In this case source_to_concept_map really only serves as an intermediate table to hold the Usagi output.

I’ve done some digging around the forums and it seems some folks just keep x_source_concept_id as 0 for new source codes; others add them + their standard concept mappings to concept and concept_relationship, respectively.

It also seems like the choice on which to do depends on what’s best for each group’s specific use case. Would you agree that there’s not an official convention for this yet?

(Don Torok) #9

x_source_concept_id is NULLable, so you can either set to NULL or Zero without violating any convention. But if you are going to add your new concepts to the concept table and build the concept relationships, then why not fill x_source_concept_id with the concept id of your new concepts.
Some folks may keep x_source_concept_id = 0 because they use the source to concept map generated by Usagi to map source to standard concepts instead of adding concepts to the concept table and then creating relationships.
Does anyone have a script that takes as input a source to concept map table and creates new concepts and and Maps to and Mapped from relationships?

(Katy Sadowski) #10

Exactly. If we go the round of adding new concepts, we would populate x_source_concept_id with those concept ids.

Also curious to hear from others who have done this flow. If we end up going this route, I will be happy to report back :slight_smile:

(Don Torok) #11

@MPhilofsky hosts an OHDSI EHR Work Group bi-weekly meeting. I know the group she works with has added custom concept and relationships. Ask @MPhilofsky when the next meeting is and, if you can attend, you can ask about her experience.

(Katy Sadowski) #12

I’ve already signed up to attend the next one :slight_smile: looking forward to it and I’ll be sure to raise this topic. Thanks very much for your help!