topic Re: Automatically mapping columns SAS DI in SAS Enterprise Guide

Automatically mapping columns SAS DI

Loko — Fri, 08 Apr 2016 07:56:22 GMT

Dear All,

Is there a method / plug in to automatically mapping a lot of DDS tables with their corresponding sources?

Take into account that variable names is different in DDS tables and sources.

WE are in the process of building our DW having a lot of sources which need to be transposed in DDS tables.

The classic method we know is to create jobs for each DDS table and manually map the fields from the interface (using mapping tab from Table Loader Transofrmation).

Since we have a lot of DDS tables and sources (around 150) manually mapping them is quite hard.

I think we can use User Written Code to map them but this will not generate the mapping interface that the Table Loader Transofrmation does , thus the maintenance of the model becoming hard.

What we are looking for is a method to automatically map them as well as generating the interface mapping from Table Loader Transofrmation.

We use SAS DI 4.901.

10x

Re: Automatically mapping columns SAS DI

LinusH — Fri, 08 Apr 2016 09:45:41 GMT

Hi Loko,

can you please define "automatically".

I know that there are quite a few "mapping" tolls on the market, but non of them (to my knowledge) support generating of SAS DI flows. You could probably do this by updating metadata problematically - but that will probably be much more time consuming that just get started building your flows the standard way.

Having jobs for each DDS table is normal, and 150 is not that large. Usually at least from the loading part perspective, you can usually have 2-3 standard types of loading (SCD, Upsert, append/insert), so you can build some rough templates for that.

What do you mean it's hard? Because it's so many? If the business logic is complicated, you can't definitely not automate this...

Do not use User Written! That's even harder than using standard transformations. If you mainly have 1-1 you just point and click.

Do you have overlapping source systems (like different customers from different systems), you can map the sources to a common stage table, and then have just one DDS load job.

What do you really mean by "What we are looking for is a method to automatically map them as well as generating the interface mapping from Table Loader Transofrmation."?

Perhaps you need to describe more about your challanges, DW architecture etc.

Re: Automatically mapping columns SAS DI

Loko — Fri, 08 Apr 2016 10:08:31 GMT

Hello Linus,

Our flows start with the Extract phase where we load the numerous *.txt into sas tables. This is not difficult and we can easily do it.

After that comes the Transformation phase where we have to map the columns...So we have Table Loader transformation where we manually map the columns from the loaded tables to DDs Tables. This is the difficult part because of the many tables we have to manually map ; that's why i asked if there is a plug-in which can mimic the Table Loader transformation behaviour , thus generating 150 jobs which will load the data.

10x

Re: Automatically mapping columns SAS DI

LinusH — Fri, 08 Apr 2016 12:47:32 GMT

Oh, so you wished for a tool that worked like Table Loader, but for meta data...

As of my first reply, there are no such tool AFAIK.

But if the logic in mapping columns in those jobs are not complex, I still think it's easier to this in DIS rather writing SAS programs, so...

But since there are mapping tools with support for other vendors like Informatica, IBM Data Stage etc it would be a great if SAS (or any other 3rd party vendor) could supply such functionality for DI Studio.