Dimension in Data Warehouse

From: Lukasz <lukasz.koperniak_at_gmail.com>
Date: 13 Apr 2006 14:02:50 -0700
Message-ID: <1144962170.444208.248970_at_z34g2000cwc.googlegroups.com>



Hi,

I was thinking about creating two relationship between one dimension and fact table on two different levels of hierarchy. Lets imagine that we have dimension with 3 levels: level1, level2, level3 and our fact is in relationship with this dimension but one relationship is connected to level3 and second on level2.

Oracle propose to create one dimension with two hierarchy: h1: level1->level2->level3
h2: level1->level2
and create relationship with this dimension on two different levels of detail. We need to create relationship on non PK column. In my opinion it is not proper modeling techniques, but Oracle suggest not to create snow flake with is necessary.

In my opinion whe should create two dimensions: dim1: level1->level2 and dim2: level3 (dim1 is in relationship with dim2)
It's clear in dimension definition (we clearly know what is dimension element, and it has one level of detail) and in creating relationships with fact table.

I was talking with my friends about this and I think I convinced them. What do You think about this? Do You think it's permitted to create relationship on different levels of detail? The question is: can we create relationship on non PK column? Oracle let do this because Oracle let create unique key with null values.
I've read in SQL standard that unique key should be not null but it can be nullable, and that relationship should be on PK column but it can be on non PK column. What do You think about this?

-- 
Regards,
Lukasz
Received on Thu Apr 13 2006 - 23:02:50 CEST

Original text of this message