Postgres vs SAS

[A_D]

Hello,

I'm creating some ETL in data integration studio, my manager asked me to use postgres tables instead of SAS tables.

Can someone tell me what is the added value of postgres in SAS di? 

thx

[ChrisBrooks]

They're two entirely different things - Postgres is an object-relational database management system which supports things that SAS doesn't like triggers, user-defined objects stored within the database, a much larger number of native data types etc.

Of course if you have SAS Access for PostgresSQL or the appropriate ODBC driver then you can still run your SAS programs against data held in Postgres giving you the best of both worlds...

[A_D]

Hi ChrisBrooks,

Thx for your answer.

All my source data are in oracle and sybase, I use odbc connection in order to create Librairies.

Can I bypass the Postgres tables and use only SAS views/tables in ETL?

Thanks 

[LinusH]

Not sure I agree to that description. In this context I would see PostgreSQL as a regular RDBMS.
Question if you can get a more detailed requirement than that.
Obviously you could easily switch so that your target tables reside in PostgreSQL. Question is what kind if usage this data will have. This is a major architectural decision.

[JuanS_OCS · Accepted solution]

Agreed with @LinusH,

probably the reason for that kind of decision is something as those tables/data in PostgreSQL would be used in another system, non-SAS.

Here my considerations:

- If you have your data origins in other data sources, as soon as you will make any query/join, SAS will bring all the data from those data sources into temporary SAS tables (SASWORK) and then make the operation/sql statement/data step. Hence, I recommend to ensure subsets from each data source are queried and then apply the transformation over those subsets.

- If you have ODBC connections, most probably you cannot use in-database operations (pass-through and such), but that is something more involved on this topic may answer you with more exact information.

- If you want to use the in-database performance features, you would need change in your SAS license the SAS/ACCESS to ODBC by SAS/ACCESS to (your database) and change your code/metadata library definition a bit to send the query to use the performance features.

[A_D]

Hi JuanS_OCS,

Yes, I think that the postgres tables will be used by an other tool outside SAS. 

Thanks for your answer.

[AhmedAccordBGro]

Hi,

same case here, i got ODBC library working fine, I could import the postgres table metadata, but i cannot open any of them, it's giving error "ERROR: CLI describe error: E" all the time.

Any suggestions?