BODI vs. Netezza stored procedures

dataservices job design
1

Dear Experts,

my project is a reengineering project. we have to enhance and reengineer the current existing system to better performance system.
in the current system oracle is source and oracel is target and by some BODI ETL jobs populates the target oracle tables.
In the to be system oracle target will be replaced with the netezza database. so we have to redevelop those jobs in BODI by changing the target system to netezza. Here the problem is… in the current system there are some oracle procedures calling from the BODI scripts. So how do we proceed in the migration activity. which is better

  1. convert the oracle procedures to netezza procedures or
  2. convert the oracle procedures to BODI jobs.

and even, Is netezza strong enough to provide ETL capabilities as we have been requested to convert all the existing BODI ETL jobs(jobs populating Dimensions and facts) to Netezza stored procedures.

Can anybody please tell me which is the better aproach and
what are the pros and cons of both BODI and Netezza.

Thanks,
srujan

srujan97 (BOB member since 2010-06-22)

2

How can I support BODI rather than converting every thing (Populating dimensions and facts jobs) to netezza procedures.

Comparing BODI with netezza, wat are the BODI capabilities than netezza and what cannot be done in netezza.

Basically my customer doesnt know much about BODI, its features and performance. They are thinking that Netezza procedures perform well compared to BODI jobs.

But I have to convince him that as BODI an ETL tool it is more powerfull than netezza in various ETL capabilities considered.
Can anybody please provide some views on this. Thanks in advance.

Thanks & Regards,
Srujan

srujan97 (BOB member since 2010-06-22)

3

How can you persuade / convince them when you don’t actually know :roll_eyes: ?

As far as I know no ETL tools work really well with Netezza anyway.
Theres very few things that you could do in any ETL tool that you can’t do using raw SQL and stored procedues, it just requires more development effort in Netezza.

Given the unique processing capabilities of Netezza they will probably even execute faster, especially, bearing in mind that an ETL tool does not produce as efficient code as a good human SQL coder.

Mak 1 :uk: (BOB member since 2005-01-06)

4

In my experience Datawarehouse Appliances and/or large MPP environments will always be faster using their own local SQL procedures vs. any external ETL tool - regardless if that’s an ETL or ELT tool.

I have no specific experience with Netezza but with large environments such as HP Neoview. In one specific case, we tested the use of BODI and Informatica vs custom written SQL procedures and the latter were quite a bit faster as the developers were able to open all the toolboxes and really tune the procedures to use all the platform specific sweet spots.

But when debating about ETL solutions - the actual execution performance is only one small variable. You also have to look at development productivity, e.g. the time it takes to develop new pieces of work, the ability to share code/objects among developers and projects, how to establish a standard framework so you do not have to rewrite all your error handling, logging etc when you have to start a new project etc etc.

Then the balance will definitely tip in favour of any ETL tool - but very much in the favour of SAP BO Data Services which is one of the most user friendly ETL tools around.

Unfortunately technical minded people will only look at the use of ETL tools vs handwritten SQL in terms of processing time - but how relevant is a running time of 5 hours vs 6 hours, if it takes you 4 times as long to develop something … and whereby every SQL procecure is a complete bespoke piece of work with no oppertunity to share it across the team?

In our case, the handwritten ETL was certainly faster when processing our daily 20 GB of data. However, some of these procedures were very long - some up to 2500 lines in total (No I’m not joking).

As a result, it took the developers a long time to write the new additions to the Enterprise Data Warehouse and a lot of small, tedious bugs were the result.

So it is not just about speed - but about speed of development, using a standard framework, the ability to share components or re-use them, version control etc. For me, an ETL tool- especially SAP BO Data Services - will always be a much better option than being kneedeep in handwritten code. While the cost of an ETL tool seems huge, if you really do the math and calculate the number of man hours associated with hand written SQL and the lack of business goodwill you generate due to your inability to quickly react to their information needs, then a tool like SAP BO Data Services will earn itself back very quickly.

ErikR :new_zealand: (BOB member since 2007-01-10)

5

Erik,

Some great points made there, especially relevant is the “time to market” piece, IMO.

Cheers,

Mark.

Mak 1 :uk: (BOB member since 2005-01-06)