Star Transformation

Submitted by subrata.dass on Tue, 2011-08-23 04:42
AttachmentSize
Microsoft Office document icon Star Transformation White Paper.doc987.5 KB
articles: 
Warehousing

Star Transformation in Siebel Analytics (OBIEE)

(Subrata Dass)


Introduction

Star Transformation is a join method mostly used in Data Warehousing Environments to fine tune typical query performance. It is of utmost importance in DB environments where reporting tools like Siebel Analytics (OBIEE) is in use. It can also be used for other tools such as Business Objects. The conventional join mechanisms that the star transformation seeks to supersede are Hash join, nested-loops and sort merge join.

This paper shows how to take full advantage of this RDBMS feature. Another myth it dispels is the fact that implementation of the Star Query is not possible without creating a physical primary key foreign key dependency between the fact and the dimension tables

All the necessary database parameters and other settings required for the set up of this feature have been highlighted in this paper. Parts of this paper which include the understanding of Star Transformation concept have been influenced by the paper Supercharging Star Transformations by Jeff Maresh in particular and various other Oracle resources mentioned in the References section

All the results in this paper were produced on Oracle 10.2.0.4 64 Bit

A white paper on this subject written by me is as below

»

Comments

Submitted by John Watson on Sun, 2011-09-18 10:01.

Hi - your paper is an interesting read, but I think there are two mistakes.

First:

In the paper you say "An extra index is created on the column which is the logical primary key of the fact table" and then give this code:
create bitmap index pk_f_idx on w_syndd_ids_f_bkp(row_wid);
You are creating a bitmap index on the primary key of your fact table. This is 100% wrong. It is impossible to enforce a primary key constraint with a bitmap index, and you can't index the column again with a B*Tree index. So your fact table cannot have a primary key constraint. Moreover, using a bitmap index on a unique column is exactly the opposite of when bitmap indexes will be efficient. Under what circumstances do you think this bitmap index would ever be superior to a B*Tree index? You need to create a B*Tree index on your primary key column, and define a primary key constraint on it.

Second:

You say that star transformation can work without defining foreign key constraints. This is not always true. Foreign key constraints will improve the chance of getting better execution plans. Without this constraint information, there will be circumstances where the optimizer cannot do a star transformation, because it cannot be certain that all rows will be included if it does. Think of anti-joins, or any logic involving NOT EQUAL TO. I can't test this on your schema because you didn't give the full DDL, but I think you need to check this out and prove to yourself that foreign constraints will give better results, typically by reducing the necessity for table or partition scans.

To conclude, it is an interesting paper, but these points need to be corrected to increase its value.

»

Submitted by subrata.dass on Wed, 2011-11-23 02:59.

Hi John

Thanks for taking the time to read through my posts and thanks for your kinds words. The entire point of my paper was to prove that Star Transformation can take place without integrity constraints. I understand that having a bitmap index in a unique column would traditionally not make sense , but it does have a better chance of making an index join or bitmap operations such as "bitmap or" and "bitmap and" and unless a "BITMAP CONVERSION FROM ROWIDS" takes place it is a better option for index joins or bitmap operations. Also Oracle itself states that there should be a bitmap index on each join column and too few bitmap index can hamper star transformation. I did try this approach in a few other projects with good success. Please refer to the following oracle link to read more about this http://docs.oracle.com/cd/B19306_01/server.102/b14223/schemas.htm.

Thanks again

Subrata

»

Submitted by subrata.dass on Wed, 2011-11-23 03:44.

Another confirmation is that we did not create primary key constraints, we actually dropped the constraints and created bitmap indexes in their places, as has been rightly said by you we cannot have bitmap indexes and primary keys at the same time. We treated them only as logical primary keys (that is we knew which columns were supposed to be primary keys, but these columns did not have physical constraints on them)

Thanks
Subrata :)

»