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?
Best answer by JTreadwell
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:
Disable simple mode only on the tables where you need to add the transformations. This can be done in table settings.
Create a view of the table and create the field & transformation there. Then map the view into the DSA.
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.
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.
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.
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:
Disable simple mode only on the tables where you need to add the transformations. This can be done in table settings.
Create a view of the table and create the field & transformation there. Then map the view into the DSA.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
