Wednesday Mar 20, 2013

ODI - Slowly Changing Dimension Quick SDK Setup

You can quickly configure your SCD metadata on ODI datastores using this helper script here. Executing this script from the UI you can specify a driver file to quickly annotate your datastores with slowly changing metadata flags on the columns. To illustrate, let's use the dimension from an earlier posting here, the driver file below will configure all of the column metadata we need on the datastore in ODI.

  1. WAREHOUSE_MODEL,DIM_CUSTOMER,*,OVERWRITE_ON_CHANGE
  2. WAREHOUSE_MODEL,DIM_CUSTOMER,KEY,SURROGATE_KEY
  3. WAREHOUSE_MODEL,DIM_CUSTOMER,C_BID,NATURAL_KEY
  4. WAREHOUSE_MODEL,DIM_CUSTOMER,CURRENT_IND,CURRENT_RECORD_FLAG
  5. WAREHOUSE_MODEL,DIM_CUSTOMER,C_EFF,START_TIMESTAMP
  6. WAREHOUSE_MODEL,DIM_CUSTOMER,C_EXP,END_TIMESTAMP
  7. ,,C_MSTAT,ADD_ROW_ON_CHANGE

 

We can have many datastores configured from one file and use * to set the same value for all columns (that's what I did for overwrite on change above). The script also allows the omission of the model and datastore so you can just specify the column name and the scd type (the add row change line above).

After executing the script the metadata tags defined in the input file are applied to the model in ODI;

Scripting is a great way to beat those boring tasks when you are building large systems where you realize you can work more efficiently. The groovy script invokes the setScdType method on the OdiColumn class here. A little groovy goes a long way.

Wednesday Mar 13, 2013

ODI - Tip of the day, processing RSS from the web

ODI can easily process RSS data from the filesystem or web...the ODI XML technology has a driver whose file specification includes support for filesystem, URL and ftp. So processing RSS feeds is very simple. The reverse engineer can also generate a DTD based on the XML, so you don't even need a DTD or XML schema for the input stream. Converting JSON to XML and reversing is a very simple way to reverse a schema for information such as JSON, you can then enrich the model in ODI. In the URL below which I defined for an XML dataserver, I specify a BBC news RSS feed, I can then easily reverse engineer the schema into ODI.

  • jdbc:snps:xml?f=http://feeds.bbci.co.uk/news/rss.xml&s=BBC&dtd=d:/bbcrss.dtd

When you use the selective reverse engineer you can see the available datastores in the model generated, its very simple;

To be able to access this URL from within the ODI studio I did define my web proxy and URL - from within the ODI Studio UI you do this in the Tools->Preferences Web Browser and Proxy tab, I defined the HTTP proxy and port number to use.

Once reverse engineered you can view the model in an ODI diagram and see the relationships and objects reversed. You can also get to work and build interface sand view the data from the feed. For example, the ITEM datastore has the meat of the RSS news feed, here is a snippet of the content right now;

 Unlike file based XML, the HTTP based ones are read only, but let you easily integrate XML oriented datastreams such as RSS very easily.

Tuesday Mar 12, 2013

ODI - Compressing/Decompressing Files in Parallel

Here's a couple of user functions for compressing and decompressing files in parallel, you can control the degree of parallelism, it will compress/decompress all files in one directory and write into another. The number of parallel processes can be configured on invocation. I posted some time back about how ODI User Functions are really useful, this is a great example. What I have here is a couple of user functions you can call in an ODI Procedure or an ODI KM for example and the functions have 3 arguments; the input directory of files to compress/decompress, the output directory where zipped files will be stored or contents extracted to and the number of processes in the pool to process all of the files.

Below you can see the FileZipper user function used in an ODI procedure to demonstrate how to compress all of the files in a directory d:\inputlogs into a target directory d:\outputzips it uses 4 parallel processes to perform the compression. Obviously the performance is determined on the processors you have available in order to gain maximum benefit. 

You can download the user functions below, the target output directory must exist;

  1. FileZipper( inputDirectory, outputZipDirectory, numberOfProcesses) user function is here
  2. FileUnzipper( zipDirectory, outputDirectory, numberOfProcesses) user function is here

 

You can look into the user functions and edit, change and enhance. Let me know what you think. The implementation is java code that uses Java thread pools introduced in Java 5. A great example of leveraging java core capabilities to provide real benefit in your integration processes.

Monday Mar 11, 2013

ODI - Tip of the day, latest scenario version

Here's how you can run the latest version of an ODI scenario from a load plan, package or SDK without explicitly defining the scenario version. Although the UI infers you pick a specific version of the scenario code to execute, if you type in -1 for the version the latest version of the scenario code is executed.

Not sure how many customers are aware of that but a handy tip. I'm confident not many use it in the UI as its kind of tricky to setup. If you just type the scenario name and -1 its OK that works fine, but if you use the scenario picker you have to close the wizard after since the version is not editable, you would have to then edit the step and set the version to -1.

About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« March 2013 »
SunMonTueWedThuFriSat
     
1
2
4
5
6
8
9
10
14
16
17
18
19
21
22
23
24
25
26
29
30
31
      
Today