OWB 11gR2 & Scripting Quirks

In case you’re following me for this, you’ve noticed that what I must be doing is generating ETL code…  Well, you’re absolutely correct.  I’m using patterns in the Data Vault, patterns in source system models,  and patterns in code generation to generate ETL code for OWB.  In this case, it’s OMB Script (TCL script) that I’m executing.  In this entry I’ll share my thoughts about some of the scripting quirks, and WHY I’m generating the code.

First…  The quirks:

1) the “backslash” at the end of the lines (line continuation) cannot have anything after it, not even spaces.

2) the “IDENTIFIERS” for objects (ie: name of mapping, name of operators, etc..) MUST be a max of 30 characters long!!  What a pain.

3) The “IDENTIFIERS” for objects must be ALL upper case, it’s funny… you can create the operators in mixed case, but then when you attempt to reference them in mixed case – the OMB PLUS import complains that: “the operator m_Ixed_Case doesn’t exist.”  Change the reference to: “M_IXED_CASE” and all of the sudden BAM, it works.

ALL FIELD NAMES and IDENTIFIERS must be in UPPER CASE..  Which means you MUST switch any lower-case database attributes to UPPER CASE to work with them.  This is a REAL Hassle!  Especially when XDB loads attributes in Mixed Case (Camel Case).  Now, there is an instance wide setting for Oracle (and a DB wide setting, and a session setting) that allows you to run case-insensitive.  This must be turned on, before deploying a mapping or you will see errors.

4) As I said before, The following “datatype settings” must be applied:
a)  (DATATYPE)  VALUES (‘TIMESTAMP’)  – no length, no precision, no scale
b) (DATATYPE) VALUES (‘NUMBER’) –  no length, no precision,  no scale
c) (DATATYPE) VALUES (‘DATE’) –  no length, no precision,  no scale
d) DATATYPE is Character or LOB based, it requires a LENGTH specification.  No precision, no scale
e) DATATYPE is numeric or decimal, it should have PRECISION – scale is optional.
f) DATATYPE is Float, it *may* have Precision… NO SCALE!

Here’s the PERL code that makes the decision:

if ($dtype =~ /TIMESTAMP/i) {
        # no length, no precision, no scale
      } elsif ($dtype =~ /FLOAT/i && $prec>0) {
        $precstr = "PRECISION,";
        $precitem = $prec.",";
      } elsif ($dtype =~ /NUM|DEC/i && $prec>0) {
        $prec    = 0 if ($prec <0);
        $scale   = 0 if ($scale < 0);
        $precstr = "PRECISION,";
        $precitem = $prec.",";
        $scalestr = "SCALE,";
        $scaleitem = $scale.",";
      } elsif ($prec >0) {
        $precstr = "LENGTH,";
        $precitem = $prec.",";
      }

5) Always use OMBCOMMIT to make sure your OMB Scripting session can save to the repository and synchronize with the Warehouse Builder GUI.  Also, creating too many objects without a COMMIT may cause an out of memory error.

6) use OMBPULS <name of TCL script file>  command line to import.  Copy & pasting the script IN to the Warehouse Builder GUI has huge problems, especially if it’s a big file.

More  to come later…

Hope this helps!

Dan Linstedt

Tags: , , , , ,

