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

sql transformation to temporary dataset but only allowing to premanent

Reply
Occasional Contributor
Posts: 10

sql transformation to temporary dataset but only allowing to premanent


Hi,

A few months ago I set up a straightforward process that took two permanent tables (e.g. registered as metadata and stored in library) and used a SQL join transformation.  I then right clicked on the table icon and selected register to create another permanent table with the result of the join.  I would now like to come back and modify the process to put another step in after the join and before loading to premanent dataset. however now when I delete the link and try add in the step (splitter transformation) I am told that "splitter requires a physical table or work table"  Is there a way without rewriting the join transformation that I can modify the existing.

Thanks

Tom

Super User
Posts: 5,424

Re: sql transformation to temporary dataset but only allowing to premanent

I'm not sitting in front of DIS right now, but if I recall right:

  • disconnect the join from the permanent table
  • right click on the transformation, and chose "Add work table"

As a best practice, it's maintain work tables (views) with the transformations, and use a Table Loader transformation for the target table. It makes the flows easier to adjust ;-)

Data never sleeps
Occasional Contributor
Posts: 10

Re: sql transformation to temporary dataset but only allowing to premanent

I can get the work table back following your instructions, thanks.  My inconvenience now is that all the formual I had in he expression columns have gone.  I assume this is because they were mapped to the permanent metadata object.  but before I replace al 600 of them can you think of a way for me to salvage them using DI studio?

Thanks

Tom

Super User
Posts: 5,424

Re: sql transformation to temporary dataset but only allowing to premanent

600? OMG!

Well, if you expressions are without linebreaks, and you are sure that you have the same sort order of the columns, you could just copy and paste the expressions. A bit risky, but could probably save some work...

Data never sleeps
Occasional Contributor
Posts: 10

Re: sql transformation to temporary dataset but only allowing to premanent

Unfortunately there are alot of case statements that when I copy else where go into multiple lines.  Mentally preparing myself to do it now.  Seems like a good task for a friday afternoon.  Thanks for the help though I certainly will always use the table loader going forward.

Thanks

Super User
Posts: 5,424

Re: sql transformation to temporary dataset but only allowing to premanent

Just take a deep breath...

For future considerations, doing ETL and DW is far more easy if the data is transposed, leaving fewer columns to maintain in metadata.

Have nice evening! Smiley Happy

Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 282 views
  • 0 likes
  • 2 in conversation