while doing staging , i.e While coping the data from one data base to
another , It is taking a lot of time for me to pull the data from
production server to local server.
So client is asking me go use Scripts to copy the data from one DB to
another DB. And they suggested me to use Sql Transformation in the
place of look ups.
I have tested the scripts and Sql transformation.
They run very fast and makes me happy
But if I move the code to production will I have any
issues because already I am using one sql transformation and one scrips
and client is asking me to add 2 more scripts and 2 sql transformations.
Are there any disadvantages of using a lot of sql transformations and scripts
Data Services cannot preserve or see any metadata in SQL transformations nor can it check the actual syntax. “View where used” will not give you the source tables for instance. Also if you are using the data lineage feature of SAP Information Steward, it will break the lineage stream from source to target when using SQL transformations.
However, if you do your Data Services Job development / design correctly, there really should not be much of a performance advantage. Most of my Data Flows generate a full INSERT INTO statement - which is the most effective method of parsing the data. Whenever DS-specific operations are required, I generally tend to put these together in the same Data Flow to avoid having to route large volumes of data through the Job Server more than once.
From your post, it is very unclear to me what you are doing or what the required design is like. SQL Transforms are not a replacement for Lookup functions really. At most an Left Outer Join could be used, which is something I do use when both tables are very large. However, care should be taken because an incorrect join may result in duplication of data - which is something that would not happen with a Lookup_Ext() call even though that may result in an unresolved or incorrect lookup, especially when dealing with questionable data quality.
I only use SQL transformations when there really are no alternatives.
In addition, SQL Transforms tend to not have the level of documentation that I prefer for support purposes. It CAN, generally it just does not. So if you do decide to use them save yourself a lot of trouble and over document the heck out of it.
To wit, in the circumstances when I use SQL (mostly for custom SQL in a lookup when I have a multi-table lookup) I tend to build the entire SQL in a script before the data flow and document out the SQL in the script. Example:
# Check the surrogates for every table dynamically.
# To do this we build a union of all tables in the schema that contain surrogate columns.
# Be very careful if you mess with this SQL. Since it is nesting an SQL build
# inside an SQL build the placement of quotation marks and pipes is very
# sensitive and could easily cause you hours of headaches to fix.
$C_SQL_Line = sql('Warehouse',
'select \' select \'\'\' || table_name || \'\'\' as t_name, '||
'\'\'\' || column_name || \'\'\' as c_name, ' ||
'count(*) ' ||
'from dw_manager.\' || table_name || \' ' ||
'where \' || COLUMN_NAME || \' = -1 UNION \' ' ||
'as SQL_CMD ' ||
'from all_tab_columns ' ||
'where owner = \'DWM\' ' ||
'and column_name like \'I_SUR%\' ' ||
'and column_name = {$N_Colm} ' ||
'and table_name = {$N_Loop_Table_Glob}');
I recently had a situation whereby DS could not import source table metadata from a source database (on Oracle 11gR2) despite the DS Service Account and my own account having been granted SELECT privs. It must have been a authorization issue for sure as SQL Transformations DID work just fine. So running a SELECT statement was fine, but somehow DS was unable to retrieve and import the metadata.
Getting to the bottom of this would have taken a long time, especially as this was a large organisation with very sensitive data and one of those situations where there were many layers between the actual DBAs and my team. So basically we were lucky to get that SELECT access to begin with, so to speak. And second, this was a data migration project so our ETL would be “one off” anyway. There were many full migration test cycles in our project but ultimately, come Go-Live, we would only do this once (unless something went really wrong).
So in that scenario I used SQL transforms to source the data into our staging tables. Everything else was done using normal table objects. It was just a matter of “picking the hill you want to die on”. It could have taken us weeks of meetings and discussions to get this resolved, or to get permission to get anything more than the very minimum SELECT privs we’ve had, and it just wasn’t worth it considering that we could get by this way.
Had this been for a more permanent Data Warehouse or Data Integration solution, I certainly would have been pushing to get this done properly. To me SQL transforms are a Band-Aid and I will only agree with their usage within our organisation if there is a very clear reason to do so.
Most places where I see SQL transforms used, is in organisations and companies that have purchased Data Services but did not invest a dime in training and education for their staff. Coming from other ETL tools such as SSIS, people may revert back to SQL transforms because it’s “quicker and easier”. Data Services is a very nice tool to use but, in my opinion, it is vital that people get basic training on how the tool works and how to get the most out of it.
(BOB member since 2007-01-10)
(BOB member since 2005-09-16)
(BOB member since 2007-01-10)