ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Context, Logical And Physical Schema – How does it work?

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.

Topology Engine

Topology Engine

 

 

 

 

 

 

 

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:

Logical Schema, Context and Physical Schema inter-relashionship

Logical Schema, Context and Physical Schema inter-relationship

 

 

 

 

 

 

 

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

17 Comments

  1. 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?

    • 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….

  2. 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.

  3. 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.

  4. 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

    • Hi,

      Yes, it’s possible if you generate a scenario, when the scenario is called you can call it with distinct contexts…

      Make sense?

  5. 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?

  6. 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.

    • 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.

  7. 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.

  8. 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.

  9. Good Idea Sam, I will publish a new post to complete it…

    Thank you!

  10. This post is really informative!!!!

    Adding ‘Agents’ in this equation, would have made this even more informative.

  11. 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)

Leave a Reply

Required fields are marked *.