8 Responses to “OWB 11gR2 & Scripting Quirks”

  1. dlinstedt 2010/05/21 at 6:46 am #

    Found another QUIRK:

    When setting properties for attributes, it is VITAL to setup the following order:
    DATATYPE, [LENGTH | [PRECISION,SCALE]]

    Even though it’s supposed to be metadata driven, and ORDER of the properties shouldn’t matter, IT DOES… especially for the DEDUPLICATOR and some of the other operators in mappings. Their parser reads a specific order of properties, that’s just how it goes.

    So: the following line DOESN’T WORK: (at least for the DEDUPLICATOR)
    SET PROPERTIES (PRECISION,DATATYPE,SCALE) VALUES (20,’NUMERIC’,0)

    Where the correct line it is expecting is:
    SET PROPERTIES (DATATYPE, PRECISION, SCALE) VALUES (‘NUMERIC’,20,0)

    Cheers,
    Dan L
    DanL@DanLinstedt.com

  2. dlinstedt 2010/05/21 at 7:07 am #

    Yet another quirk… When specifying a LOOKUP object, and you want to “SET PROPERTIES” it seems logical to first BIND the lookup to the table, then set the properties. You may have noticed, this doesn’t work!

    Here’s the PROPER working code for setting properties of a lookup AND binding it to a table:

    ADD LOOKUP OPERATOR ‘LKP_TARGET_KEY’\
    SET PROPERTIES (LOOKUP_CONDITION)\
    VALUES (‘INGRP1.OUNIT_ID = OUTGRP1.UNIT_ID’)\
    BOUND TO TABLE ‘HUB_UNIT’\

    The BOUND TO statement must happen AFTER the properties are set.

    Cheers,
    DanL@DanLinstedt.com

  3. Apex 2010/05/22 at 5:37 am #

    Hi Dan,

    A couple of comments if don’t mind.

    2) the “IDENTIFIERS” for objects (ie: name of mapping, name of operators, etc..) MUST be a max of 30 characters long!! What a pain.

    It’s because of limitation of object’s name length in Oracle – 30 bytes (not chars).

    3) The “IDENTIFIERS” for objects must be ALL upper case, it’s funny… you can create the operators in mixed case, but then when you attempt to reference them in mixed case – the OMB PLUS import complains that: “the operator m_Ixed_Case doesn’t exist.” Change the reference to: “M_IXED_CASE” and all of the sudden BAM, it works.

    It also can be a peculiarity of Oracle, the fact of the matter is that Oracle stores all dictionary info in upper case format. So, if you create table with the name “MyTable”, you should looking it in dictionary with predicate like this “where object_name = ‘MYTABLE'”. It seems that developer of the OWB Script just didn’t care about upper casing of object names.

  4. dlinstedt 2010/05/22 at 5:50 am #

    Hi Apex,

    I’m the developer of the OWB script… If I had a way to import the OMB TCL commands, and have them access the appropriate Case Sensitivity, I would switch the generation code over to use it. However, every time I tried to “setup” OMBPLUS with commands that were case-sensitive, it failed.

    It’s not the SQL against the dictionary that I’m worried about, it’s the use of OMBPLUS TCL scripts. Any ideas?
    Thanks,
    Dan L

  5. dlinstedt 2010/05/25 at 3:23 pm #

    I’d like to add a few more quirks to the list….

    When I generate code with Joiner conditions, or Filter Conditions, it doesn’t always validate properly. Let me explain:

    Step 1: Generate the Code
    Step 2: Import it using OMBPLUS command line (yes it issues OMBCOMMIT)
    Step 3: Open OWB Designer
    Step 4: Deploy (straight away)

    Result: ERRORS in the SQL code with invalid field names, missing parens, etc… Can’t pinpoint the error, so….

    Expected Result: VALID MAPPING PRODUCED

    Work-around (very time consuming): Click on JOINER, Open up the “Editor” for Joiner Properties, Click “VALIDATE”. Click on FILTER, open up the Editor for Filter Condition, click VALIDATE, Save the mapping, and DEPLOY a second time.

    I really shouldn’t have to do this over and over again, especially when I’m generating 150+ mappings into OMBPLUS in the first place… just be aware.

    Dan L

  6. dlinstedt 2010/06/02 at 7:09 am #

    Even MORE scripting quirks…

    Turns out… the ORDER of the commands (the metadata) is processed when executed. This is totally unlike a standard database engine (which usually waits for a commit before validating).

    In other words, creating a JOINER, with a JOIN CONDITION that accesses attributes that are defined “AFTER” the join condition – causes validation problems. The join-condition, the filter, and the lookup conditions all have to be validated by HAND after the fact in order for this to work properly.

    for example:
    [code]
    ADD LOOKUP OPERATOR ‘LKP_TARGET_KEY’\
    SET PROPERTIES (LOOKUP_CONDITION)\
    VALUES (‘INGRP1.OFAC_SQN = OUTGRP1.FAC_SQN and INGRP1.OEVNT_SQN = OUTGRP1.EVNT_SQN and INGRP1.OCLINIC_SQN = OUTGRP1.CLINIC_SQN’)\
    BOUND TO TABLE ‘LNK_ENC_FAC_CLINIC’\
    ADD ATTRIBUTE ‘OFAC_SQN’ OF GROUP ‘INGRP1’ OF OPERATOR ‘LKP_TARGET_KEY’\
    SET PROPERTIES (DATATYPE,PRECISION,SCALE) VALUES (‘NUMBER’,12,0)\
    ADD ATTRIBUTE ‘OEVNT_SQN’ OF GROUP ‘INGRP1’ OF OPERATOR ‘LKP_TARGET_KEY’\
    SET PROPERTIES (DATATYPE,PRECISION,SCALE) VALUES (‘NUMBER’,12,0)\
    ADD ATTRIBUTE ‘OCLINIC_SQN’ OF GROUP ‘INGRP1’ OF OPERATOR ‘LKP_TARGET_KEY’\
    SET PROPERTIES (DATATYPE,PRECISION,SCALE) VALUES (‘NUMBER’,12,0)\
    [/code]

    This OMBSCRIPT adds a lookup condition, and the fields referenced are added AFTER the properties are set. OMB when it parses this script attempts AT PARSE TIME to validate the lookup condition, and fails – so it marks the condition as INVALID because the fields haven’t been defined yet. However, there is NO KNOWN WORK-AROUND for this, except:

    a) add the transformation LOOKUP
    b) add the fields
    c) issue OMBCOMMIT
    d) issue OMBALTER MAPPING… ALTER LOOKUP (add lookup condition)

    This is a problem, because OMBPLUS absorbs scripts extremely slowly – and having the extra ALTER commands really slows it down further. On the other hand, it works.

    I’ve not yet tried to use OMBVALIDATE MAPPING commands to see if that will do the trick after everything is added.

    Dan L

  7. Thomas 2011/01/25 at 12:15 pm #

    HI

    Do you have a solution for the Joiner conditions-problem?

    Thomas

  8. dlinstedt 2011/01/26 at 6:55 pm #

    Hi Thomas,

    Thank-you for the comment. Unfortuantely no. Oracle has told me that right now they are not putting any big engineering efforts towards improving OWB, they have not yet solved these problems that I mentioned. Sorry.

    Dan Linstedt

Leave a Reply

*