What is the use of bitmap indexes in Informatica?

Question

In the next few weeks, my company will be engaging multiple vendors to establish a choice for a common global ETL tool - not necessarily one that can't be broken from, but just where our license investment will go to consolidate those costs. Two of the major players are Talend and Informatica, with others that are unimportant for the sake of this question.

My experience is in Talend's Enterprise ETL tool, Talend Integration Suite, which also has many standard integration capabilities. Aside from all of the obvious pros and cons between the two such as cost, availability of knowledgeable resources, etc. my question is specific to the development language knowledge required.

I'd like some feedback on the Informatica option. I can state from experience that to use Talend effectively, and reach beyond it's pre-programmed bounds (which happens pretty often), you need to have a decent background in Java. And in this case, I mean Java directly - you can literally use Java if you need to add-on functionality, not a "special" version of Java. What is the situation with Informatica? Is there a particular extension language you can use? Is it proprietary, but perhaps closely related to a mainstream language? What is the likelihood of needing to use it for any jobs of consequence?

This is only one factor I'm going to use in evaluating the space, but I need to get a little more up to speed on this aspect of Informatica before I feel fully informed. Thanks for your answers!

Answer 1

Bitmap indexes are widely used in data warehousing environments. The environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transactions. For such applications, bitmap indexing provides:
• Reduced response time for large classes of ad hoc queries.
• Reduced storage requirements compared to other indexing techniques.
• Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory.
• Efficient maintenance during parallel DML and loads.
Traditional B-tree index can be prohibitively expensive in terms of disk space because the indexes can be several times larger than the data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table.
Bit Map Indexes are most effective for queries that contain multiple conditions in the WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.
Bit Map Indexes are primarily intended for data warehousing applications where users query the data rather than update it. They are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data.
Bit Map Indexes are easier to create and drop
Bit Map Indexes (BMI) are generally used for data with low cardinality (no of unique recs) as many column in DWH is repeated for example gender either M or F this type of data supports BMI