topic Re: Using SAS views in ETL processes in SAS Programming

Using SAS views in ETL processes

juanvg1972 — Mon, 29 Jun 2015 09:17:37 GMT

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,

Re: Using SAS views in ETL processes

LinusH — Mon, 29 Jun 2015 10:39:01 GMT

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.

Re: Using SAS views in ETL processes

Patrick — Mon, 29 Jun 2015 10:39:25 GMT

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.

Re: Using SAS views in ETL processes

Kurt_Bremser — Mon, 29 Jun 2015 11:50:20 GMT

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.