SAS DI Query

[Sheeba]

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

[jakarman · Accepted solution]

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.    

[Sheeba]

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

[LinusH]

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).

[Sheeba]

Thanks a lot for the suggestions Linush.

[jakarman]

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

[Sheeba]

Thanks Jaap.

I will go through this section in the user guide.

[TomKari]

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

[Sheeba]

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

[LinusH]

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.

[Sheeba]

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