Thread: please ans....questions in DWH

hi,
i have a few questions, can any one answer please.......its urgent....

1. Is the fact table normalised or denormalised, justify?

2. Where do the surrogate key reside, in fact or in dimension tables?

3. If surrogate key reside in dimension table, then how can u identify a unique record in the fact table?

4. Does the fact table contain any primary key?

Originally Posted by manjula_yeruva

hi,
i have a few questions, can any one answer please.......its urgent....

1. Is the fact table normalised or denormalised, justify?

2. Where do the surrogate key reside, in fact or in dimension tables?

3. If surrogate key reside in dimension table, then how can u identify a unique record in the fact table?

4. Does the fact table contain any primary key?

Fact table is surely normalized. (I can't justify please)

For surrogate key, dimension is parent table and fact is the child table. i.e. in dimension table only, sk resides.

With the use of sk only since, we maintain foreign key relation with the dimension table here.

It may not. Directly speaking no. Since it containes only foreign key only.

Originally Posted by vizaik

It may not. Directly speaking no. Since it containes only foreign key only.

Actually it depends upon the type of relationship u use ...identifying or non identifying relationship... in the first case u will get PK and with the later case we get FK.

Sarogate key was the alternate of primary key.

1) The dimension table contain the primary key or sarogate key & the primary key will be unique.This dimension tabe contain the textual data.

2) The fact table contain the foreign key & this key was not unique.This fact table contain the calculation data.The important thing is a fact table required minimum two dimensional tables.

Originally Posted by manjula_yeruva

hi,
i have a few questions, can any one answer please.......its urgent....

1. Is the fact table normalised or denormalised, justify?
ANS-Fact Table always in Normalized form. because Fact Table contails Measures(also called Facts or Calculated Value) and foreign key to Dimentional Tables ok. so measures are directly came from source. source is relational database right.

2. Where do the surrogate key reside, in fact or in dimension tables?
ANS-Surrogate key generated in dimentional table because to identify each row uniquely in dimentional table. i think still you are in confussing i tell you by taking an example let us consider Dept table in that table deptno deptname loc attribute are there ok.suppose take one employee his deptno is 001 deptname is ACCOUNT and Loc is Bangalore this is the situation. what happens the employee job loc changed from Bangalore to mumbai how you can do this one keeping both data available so inorder to identify each row uniquely one extra column is created in Dimentional table using KeyMgtGetNextValue routine we can generate surrogate key clear right.
These surrogate key acts as a foreign key in fact table.

3. If surrogate key reside in dimension table, then how can u identify a unique record in the fact table?
As i told in 2nd answer surrogate key acts as a foreign key in fact table right then there is no necessary to identify row uniquely in fact table.whatever the key generated in dimentional table that goes to fact table as a foreign key.

4. Does the fact table contain any primary key?

As per my knowelde there are no primary key's are available in fact table. clerify

Sorry for my answers, anyways :

1. Is the fact table normalised or denormalised, justify?

Answer: It is recommended to be denormalized (ask ralph kimball )

2. Where do the surrogate key reside, in fact or in dimension tables?

Answer: Surrogate key becomes the primary key of the dimention table, usually coming from a sequence, identity, sequencer etc. but at the same time will also reside in the fact table as the foreign key. Otherwise you could not have a relationship in between.

3. If surrogate key reside in dimension table, then how can u identify a unique record in the fact table?

Answer :
As I mentioned in my answer in no.2, the primary key value of the dimension becomes the foreign key of the fact table. Therefore, in the ETL processing a record is checked in the dimension table getting the primary key and saving it in the fact table as the foreign key.

4. Does the fact table contain any primary key? There is what you call a fact primary key which usually consists or combinations of all foreign keys ( ask kimball again)

Thanks just my two cents buddy.