Hi people,
Let me try to explain a subject that I already got a lot of emails asking me about how it works:
- Data Server
- Physical Schema
- Logical Schema
- Context
It’s very common doubts raises from this combination because it is based in 3 concepts that I call “The three bases”.
The most important is: one doesn’t exist without other.
It’s good remember that Data Server can be understood as the higher level of Physical Schema and that one Physical Schema is linked to one, and no more than one Data Server.
To a better understanding, take a look in the following flow:
How does this flow wok?
- Data Server
- Object that defines the connection to database. It storage the IP, User and Password for instance
- Physical Schema
- Defines 2 database schema’s (Oracle definition), one to read the data and other to ODI works (work area where the C$, I$ tables could be created if necessary)
- Context:
- Defines an “environment”, a particular instance for code execution. The most common example is Development, Test and Production environments but there are several possible other possibilities.
- Logical Schema
- It is an alias to a “Logic Structure”, I mean, when a code is developed in a Development environment (a single interface in ODI as example) it is expected (and necessary) that any database structure table and column used at it must be at any new environment where this code could be deployed because, if not, a database error will be raised.
Logical Schema is the final dot to understand the flow. The idea behind its existence is allow the same code be used at any environment once it is an alias.
But this alias, the Logical Schema, can not work alone, once it only represents the a Logical Structure not the connection itself, I mean, User, IP, etc… For that, exists the Physical Schema. It will complete the Logical Schema with physical characteristics to connection.
Because of that, one Logical Schema is linked to one, and just one, Physical Schema.
But why to have an alias to the user, IP, password??? Because then there is no need to include these physicals characteristics into the scenario (“compiled code”) allowing that if, for instance, a password is changed there is no need of a scenario regeneration!!!
Well, after understand the link between Physical and Logical Schema how to add the Context in this equation?
It is to determine, at execution moment, to which hardware the Logical Schema that points to a Physical schema it will be executed. A hardware here can be understood as Development, Test or Production.
If you take a look into the users and schema’s used at the figure you will see differences between the environments. I will explain those differences in another post, about “Connection Architecture”.
Friends, I hope to be helpful in the understanding of all this concepts!
See you soon.
Cezar Santos
September 15, 2019 at 2:17 AM
I need load from different locations of data servers to load it into the DWH by context ,one maping code is to load data from different servers by context change it will change it to server then it load it to the target table.
October 11, 2016 at 1:13 PM
Thanks a lot for the Information!!
I have a doubt that how we can connect two different technology’s like Oracle,Teradata in Physical Schema to Logical Schema that we can only only one logical Schema.
How Can I map these two Physical Schema’s to one Logical Schema?
October 11, 2016 at 5:03 PM
For that it’ll be necessary 2 Contexts… so for each physical you can use the same logical in distinct contexts. That is what happens when Development and Production context are defined, for instance….
July 4, 2016 at 2:08 PM
Hi,
Thanks a lot for the extremely informative article!
Why do we create physical schemas before logical schemas? Surely if we create the logical ecosystem first then when linking we are testing for the data’s conformity to our requirements/expectations rather than creating our logic according to the constrictions of present accessible data.
May 26, 2014 at 10:51 AM
Is it possible to define more than one physical schema associated with the same data server?
A. Yes, but you have to flag which one is the default physical schema.
B. Yes, but it is mandatory to specify a different user to log in.
C. It is possible,but it is better to avoid doing so because it is more difficult to define a logical
schema this way.
D. No, ODI Topology does not allow defining more than one physical schema for a data server
because the associated logical schema would be ambiguous.
May 26, 2014 at 1:57 PM
Between the options that you sent, it’s A
Where this come from?
May 14, 2014 at 7:04 PM
Hi Cezar,
Can I hardcode a context in a procedure and then use that code to override the context passed at runtime?
Thanks,
Anmol Kaushik
May 16, 2014 at 1:11 AM
Hi,
Yes, it’s possible if you generate a scenario, when the scenario is called you can call it with distinct contexts…
Make sense?
March 28, 2014 at 9:56 AM
Hi, when I execute a scenario in test environment, I am getting the error, unable to find logical schema in master repository. but the issue does not occur in development. I read from other posts to check for the existence of logical schema and I have done it. it exists. And also, to change the technology to undefined in the command on source tab in proceudres. but I have only interfaces and there is only one proc which is used across all the scenarios. so I don’t want to touch that.
can anybody help how to resolve this issue?
March 28, 2014 at 3:04 PM
Hi Raji,
Did you associate the Physical Schema with Logical Schema to the Test environment context?
September 14, 2010 at 12:02 AM
hi i want to run a pkg in more than 1 context.i hav used a procedure with following steps
1.create connection->
srcConn=odiRef.getJDBCConnection(“SRC”)
stmt=srcConn.createStatement();
……………
2.Retrieve and Store File List>
import glob,os
mydir=”;
mypattern=”;
filepattern=’%s%s%s%s’ % (mydir,os.sep,os.sep,mypattern);
mydirlist=glob.glob(filepattern);
mysession=”;
myfilestable=’F%s’ % mysession;
mystatus=’ARRIVED’;
stringtoprint= ‘mydir’ + mydir + ‘ patten:’ + mypattern + ‘filepattern :’ + filepattern
for file in mydirlist:
print os.path.basename(file);
mystmt=”insert into FILESLIST values(‘%s’,’%s’,’%s’,’%s’)” % (mysession,os.path.basename(file),mydir,mystatus);
stmt.execute(mystmt);
stmt.execute(“commit”);
stmt.close();
……………………
3.FileDispatcher->
(Target)
OdiStartScen -SCEN_NAME= -CONTEXT= -SCEN_VERSION=001
-“ORACLE.FileToBeProcessed=#FILENAME”
(Source)
Select FILENAME from fileslist where status=’ARRIVED’
………..
4.close connection-:>
srcConn.close();
ITS GIVING ERROR IN 3RD STEP..scenerio did not end properly..
plz suggest.
September 14, 2010 at 4:51 AM
although it will be difficult for me to suggest the correct error. Please check for this.
Have you make the command on Target with Sunopsis API .
Next can you copy the generated scenario code and call it from the oracledi/bin command prompt using startscen ,replacing variable FILENAME with hardcoded value. Iam sure you will be able to figure out then what mistake you are doing in syntax and later reflecting the same in the Procedure.
Also just want to add , i am not sure if you know this, if you use SCEN_VERSION= -1 , it will always fetch the most recent scenario version.
October 23, 2009 at 8:36 AM
hi,
I need to load files from a Linux server end development environment Oracle Data Integrator is on Windows platform.
In the creation of the technology file I added the absolute path of the location of files on Unix machine
but in executing the interface returns the erro “Column not found”.
The interface has been executed there in a Windows environment and it has not problems.
How could I configure the technology to point to the folder on the Unix system?
thanks in advance.
July 2, 2014 at 7:03 PM
I have the same issue, can you tell me how you fixed it.
October 23, 2009 at 7:54 AM
hi,
I need to load files from a Linux server end development environment Oracle Data Integrator is on Windows platform.
In the creation of the technology file I added the absolute path of the location of files on Unix machine
but in executing the interface returns the erro “Column not found”.
The interface has been executed there in a Windows environment and it has not problems.
How could I configure the technology to point to the folder on the Unix system?
thanks in advance.
October 16, 2009 at 9:31 AM
Good Idea Sam, I will publish a new post to complete it…
Thank you!
October 13, 2009 at 5:58 PM
This post is really informative!!!!
Adding ‘Agents’ in this equation, would have made this even more informative.
October 13, 2009 at 5:32 AM
Thanks a ton for the post!!
I guess it would be more clear/helpful if you can provide a set of example(may be moving from one enviornment to another enviornment, where the use of different contexts, physical and logical schema comes into play)