Using SAS views in ETL processes

[juanvg1972]

Hi,

I have to made some ETL process in SAS, typical process in which some intermediate tables were created:

input file -> (read) -> temp1 -> (transform) -> temp2 -> ........ -> tempN -> (load) -> database

Are sas views suitable in the transform steps??. I think it can reduce I/O, but what about CPU time?

I woud like to know pros and cons of using views in that type of processes.

Thanks in advance,

[LinusH]

Usually I/O is the bottleneck that you need to worry about. There is no real truth here, so many different use cases. So start with views and evaluate your CPU consumption as you go.

[Patrick]

The "problem" with views is that it combines all the execution into the node where you actually create a physical table. That makes it sometimes much harder to debug in case of error.

Views can help to improve performance but if nesting too many views then performance can also decrease. You basically need to test what performs better.

Using DI I normally create tables and test step by step. If I need to improve performance then I might change some of the physical tables to views and look what impact this makes on performance.

[Kurt_Bremser]

If you use a view to extract a subset of data (vertical and/or horizontal) from a dataset, and use that three times, you will read the whole sourcedata three times. If instead you create a smaller (temporary?) dataset once and use that three times, you will be more efficient. This will become even more evident if the view contains a join.