Reverse trace data source

[jason_nicholas]

Hi,

  I am wondering if there is a way to identify the source of a particular item in a field? I would like to make a selection on my application, and see the transformational path that data point has taken since it was loaded.

  My application has a list of ~200 buildings, and I have these grouped into regions through a mapping load. I load the entire list of buildings that are in our environment, and map each one to either NA, EMEA, or APAC. Then, I load the source data in with ApplyMap to add a Region field.

RegionMap:

MAPPING LOAD * INLINE [

Building, Region

BldgA, NA

BldgB, EMEA

BldgC, APAC...

];

ApplyMap ('RegionMap', [Building], null()) as Region,

  I then have a function in my application used to test this. I have the Building field and the Region field next to each other. When I select all three regions, there should be no excluded buildings. When I find excluded buildings, it means there is data that wasn't present before, and I can add the new buildings to the RegionMap.

However, occasionally, a building appears as excluded even if it is listed in RegionMap. I'm assuming this means the data is coming in in a way that bypasses the ApplyMap in my fact table load, but I can't figure out how. Is there a way to view the load and transformation path of a data point so that I can look at the excluded building and determine where it came from, and why it doesn't associate with the listed region?

[marcus_sommer · Accepted solution]

I'm not sure that I have understood your problem right but I assume that your fact-table is created from multiple sources. These sources could be loaded as an own field, for example with something:

'db.XYZ.table.ABC' as Source

or by file-sources:

filebasename() as Source

Further if you put a real value like '#NV' instead of null() as the third default-value in your applymap() you could directly select them and see which other data are related to this record which will probably also give a good idea where the cause could be.

Also you could try to make your mapping more stable with something like:

ApplyMap ('RegionMap', upper(trim([Building])), '#NV') // the mapping table would need the upper too

which would exclude some of the possible non-matchings.

- Marcus

[jason_nicholas]

While my data does come in from multiple sources, the building information should only come in from a very limited few. That's where my confusion is coming in, because I THOUGHT I had that data under control, and I can't figure out what I am missing.

I will try the #NV solution, as that should be a pretty good indicator of sourcing. I'll come back and mark correct if this gets me where I need to be.

I'll also add your mapping stabilization. I don't think it is the issue, but it sounds like pretty solid advice regardless. Thank you.

EDIT: While I still have to figure out the reason for my issue, the #NV solution solved this question. I have three different ways building information can be imported, and I used "#NV1", "#NV2", and "#NV3" to separate these. I can now look at which sources are providing which errors, and hopefully, will be able to identify the mismatch by comparing.

Ultimately, I will remove the numbering and keep "#NV" in there to identify any new data in the future. Thanks for the help!

[jason_nicholas]

And just to close out the story, the issue I was encountering was due to errant spaces within my building title. I can source the problem to an Excel function I did to create my source list for the Mapping Load (text to columns, comma delimited). In the process, I added the spaces.

Find and replace {comma} {space} {space} with {comma} {space} in my Mapping Load, and the problem disappeared.