Wednesday, March 1, 2017

Importance of creating separate schema for Work in ODI

We had come across an article a long while back on the significance of using a separate schema for work instead of Target schema in ODIExperts blog. We later learned from this as we could clearly see the advantage of doing the same. 

We use OBIEE as our front end tool and we could see that all the work tables of ODI for each target table would be present in the target schema. The more tables in the target schema, the more time it takes for OBIEE to process the import metadata step. Imagine you have 10 tables in your DWH target schema, if you don't define a separate work schema for each table then for one table, W_STUDENT_D you will have the following work tables also, 

C$_W_STUDENT_D, 
I$_W_STUDENT_D,
E$_W_STUDENT_D

This not only makes OBIEE import slow also it takes up table space on the target schema making database management hard. If maintained separately then you can purge all tables in the work schema at once. The worry of accidentally truncating any of your target tables are also eliminated this way. 

Another important step as they discussed is to not use the 'Stage area different from target option', which is done at the interface level. The same can be done at the physical schema in topology level, 

ODI Work Schema.
 

No comments:

Post a Comment