topic Re: sql transformation to temporary dataset but only allowing to premanent in SAS Data Management

sql transformation to temporary dataset but only allowing to premanent

qwererty — Thu, 13 Jun 2013 09:28:22 GMT

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

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

LinusH — Thu, 13 Jun 2013 10:31:11 GMT

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 😉

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

qwererty — Thu, 13 Jun 2013 11:45:00 GMT

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

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

LinusH — Fri, 14 Jun 2013 13:00:39 GMT

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

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

qwererty — Fri, 14 Jun 2013 13:04:16 GMT

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

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

LinusH — Fri, 14 Jun 2013 13:09:47 GMT

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!