Wednesday, February 24, 2016

Designing, building, loading and managing a Staging Area for an Oracle Data Warehouse. In 20 minutes.



I briefly summarize what I have described in the presentation, with the same name, on Slideshare.

In the recent years, I have tried to explain and share a vision of the Data Warehouse which I defined as Micro ETL Foundation (MEF). MEF was born as a set of ideas, tips and methods to be able to successfully implement a project of this type.

The main guideline was the simplicity. This was the most difficult point to be maintained, because a project of Data Warehouse and Business Intelligence is extraordinarily complex.I have shown in various articles, that even small details such as the descriptions of the codes or the null values management can weigh heavily on the final outcome of the project.In fact, in my opinion, the real complexity lies in being able to simplify  the complexity.

It is now time to collect and put together organically and usable in real life, everything that I wrote, giving a consistent life to the Micro ETL Foundation transforming it into a an ecosystem to be used by all in a simple and free way.
The implementation was carried out using the internal language of the database (pl / sql) and  it is addressed to an Oracle Data Warehouse builted in Windows (and Linux) environment, but the underlying philosophy can be easily adapted to any RDBMS and any Operating System. 

The use of a well-defined Naming Convention has been decisive for the creation of models useful to the automatic generation of all the objects that constituting the system. We can then define MEF as a "Naming Convention-driven" ecosystem.

The focus will be on the Staging Area, because, for me, is one of the main components of a Data Warehouse, the basis (according to the Kimball approach) on which we can build the next components, ie the Dimensions and the Fact tables. The example described is the load of a simple file in csv format.

The MEF ecosystem is downloadable at https://drive.google.com/open?id=0B2dQ0EtjqAOTQzZSaUlyUmxpT1k
The use case is described on Slideshare:
http://www.slideshare.net/jackbim/recipe-14-of-data-warehouse-and-business-intelligence-build-a-staging-area-for-an-oracle-data-warehouse-1?related=1
http://www.slideshare.net/jackbim/recipe-14-build-a-staging-area-for-an-oracle-data-warehouse-1

Now I will describe how to use it with others practical examples.