August 16, 2011 at 2:36 am
Hi
I am working as Jr. SQL DBA, I want to know that, the learning Informatica is useful for me:-). means, Is Informatica is useful for SQL DBA?
Help will be appreciated
Ali
MCTS SQL Server2k8
August 16, 2011 at 3:14 am
All skills are useful somewhere. As I've never heard of the company or its software it wouldnt be useful to me nor at any of the companies I've worked at.
Looking at what they offer, I'd guess its a pretty specific skill so unless you intend on using it at your current company I'd probably say its not a huge requirement to being an excellent DBA. Only invest as much time as is needed by your employer in my opinion.
August 16, 2011 at 3:54 am
thanks,
MysteryJimbo
I have got the information about it by a freing, Who is working on ETL projects. the informatica site is http://www.informatica.com/Pages/index.aspx
Ali
MCTS SQL Server2k8
August 16, 2011 at 8:40 am
Informatica is very popular in a lot of high-end ETL projects. It's often touted as one of the top performers in terms of functionality and performance.
That said - it's also incredibly expensive, so it's not intended to be a "once in a while" type of commitment. An org committing to Informatica is usually going to be committing several hundred thousand $'s in licensing alone, nevermind the hardware costs, above and beoyond the DB hardware and licensing: so they will be rather committed to using it and using it a LOT.
So - in short, yes - it's useful to know, but I don't know that most DBA's would need to know or care, since Informatica would have its own dedicated team etc... At best, one of the DBA's might be tasked to work with the Informatica team, to help tweak performance, etc...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 17, 2011 at 1:00 pm
Matt Miller (#4) (8/16/2011)
Informatica is very popular in a lot of high-end ETL projects. It's often touted as one of the top performers in terms of functionality and performance.
I agree that high performance by Informatica is the perception, but is it the reality when used with SQL Server? The default behavior (and maybe the only behavior) for Informatica is to do row-by-row INSERT operations. I did a little research to try to find bulk copy capability in Informatica, but I came up empty. I do not use Informatica myself, but I'm very interested in how others use it with SQL Server. The row-by-row INSERT operations are a killer for big ETL processes.
August 17, 2011 at 1:53 pm
Well the short answer is - its primary purpose isn't speed copies "as is" of data. There is a way to perform bulk-loading operations , but it's an awfully expensive tool if that's your primary goal.
What it IS good at is to do a lot of transformation type work in memory, pipelining records through complex business logic, and them "flushing" the results to database structures at the end. So - high-end data cleansing, conforming data or prepping it to load into another form (often cubes, or tools like SAP).
It's fairly similar in concept to how SSIS operates in that you express what happens in terms of what happens to a SINGLE record. The persistence aspect however works more like a streaming process than a pure "row by row" process (it "commits" the changes in chunks). That said - it's like a *paid* version of SSIS, so it tends to do what SSIS can do a bit faster, with better addon tools, etc... Of course you'd expect a tool costing a LOT to do more than a free one, so that should be no surprise.
It's a matter of using the right tool for the right job.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 17, 2011 at 2:02 pm
Ali Tailor (8/16/2011)
I am working as Jr. SQL DBA, I want to know that, the learning Informatica is useful for me:-). means, Is Informatica is useful for SQL DBA?
It depends...
- are you working or planning to work in a shop that uses or will use Informatica?
- are you planning to get involved in designing/implementing ETL mappings?
If your answers are YES/YES then it is usefull for you.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 17, 2011 at 2:31 pm
Matt Miller (#4) (8/17/2011)
Well the short answer is - its primary purpose isn't speed copies "as is" of data. There is a way to perform bulk-loading operations , but it's an awfully expensive tool if that's your primary goal.What it IS good at is to do a lot of transformation type work in memory, pipelining records through complex business logic, and them "flushing" the results to database structures at the end. So - high-end data cleansing, conforming data or prepping it to load into another form (often cubes, or tools like SAP).
It's fairly similar in concept to how SSIS operates in that you express what happens in terms of what happens to a SINGLE record. The persistence aspect however works more like a streaming process than a pure "row by row" process (it "commits" the changes in chunks). That said - it's like a *paid* version of SSIS, so it tends to do what SSIS can do a bit faster, with better addon tools, etc... Of course you'd expect a tool costing a LOT to do more than a free one, so that should be no surprise.
It's a matter of using the right tool for the right job.
If Informatica is touted as being a high-performance ETL tool then I think it should be fast at something. I could be mistaken, but I think it does an INSERT statement for every row. I think it would be much more efficient if it used the .NET SQLBulkCopy method, like SSIS does. I understand Informatica is a generic tool, but what does it really offer for the money? Most "transformations" can be done quite nicely with plain T-SQL.
August 17, 2011 at 2:51 pm
BW_Toro (8/17/2011)If Informatica is touted as being a high-performance ETL tool then I think it should be fast at something.
In this context "performance" is not our day-by-day performance but the performance gains in having a single ETL tool to deal with a variety of data sources.
Let's say you have your Data Warehouse based on Teradata and datasources coming from Oracle, SQL Server and DB2 just to mention a few plus some csv files coming from nobody-knows-where. In this scenario Informatica provides the ability to deal with all of them in a consistent way therefore your ETL team has to deal with a single technology no matter the RDBMS (or other) datasource/data warehouse you have around.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 17, 2011 at 5:54 pm
BW_Toro (8/17/2011)
Matt Miller (#4) (8/17/2011)
Well the short answer is - its primary purpose isn't speed copies "as is" of data. There is a way to perform bulk-loading operations , but it's an awfully expensive tool if that's your primary goal.What it IS good at is to do a lot of transformation type work in memory, pipelining records through complex business logic, and them "flushing" the results to database structures at the end. So - high-end data cleansing, conforming data or prepping it to load into another form (often cubes, or tools like SAP).
It's fairly similar in concept to how SSIS operates in that you express what happens in terms of what happens to a SINGLE record. The persistence aspect however works more like a streaming process than a pure "row by row" process (it "commits" the changes in chunks). That said - it's like a *paid* version of SSIS, so it tends to do what SSIS can do a bit faster, with better addon tools, etc... Of course you'd expect a tool costing a LOT to do more than a free one, so that should be no surprise.
It's a matter of using the right tool for the right job.
If Informatica is touted as being a high-performance ETL tool then I think it should be fast at something. I could be mistaken, but I think it does an INSERT statement for every row. I think it would be much more efficient if it used the .NET SQLBulkCopy method, like SSIS does. I understand Informatica is a generic tool, but what does it really offer for the money? Most "transformations" can be done quite nicely with plain T-SQL.
Don't read me wrong - it IS fast, it gains a LOT of efficiencies in those complex processes like I mentioned. And yes you can set a session to load in bulk as long as the process lends itself to it(look at your load types under session properties). That said - nothing will ever beat native bulk copy if all you're doing is a native bulk copy.
And once again - if all you're ever doing is the straightforward stuff - your CIO will not likely buy into the 250K license + 100K in hardware it would take to have an entry level Informatica server.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 18, 2011 at 7:25 am
Matt Miller (#4) (8/17/2011)
Don't read me wrong - it IS fast, it gains a LOT of efficiencies in those complex processes like I mentioned. And yes you can set a session to load in bulk as long as the process lends itself to it(look at your load types under session properties). That said - nothing will ever beat native bulk copy if all you're doing is a native bulk copy.
And once again - if all you're ever doing is the straightforward stuff - your CIO will not likely buy into the 250K license + 100K in hardware it would take to have an entry level Informatica server.
Again, I'm not an Informatica user myself (nor an ETL developer), so I could be mistaken...
I have read about a bulk load option for Oracle, but not for SQL Server. If there's a bulk load option for SQL Server we would definitely like to use it in my current environment.
If my current environment is any indication...
I think there's a tendancy to use Informatica for too many things, perhaps to justify the expenditure after purchase. If it's used for "straightforward stuff" then you suffer the very slow INSERT behavior without any benefit of performance on "complex processes" because there are none.
I understand the appeal of using a single tool to handle a variety of data sources, but Informatica is certainly not alone in that capability. What convinces organizations to invest $250K in a license for this particular tool? This is a SQL Server forum, so I'm assuming everybody here has access to the tools from Microsoft. I would like to understand the compelling benefits of Informatica so I can help it be used more effectively in my current environment.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply