To homepage To homepage
Solved

How to create a transformation that is different for each mapping going to the same table

  • 20 December 2019
  • 5 replies
  • 24 views
S
Rank

Example scenario:

Table DimProject has 2 mappings, each from a different source:

Mapping 1 = SourceA.Project

Mapping 2 = SourceB.Project

Now both systems will have the same project number with different meanings.

My question is how do I make a transformation that returns 'A' + [project number] in mapping 1 and 'B' + [project number] in mapping 2.

The solution needs be rely on explicitly meaningful conditions, no implicit conditions that drive people crazy trying to decipher what they are for somewhere in the future.

Any ideas?

icon

Best answer by JTreadwell 21 December 2019, 02:01

View original

5 replies

J
Rank

Hi Steven.

If the source tables are coming from different sources, you could do the condition on the DW_SourceCode.

If the DW_SourceCode is the same - both tables are coming from the same Source - you could create an indicator on the delivering tables (a field called eg. SourceTable), fill it with 1 the first table and fill it with 2 on the second table. On the receiving table move the fields from the sourcetables to the same field and create the condition from this.

Regards JJ

S
Rank

Thanks for your suggestion! I'm keeping my fingers crossed for a solution that does not hit the source table :) 

I know there is a feature request for an actual system field DW_SourceTable but I think this area of features is more complex than most people need so it doesn't generate enough attention to make the roadmap.

andri
Rank
Badge +1

I've also had this issue and have used a custom field in each source table with a fixed value. That's an issue when you're mapping from an ODX that is in simple mode. 

If we were able to create a condition based on the source mapping it would allow us to create a conditional fixed value to solve this.

JTreadwell
Rank
Userlevel 3
Badge +5

 

Hi Steven, thanks for posting. This is a very common need that has a few possible solutions. 

If the two tables are from different sources then something like this would work:

If the two table mappings happen to come from the same source. Then you will need to create the field w/ a fixed transformation in the ODX/staging table. If the ODX/staging is in Simple mode you can do one of the following: 

  1. Disable simple mode only on the tables where you need to add the transformations. This can be done in table settings. 
  2. Create a view of the table and create the field & transformation there. Then map the view into the DSA. 

Does that make sense? 

-Joseph Treadwell
peter.jensen
Rank
Badge +2

Another solution is to create 2 query tables in which you add an extra column.

Peter Jensen (element61)

Reply


Terms and ConditionsCookie settings