topic Re: SAS DI Query in SAS Data Management

SAS DI Query

Sheeba — Fri, 23 Aug 2013 20:14:27 GMT

I have a table A which contains a column Deal ID.New deal id gets added to Table A every day. I have another table B which has all the details of Deals and Deal ID as well. I need to pull some details of Deals from B into A based on deal id using SAS ETL(DI JOBS).

If I have 100 deals getting added to table A every day,how will i pull the details of this 100 deals from table B using DI studio? Is it possible to store the Deal ID in cache and pull the corresponding data?

Is data cache supported in SAS DI studio?

Appreciate any help on this,

Regards,

Sheeba Swaminathan

Re: SAS DI Query

jakarman — Sat, 24 Aug 2013 13:48:37 GMT

DI is a developpers tool for building jobs (collection of sas code steps) using transformations ( a kind of templates for processing) in a graphical way.

If your question is: is it possible to develop with DI ... answer is yes.

If your question is: how should id the technical detail design? To solve that that is the role of a developer.
Are you missing some helpfull tranasformations? Cooperate with your platform admin, these can be set up and made available as user-transforms.

Re: SAS DI Query

TomKari — Sun, 25 Aug 2013 12:18:24 GMT

A spitball kludge for this would be:

1. Have a copy of table A (call it Aprime)

2. Add your new transactions to table A, in your regular daily processing.

3. To get todays transactions, query A and Aprime for records in A but not in Aprime (different ways to do this).

4. Once you have them, copy A over Aprime.

Not good in many situations (ie high volume). I agree with Jaap...if this is being implemented in DI, it's worth having professional developers deal with it, to find i) the optimal solution and ii) make sure it handles all contingencies.

Tom

Re: SAS DI Query

LinusH — Sun, 25 Aug 2013 18:27:07 GMT

Can you be more specific on how you define a cache in this scenario?

It sounds like you are meaning something DW arhitects refer to as a staging area.

So store the daily data in a staging table (with only today's data).

Join with B to get the details.

Append/Insert Stage.A (with details joined from B?) to a permanent table holding history records.

Also agree with Jaap, have a more exprienced developer/architect lay out guideline for your task.

Re: SAS DI Query

Sheeba — Mon, 26 Aug 2013 16:50:20 GMT

Thanks a lot Jaap. I will try to set up User written transformations

Re: SAS DI Query

Sheeba — Mon, 26 Aug 2013 16:53:03 GMT

Thanks for the suggestion Tom. It clearly provides a plan for the problem. This situation deals with large volume of data. So I will try to find an optimal solution with the help of experts

Re: SAS DI Query

Sheeba — Mon, 26 Aug 2013 16:57:25 GMT

Thanks a lot Linush.

By Cache, I refer to a temporary table which can hold only the latest Deal ID's. Using this temporary table, i am planning to pull the matching records from table B.

From your reply,I believe it is possible to derive that

Re: SAS DI Query

LinusH — Mon, 26 Aug 2013 20:15:14 GMT

A b est practice is to avoid user written transformations if possible. Standard transformations are more easy understand and maintain, especially for "3rd parties".

Since your request seems quite straight forward, my guess is that you could do fine with standard transformations such as SQL Join and Table Loader, potentially with some DB tuning (indexing etc).

Re: SAS DI Query

Sheeba — Mon, 26 Aug 2013 21:32:13 GMT

Thanks a lot for the suggestions Linush.

Re: SAS DI Query

jakarman — Tue, 27 Aug 2013 04:55:22 GMT

SAS(R) Data Integration Studio 4.7: User's Guide (Selecting a Load Technique in the Table Loader)

Re: SAS DI Query

Sheeba — Tue, 27 Aug 2013 16:16:32 GMT

Thanks Jaap.

I will go through this section in the user guide.