Ab Initio Best Practices and Useful Tips These are general guidelines ideal for implementing in the development, maintenance and testing of Ab Initio projects. These have been gathered from various sources on the Internet, as well as from experienced Ab Initio developers. Project Access Control – CheckIn, CheckOut 1) Before Checking In a graph, ensure that it has been deployed successfully 2) Inform the ETL Admin before Checking In 3) To get the latest version of the graph, Check Out from EME Data Store 4) Always Check Out from EME Data store to your individual sand box before running a graph 5) In case the graph is not present in the EME Data Store, Check In and run it 6) Ab Initio Sand Box should be created only by the ETL Admin for all authorized users 7) Ensure that User-ID and Password in the EME Settings and the Run Settings are the same, before creating graphs on the server 8) Ensure that a graph is locked before modifying a graph, to prevent any sharing conflicts 9) Try to see that individual graphs are handled by separate users – when a user locks a graph, it prevents other users from modifying it while it is locked 10) Ask the DBA to create any tables needed in the target database – do not create yourself 11) Report all database related activities and issues to the DBA promptly 12) Inform the DBA and get his approval before you modify any table in the target database
Email: info@maxonlinetraining.com Skype-id:max.online9
Contact Us: +1 9404408084/+91 9533837156 www.maxonlinetraining.com
Ab Initio Best Practices and Useful Tips 1) One may encounter errors while running a graph. Maintain error logs for every error you come across. 2) Maintain a consolidated, detailed error sheet with error related information and resolution information for all users. This can be used for reference when facing similar errors later on. 3) In case you have a database error, contact the DBA promptly 4) In all your graphs, ensure you are using the relevant dbc file 5) Always validate a graph before executing it – deploy the graph only after successful validation 6) Run the ab_project_setup.ksh on a regular basis – Contact ETL Admin if you need further details 7) Check whether the test parameters are valid before running an Abinitio graph 8) Save and unlock the graph after implementing the modifications desired
Handling Run Time Errors 1) When testing a graph, contact the person who created it or modified it recently for assistance 2) Contact the ETL Admin if the error encountered relates to Admin settings 3) When you face a problem that you have not encountered and resolved before, look in to the consolidated error sheet to see if it has previously been faced and resolved by any other user 4) Check with online tech forums for further inputs on the error
Documentation Practices 1) Maintain documents regarding all the modifications performed on existing graphs and scripts 2) Maintain ETL design documents for all ab initio graphs created or modified – when changes are made to the existing graphs, the documents should be suitably modified
3) Follow testing rules as per the testing template when testing a graph 4) Document all testing activities performed
Email: info@maxonlinetraining.com Skype-id:max.online9
Contact Us: +1 9404408084/+91 9533837156 www.maxonlinetraining.com
Ab Initio Best Practices and Useful Tips Underlying Tables 1) In all graphs with RDBMS tables as input, ensure that the join condition is on indexed columns or indexes are created on the columns in the join condition – This is very important because if indexes are absent there will be a full table scan affecting performance. Use the Oracle Explain Plan utility before executing a graph to find the execution path of the query. 2) If there are indexes on target table, ensure that they are dropped before running the graph and recreated after the graph is run 3) Try to shift the sorting or aggregating of data to the source tables (assuming the data source is an RDBMS, not a flat file). SQL OrderBy or GroupBy clause is faster than AbInitio because the database server would invariably be more powerful than Ab Initio server (in any case, SQL OrderBy or GroupBy is more efficient than an ETL tool) because Oracle runs the statement in optimal mode. 4) Bitmap indexes may not be created on tables that are updated frequently. Bitmap indexes tend to occupy a large amount of disk space. Create a normal index such as a B-tree instead.
Email: info@maxonlinetraining.com Skype-id:max.online9
Contact Us: +1 9404408084/+91 9533837156 www.maxonlinetraining.com