A Quick Tip on DQS Data Mapping Validation

After importing our mapping data into DQS, the business I was working with wanted to verify that all the values were mapped/matched correctly. We had a lot of data, so scrolling thru the UI to visually test was not realistic, and we’d also need an export of the mappings.

Since there isn’t an export option thru the UI, I looked at the SQL tables. Here’s how you can replicate what I did.

First, identify which KnowledgeBase Id is used for the schema name for your target domain data.

SELECT

[ID]

, [NAME]

, [DESCRIPTION]

, [TYPE]

FROM

[DQS_MAIN].[dbo].[A_KNOWLEDGEBASE]

Once you have that information, update the code below to point to the correct Knowledgebase. In my situation, the ID was 1000001.

SELECT

Child.[VALUE]as Term

,Parent.VALUE as MappedToTerm

,Domain.NAME as Domain

,Domain.ID

FROM

[DQS_MAIN].[KnowledgeBase1000001].[V_B_TERM]Child

LEFTOUTER JOIN [DQS_MAIN].[KnowledgeBase1000001].[V_B_TERM] Parent

OnChild.LEADING_EXTENSION_ID = Parent.TERM_EXTENSION_ID

INNERJOIN [DQS_MAIN].[KnowledgeBase1000001].[B_DATA_SERVICE_FIELD] Domain

ONChild.FIELD_ID = Domain.ID

–WHERE

—    Child.FIELD_ID = 1011 — Identifies the domain set, for example, Country

ORDERBY

Child.FIELD_ID

,Parent.VALUE

,Child.VALUE

I hope this helps save someone time!

Phone: 312-602-4000
Email: marketing@westmonroepartners.com
222 W. Adams
Chicago, IL 60606
Show Buttons
Share On Facebook
Share On Twitter
Share on LinkedIn
Hide Buttons