BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sheeba
Lapis Lazuli | Level 10
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
1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

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.    

---->-- ja karman --<-----

View solution in original post

10 REPLIES 10
jakarman
Barite | Level 11

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.    

---->-- ja karman --<-----
Sheeba
Lapis Lazuli | Level 10

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

LinusH
Tourmaline | Level 20

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

Data never sleeps
Sheeba
Lapis Lazuli | Level 10

Thanks a lot for the suggestions Linush.

jakarman
Barite | Level 11

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

---->-- ja karman --<-----
Sheeba
Lapis Lazuli | Level 10

Thanks Jaap.

I will go through this section in the user guide.

TomKari
Onyx | Level 15

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
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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.

Data never sleeps
Sheeba
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 1542 views
  • 7 likes
  • 4 in conversation