SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS DI Query

Accepted Solution Solved
Reply
Regular Contributor
Posts: 162
Accepted Solution

SAS DI Query

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

Accepted Solutions
Solution
‎08-24-2013 09:48 AM
Valued Guide
Posts: 3,208

Re: SAS DI Query

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


All Replies
Solution
‎08-24-2013 09:48 AM
Valued Guide
Posts: 3,208

Re: SAS DI Query

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 --<-----
Regular Contributor
Posts: 162

Re: SAS DI Query

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

Super User
Posts: 5,257

Re: SAS DI Query

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
Regular Contributor
Posts: 162

Re: SAS DI Query

Thanks a lot for the suggestions Linush.

Valued Guide
Posts: 3,208

Re: SAS DI Query

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

---->-- ja karman --<-----
Regular Contributor
Posts: 162

Re: SAS DI Query

Thanks Jaap.

I will go through this section in the user guide.

PROC Star
Posts: 1,093

Re: SAS DI Query

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

Regular Contributor
Posts: 162

Re: SAS DI Query

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

Super User
Posts: 5,257

Re: SAS DI Query

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
Regular Contributor
Posts: 162

Re: SAS DI Query

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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