RE: What type of Indexing mechanism do we need to use for a typical datawarehouse
On the fact table it is best to use bitmap indexes. Dimension tables can use bitmap and/or the other types of clustered/non-clustered, unique/non-unique indexes.
To my knowledge, SQLServer does not support bitmap indexes. Only Oracle supports bitmaps.
RE: What type of Indexing mechanism do we need to use for a typical datawarehouse
It generally depends upon the data which u hav ein table if u have less distinct values in particular column its always that u built up bit map index... rather that other one on dimension tables generally we have indexes...
RE: What type of Indexing mechanism do we need to use ...
Space requirements for indexes in a warehouse are often significantly larger than the space needed to store the data, especially for the fact table and particularly if the indexes are B*trees.Hence, you may want to keep indexing on the fact table to a minimum. Typically, you may have one or two concatenated B*tree indexes on the fact table; however, most of your indexes should be bitmap indexes. Bitmap indexes on the foreign key columns on the fact table are often useful for star query transformation. The maintenance window will also dictate whether you use partitioned indexes, which can be faster and easier to maintain. Bitmap indexes also take up much less space than B*tree indexes and so should be preferred. On the other hand, dimension tables are much smaller compared with the fact table and could be indexed much more extensively. Any column of the dimension table that is frequently used in selections or is a level in a dimension object is a good candidate for indexing.
