An Idea Exchange for SAS software and services

Comments
by Super User
on ‎02-07-2014 05:37 AM

I am saying yes to all new features that allows for performance tuning.

But, in my vision, there is only one generic table loader transformation, and that transformation could handle the different specifics for different target databases. In this way, when changing a target DB in a solution, you can minimize the need for rebuilding all load ETL-flows.

by PROC Star
on ‎02-07-2014 09:43 AM

I suspect you're not executing the bulk load correctly. I've used it before, and it is much, much faster than the numbers you're presenting.

Tom

by New Contributor bicc
on ‎03-14-2014 10:51 AM

Experimented in the past with this a bit , with custom code and some sybase options 
to allow sybase to read client files (not avoid using sftp)
it is doable on IQ 15 but a standard task would be nicer.

Roland.


LIBNAME SLIB1 SYBASEIQ  ....etc';


%let sytablename=MYSCHEMA.XTABLE
%macro dropsqltable(tn);
%if %sysfunc(exist(&tn)) %then %do;
proc sql;
drop table &tn;
quit;
%end;
%mend dropsqltable;


%dropsqltable(&sytablename);
run;

proc sql;
/*
Create the table in Sybase IQ.
Required Sybase IQ options:
set option allow_read_client_file=on
grant readclientfile to <your account>
*/

create table  &sytablename
                (  BULKLOAD=YES
                BL_USE_PIPE=YES
                )
as select * from &_INPUT where 1=0;
quit;


options bufno=20;

proc append base=MYSCHEMA.XTABLE
     (BULKLOAD=YES
       BL_USE_PIPE=YES
)
data=&_INPUT;
run;

%rcSet(&syserr);
%rcSet(&sqlrc);


/**  Step end Append data (Bulk load) **/

by New Contributor sumanta
on ‎12-04-2014 04:21 AM

Thank you for your input and very sorry for late respond.

@ : I had the vision of building a generic transformation for all jobs, which are required to use the transformation.

Summary of the approach and design is outline as below and let me know your thought on the subject.

1.Business problem:

          We used SAS DI as ETL tool to load the data in Sybase IQ RDBMS. But we don’t have specific table loader transformation for Sybase IQ (SAS DI 4.4). The SAS DI 4.4 has special Table Loader for following RDBMS.

  • DB2 bulk table loader
  • Oracle bulk table loader
  • Teradata Table Loader 

Up to some extend we can manage the Table Loader by providing the BULKLOAD=YES system option and increase the input BUFFER size. But still the Performance is not satisfactory.

Also there is sometime data issue which contains special character which leads to an issue for Bulk load. If we used bulk load with system defined delimiter and if the system defined delimiter contains in the data then it will treat as a different column for the Bulk load. So it’s necessary to provide a user provided delimiter to resolve the issue. Even we can provide these options as a system option in the table loader but won’t work for Update/Insert perfectly (Works only for Append or Insert).

There is another bottleneck in SAS provided Table loader that it don’t do the Update/Insert for initial Load. Even if, the data contains Duplicates on the Business key for initial load then also it will load ASIS. So, next load if any new record comes for the business key then it will throw an error –Multiple rows found for Updates. So we required to handle another process for Duplicates for Initial Load. Same thing happened for incremental load also if the duplicates records doesn’t present in the target Table.

The Updates/insert transformation in SAS DI turning unexpected execution time(7 -10 hours for single Job) for Large Data Mart. We have raised tickets to Sybase IQ,SAS Support regarding the issue, but didn’t get any satisfactory  solution. So, we need a alternate approach to achieve the goal, which trigger an alternate transformation same as SAS DI table loader


2. Solution:

The developed New Transformation will handle above mentioned bottle neck of the SAS provided transformation. The execution time is incomparable with SAS provided transformation. It has the following advance feature as compare to SAS provided transformation.


  • To increase performance I used BULKLOAD option to load the New DATA. (We can used as System option for SAS table loader but it will not work for Update/Insert).For BULKLOAD we required to specify the System delimiter. For that we have an option to put the delimiter.
  • It has all Necessary functionality which should be present in a table loader
    • Auto mapping to source to target
    • Mapping Rename
    • Without mapping to target
    • User provided mapping(Includes all SAS function )
    • Duplicates removed on provided Keys
    • Update/insert
    • Target Tables can be hold Duplicates on business keys
    • Target Tables don’t required Duplicates on business keys
    • Any source to any target(But tested for Sybase IQ to Sybase IQ,SAS work to Sybase IQ, SAS dataset to Sybase IQ ,Sybase ASE to Sybase IQ, Oracle to Sybase IQ)
  • We have an option to load duplicates or unique values on business keys. No any additional transformation required to remove duplicates. As stated above ,if we used the developed transformation then we will get following output as below or it will load the same as SAS TABLE loader(If we don’t opt for remove duplicates).

3.Benefit:

Primarily the developed transformation has following key advantage or benefits.

  • 90% faster data loading. We have experience there are some Jobs which takes around 10-12 hours to complete the data loading. Now same data load (using custom transformation) within 20 minutes.
  • Duplicates records handle for new records or Initial Load.
  • The code used pass through execution but can be used any SAS function in mappings.
  • The used loading logic if different as compare to SAS
  • Easy to use
  • The Solution is dynamic/generic and can be used in any other SAS DI to Sybase IQ data ware house projects

Transformation_DI_ff.jpg

Note:The transformation is ready to use and if anybody interested, I can transfer the transformation as an Object.Just need to import into SAS DI and it will appear in the SAS DI transformation window.

by Senior User asandy1234
on ‎12-02-2016 11:39 AM

Hi Sumanta,

We are having the same issue with SQL Server, the idea of using a custom transformation is the one we are looking for.

It'll be great if we had some information on how to go about it or the code for the custom transformation itself if it can be provided.

Any help is appreciated.

 

Thanks,

 

Anand

Idea Statuses
Top Liked Authors