October 20th, 2011.
Gianluca
In any Business Intelligence environment, changing technology of data sources is a big challenge.
This is valid particularly in the case of mature, long-running BI platforms, where the overall ETL processing is likely to exceed three or four hundred single jobs.
A change of the data source database technology – for example, from SQL Server to Oracle – and related data migration means often a painstaking exercise of manually updating every single ETL step, unit and regression testing, QA and moving to Production.
In order to minimise the effort required it is recommended to avoid database-specific SQL wherever possible, and to make use of any automation your ETL tool offers in order to make the code portable across platforms.
Continue reading this post >
February 1st, 2010.
Gianluca
One of the greatest advantages of buying an OBI Application – Project, Supply Chain or any other of the many Analytics flavours – is the set of predefined ETL mappings, sessions and workflows that come with it.
Although there is a good chance that the OLTP data source is highly customised, the online Oracle documentation is full of information that can make the ETL developer’s life easier. That said, there are some important ETL tasks whose logic isn’t very easy to find. They are like black boxes: you customise them a little bit – some fields behind the X_CUSTOM placeholder here, a small datatype change in the target table there – and they operate their magic.
So let’s reveal the truth behind the veil of a very important out of the box ETL logic in OBI Apps: the Slowly Changing Dimension management mappings.
Continue reading this post >
December 22nd, 2009.
Gianluca
In a not so remote past, one of the biggest frustrations for ETL designers was having to replicate a job many times in order to accommodate slight differences depending on the data source. Whether it’s a change in the source table definition or a different clause in the extract SQL, today’s ETL tools have embed capabilities that allow for the reuse of metadata objects to save time and improve maintainability of the workflows.
In this post, I will describe with simple examples how to best reuse ETL mappings in Informatica PowerCenter using the SQL override capability.
Continue reading this post >
