Can we able to perform all the ETL activities by using VIEWS ??? In our Teradata Environment, developer team is performing ETL activities directly on base tables, which is affecting Teradata performance.
Whenever there is an ETL activity runs on direct base tables, we observe slowness in the performance.
Kindly let me know by using VIEWS can we able to achieve ETL activities ?? If YES then HOW ?? So that it will not affect the system performance.
Yes this can be achieved using view. Provided DB2DB righty have been granted prior to it [in case View and table are in 2 different database].
You can do insert, update and delete operations through views as long as you follow all the "updateable view" rules listed in the manual. However, this will not affect performance positively or negatively.
If you are having performance issues with the ETL processes, you will need to look elsewhere than what object is being accessed. What tool is being used? what interface is it using to Teradata? how is the process implemented - pushdown of the work into the database, pulling a bunch of data to the ETL platform, doing operations one row at a time,...? And a bottleneck analysis - load server, network, database,... .
We have been very successful in switching some of the ETL processing out of a seriel ETL engine and into the parallel efficiency of the Teradata RDBMS. In many instances we have accomplished this utilizing correctly formulated views. I would like to communicate a potential 'gotcha' when adopting this method though... Once ETL starts getting 'pushed down' into the database, it can perform very well due to the MPP architecture of the database. It also has the potential to perform much more poorly than the same processing performed in the ETL engine. Why you ask can this be so?.... Usually the culprit is a lack of statistics. Pushdown creates an level of stats rigor that was not present before pushdown became so popular. Make sure your ETL developers are cognizant of the perils of the lack of stats when executing pushdown sql... or you can enforce by combing through the DBQL, logs which of course is reactionary instead of proactive. A good point of load stats process is also very helpful in avoiding those pesky stale stats situations.
Some do not like doing things like this outside the ETL tool because of lineage, metadata, business rules, etc. issues. I suppose there are pros and cons to both.
Thanks a lot for your responce :-)
My concern was, Our ETL developer team is executing ETL activities in business hours, which is affecting the slowness in the reports execution.
Kindly suggest me in this situation what I need to suggest our ETL developer team.
Usage of the views by ETL team will not help improve your query performance. Usage will only help in avoiding deadlock situations and will allow 2 concurrent queries on a single table. The DBA needs to raise the priority of your user/profile to improve the query performance as Ulrich suggested.