Tip: Generating Documentation in ODI

The most annoying part of my job is generating documentation. I hate when you finish a project and you have to go through all your code and interfaces to describe what they do. This is mainly for two reasons. First, I am not so well organized that I write down every little change when I apply it. Mea culpa. Second, because most of the time I am in a hurry and the most important thing is showing results instead of documenting what you are doing.
When you develop a considerable amount of interfaces, involving a considerable number of tables, columns and transformations, the last thing you want to do is re-open them to describe their behaviour.
Luckily, ODI provides a feature for generating PFD documents of your job. To use this functionality, you first have to configure it.
In ODI->User Parameters, you have to edit the following entries:

•  Default PDF generation directory: this is the complete path to the folder you want to save your documentation to. E.g. C:\TEMP\
• PDF Viewer: Full path to your PDF viewer. E.g. C:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe

 

ODI allows you to generate documentation for the following objects:

• Knowledge Modules: right click on a project, then Print -> Print Knowledge Modules
• Packages: right click on a project folder, then Print-> Print Packages
• Procedures: right click on a project folder, then Print -> Procedure
  
• Interfaces: right click on a project folder, then Print -> Interfaces
  
• Folders: right click on a project folder, then Print ->Folder

The result is a PDF file with detailed information of the object you printed. The most complete option is the one for printing folders, because you can generate documentation of every object in it.
The thing I like the most is the fact that for every interface you get the physical structure of every source and target table, every join and, most important, each transformation you apply to get a target column.

ORA-02292: integrity constraint (.) violated - child record

I was writing an ODI procedure from creating a static dimension from scratch. The procedure looked like this:

begin
DELETE FROM <DIM_TABLE>;
INSERT INTO <DIM_TABLE> VALUES (0,1,etc.);
COMMIT;
end

When I run it, I got the following error:

ORA-02292: integrity constraint (<OWNER>.<FK_CONSTRAINT>) violated - child record

The error means that I was trying to delete values that were used in a column that had a foreign key constraint on it.
To overcome this issue, without deleting any value in the referencing table, you first have to disable the constraint and then enable it again once you have finished populating the table.
So the procedure will look like this:


begin
execute immediate 'ALTER TABLE <DIM_TABLE> DISABLE CONSTRAINT <OWNER>.<FK_CONSTRAINT>';
DELETE FROM <DIM_TABLE>;
INSERT INTO <DIM_TABLE> VALUES (0,1,etc.);
COMMIT; 
execute immediate 'ALTER TABLE <DIM_TABLE> ENABLE CONSTRAINT <OWNER>.<FK_CONSTRAINT>';
end

Tip: How to specify Service Name in JDBC Connection

I needed to specify a new Oracle Database Connection in ODI. I have always used SIDs to configure JDBC connections.

jdbc:oracle:thin:@<hostname>:<port>:SID

Changing the SID for Service Name requires a minor syntax change. You have to replace the semicolon with a slash:


jdbc:oracle:thin:@<hostname>:<port>/<Service Name>

Tip: Oracle Data Modeler How to display just object names in a diagram

You may need to put your logical or physical diagram in a PowerPoint presentation, but it is quite unpractical showing all columns and details of a table. If you just want to show the Object Name, you have to go to View -> Details -> Only Names.

Then, you can give a better look to your diagram by customizing the look and feel and the best thing is that you can always go back to the full view.

Tip: Solve ORA-14402

Recently I have partitioned a fact table and today I had to perform the weekly load.
I got into the error:

ORA-14402: updating partition key column would cause a partition change


This error was being raised every time I tried to update the partition field (a date) in a record. To overcome this issue just execute the following:

ALTER TABLE <my_table> ENABLE ROW MOVEMENT;

Tip: Scheduling OWB jobs using SQLPlus

Scheduling jobs in OWB 11g using the Calendar feature is possible only if you have bought the Data Integrator Enterprise Edition, which is not the case of the project I am currently working on.

Error you get when you try to create a new Calendar Module in OWB 11g without Data Integrator EE license

You can still schedule jobs by using a SQLPlus script which can then be called in a .bat or Shell script and scheduled using Windows Scheduler or Crontab in Unix.
In OWB 11g, we can find a SQL script ready to use in [OWBHOME]\owb\rtp\sql. The script you need is called: sqlplus_exec_template.sql
The script can be executed only by a Workspace Owner or a Workspace user with Execute privileges.
The script uses the following parameters:

• WORKSPACE 
Workspace in which the job is to run: e.g. MY_WORKSPACE Make sure you specify the right Workspace Name. You can check it by executing:  

SELECT * FROM ALL_IV_WORKSPACES

• LOCATION_NAME 
Physical Name of the Location to which this task was deployed: e.g. MY_WAREHOUSE  
• TASK_TYPE   
• PLSQLMAP - OWB PL/SQL Mapping
• SQLLOADERCONTROLFILE - OWB SQL*Loader Mapping
• PROCESSFLOW - OWB ProcessFlow
• ABAPFILE - OWB SAP Mapping
• DATAAUDITOR - OWB DataAuditor Mapping
• SCHEDULEDJOB - OWB Scheduled Job
• CTMAPPING  - OWB Template Mapping

• TASK_NAME
Physical Name of the Deployed Object.e.g. MY_MAPPING. Make sure you specify the TASK_NAME in the following format: [PARENT_TASK]/TASK_NAME

• SYSTEM_PARAMS { , | (name = value [, name = value]...)}
• CUSTOM_PARAMS { , | (name = value [, name = value]...)}

The following example shows how to call the DIM_ALL workflow under DIM_FACT workflow package deployed in WFS_LOCATION location in MYWKS workspace.

SQL> @sqlplus_exec_template.sql MYWKS WFS_LOCATION PROCESSFLOW "DIM_FACT
/DIM_ALL" "," ","

The script calls functions from the OWBSYS.wb_rt_script_util package so you may need to grant the right privilege to the OWB User.

Tip: DATE and DATETIME in OBIEE

I must admit that I am new to the OBIEE world and I really wish I can help people that are starting with this Oracle product to save some precious time when dealing with Calendar dimensions.

I have created a Calendar dimension in my database with a field "DIA" to be DATE. This dimension is meant to replace one that was already in place, same content, but more fields. I have imported the table in the physical layer, mapped it to the logical layer, but when I run the dashboard I kept on seeing date fields with DD/MM/YYYY hh:mm in the calendar prompts.

I spent quite a lot of time (call me dumb!) to find out that the answer was as simple as replacing the DATETIME field with DATE.