This question might sound strange, but my experience with people has led to explaining this fact in my blog.
So, tables are contained within a schema, for convenience sake, lets assume everything is done in Oracle SQL. I have two schemas as Source : Source1 and Source2, one staging : Staging and a target schema Target.
Case 1: Can I use tables in Source1 and Source2 joined by some clause as Sources ?
Yes, if tables are joined in source, it doesnt matter what schemas they belong to.
Staging can be set as target or defined as something other than target
Case 2: I need to use table1 from Source1 as source, I will need table2 from source2 while looking up a certain value while inserting in target but, essentially table1 and table2 wont be joined by any logical condition and Staging area is different from target
Now, things get a little tricky here.
Any table X that is referenced in ODI interface should satisfy one of the criteria to work successfully
1. It should be used as a source
2. If X is used along with another table as Source, X and the other table should be joined
3. If X is not present in Source but needed for some reasons, the staging schema should be the same as the schema to which X belongs, and table X needs to be explicitly called with its schema name (e.g. Select * from StagingSchema.TableX ) and this mapping must be executed on staging area.
(In the next article, I will be explaining the use of 'odiRef.getObjectName' for this purpose)
Case 3: Source1 used as source, Source2 needed but not joined and Staging area same as target.
In this case, the staging schema is that of target i.e. Target
A workaround for this case is to bring the contents to Source2.Table2 into Target.TempSource2 (replicate the table of Source2 schema in Target schema and use it) But there is additional dependency of creating a table and pulling data into another one, which adds to the confusion
So, the moral of the story is : Whenever doing integrations make sure that Source Schemas are organized in such a way that they can be safely used without conflict, or, in other words, the datapoints must be designed smartly.
Cheers!
So, tables are contained within a schema, for convenience sake, lets assume everything is done in Oracle SQL. I have two schemas as Source : Source1 and Source2, one staging : Staging and a target schema Target.
Case 1: Can I use tables in Source1 and Source2 joined by some clause as Sources ?
Yes, if tables are joined in source, it doesnt matter what schemas they belong to.
Staging can be set as target or defined as something other than target
Case 2: I need to use table1 from Source1 as source, I will need table2 from source2 while looking up a certain value while inserting in target but, essentially table1 and table2 wont be joined by any logical condition and Staging area is different from target
Now, things get a little tricky here.
Any table X that is referenced in ODI interface should satisfy one of the criteria to work successfully
1. It should be used as a source
2. If X is used along with another table as Source, X and the other table should be joined
3. If X is not present in Source but needed for some reasons, the staging schema should be the same as the schema to which X belongs, and table X needs to be explicitly called with its schema name (e.g. Select * from StagingSchema.TableX ) and this mapping must be executed on staging area.
(In the next article, I will be explaining the use of 'odiRef.getObjectName' for this purpose)
Case 3: Source1 used as source, Source2 needed but not joined and Staging area same as target.
In this case, the staging schema is that of target i.e. Target
A workaround for this case is to bring the contents to Source2.Table2 into Target.TempSource2 (replicate the table of Source2 schema in Target schema and use it) But there is additional dependency of creating a table and pulling data into another one, which adds to the confusion
So, the moral of the story is : Whenever doing integrations make sure that Source Schemas are organized in such a way that they can be safely used without conflict, or, in other words, the datapoints must be designed smartly.
Cheers!
