Dimension in Data Warehouse
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, LukaszReceived on Thu Apr 13 2006 - 23:02:50 CEST