topic Re: replicate entire table in RDBMS to SAS datamart. in SAS Data Management

replicate entire table in RDBMS to SAS datamart.

sassharp — Thu, 14 Jun 2012 14:46:59 GMT

there is a 500M rows in RDBMS table. Eachday approximately 1000 new rows adds to the 500M rows. Out of 500M rows everyday 0 to 100 records updates with the same old id one or two field contents changes. Assume for 100 rows field contents changed. so now RDBMS contains 500M-100 +1000=500M and 900 rows.

Q) Need to replicate this RDBMS table as a SAS data set?

Re: replicate entire table in RDBMS to SAS datamart.

Doc_Duke — Thu, 14 Jun 2012 15:11:38 GMT

I'm not sure what the issue is. If "replicate" means "copy", then a DATA/SET combination will do it. If you just want to access the data, a LIBNAME to the RDBMS will do that. If you want to replicate the "process" of updating the RDBMS table using SAS code, you've got a lot of work to do, particularly if you are thinking about in-place replacement.

Re: replicate entire table in RDBMS to SAS datamart.

sassharp — Thu, 14 Jun 2012 15:55:14 GMT

Need something like this.

http://www2.sas.com/proceedings/sugi31/157-31.pdf

Re: replicate entire table in RDBMS to SAS datamart.

sassharp — Thu, 14 Jun 2012 16:22:59 GMT

sorry not to mention Doc

Doc.

Lawrence Muhlbaier

Re: replicate entire table in RDBMS to SAS datamart.

shivas — Fri, 15 Jun 2012 04:36:05 GMT

Hi,

Are you looking for something like SCD type 2 Loader/Type 1 columns with version number.This can achieved using SCD type 2 transformation.

Thanks,

Shiva

Re: replicate entire table in RDBMS to SAS datamart.

sassharp — Fri, 15 Jun 2012 13:34:42 GMT

@Shivas

To be simple there is a table 1 in RDBMS. The RDBMS table is dynamic. Means everyday some new rows add to the RDBMS. I need to bring this table to SAS data mart.

For example the RDBMS table maintains customer transactions. For each transaction there is a unique id. Transaction id. amount1 adjusted amount, modified date.

transactionid amount1 adjusted amount modified date

1 10 0 6/6/2012

2 12 0 5/6/2012

3 4 0 4/6/2012

4 2 0 6/13/2012

This is the table status on 6/13/2012

on 06/14/2012 the status of the table is

transactionid amount1 adjusted amount modified date

1 10 0 6/6/2012

2 12 2 6/14/2012

3 4 0 4/6/2012

4 2 0 6/13/2012

5 100 0 6/14/2012

Now when you run this in DI studio you should get the RDMS table in SAS side. with 5 rows and row should be updated in SAS table.

we tried with update modify and delete, append code. code is working fine. Performance is low.

so wanted to try with DI studio.

thanks.

Re: replicate entire table in RDBMS to SAS datamart.

shivas — Fri, 15 Jun 2012 14:24:23 GMT

Hi,

Comm table would be you RDBMS table and SASComm would be SAS Dataset.You need to change the load techique tab in table loader as below to update/Insert(if new record comes it will insert and old record changes it will update..try this).Hope this helps.

Thanks,

Shiva

Re: replicate entire table in RDBMS to SAS datamart.

sassharp — Fri, 15 Jun 2012 15:07:44 GMT

@shivas

Thanks for your reply.

would you follow me here. That way I can send you direct message to you only. Just send me a request to follow me. I am already following you.

thanks.