Star transformation for Oracle data warehousing

[Guest_imported]

I am in the process of creating a large enterprise DW (500GB+, growing to ~6TB), which will be on a 4-way IBM M80 with 8GB RAM. I have a question on Oracle star transformation usage:

· From what I have been able to research, the Cost Based Optimizer will only use star transformation if bitmap indexes exist on the fact table for all dimension foreign keys participating in the predicate of the query. I have come across a data mart where several dimensions are of very high cardinality compared to the fact table, resulting in 10,000s of bitmaps for the index and thus a very large bitmap index (excluding the effects of the index compression). What kind of compression is possible (I know it will vary based on sparsity, so assume 100,000 different values and an even distribution of values amongst the bitmaps in the index)? Also, at what point do I abandon star transformation because I would need to maintain too large of a bitmap index, or too many fairly large bitmap indexes?

Thanks

 

[Guest_imported]

It might be time to look to MPP based technologies/architectures. Sounds like your environment is getting too cumbersome to maintain and you're having scalability & managability issues.

I recommend reading this document from Gartner on teradatalibrary.com:

http://www.teradatalibrary.com/pdf/ar1505.pdf