Question on fact(degenerate) type of fact table relationship

Hi, all,

Could any of you give me some guidance on the fact(degenerate) type of fact tabel relationship between a dimension table and a fact table? Though its definition as: the dimension members are derived from the fact table. How does it really work?

We knwo there are degenerate dimensions which are included in fact tables e.g TransactionID etc in the underlying relational database. What is the idea of dealing with these scenario where we still want to see the degenerate dimensions in the cube dimensions instead of residing meaningless in the cube measure groups? Any best practices or suggestions?

Hope my question is clear for your help and I am again looking forward to hearing from you shortly for your advices.

With best regards,

Yours sincerely,

Hello Helen. When you build a dimension with the fact table as a data source these dimensions will not be different from dimensions with separate tables as data sources.

So you can start the dimension wizard and choose the fact table as a data source and pick the attributes that will be part of the degenerate dimension.

You can only have one degenerate dimension in a cube.

When you put them in a separate table you will have a one-to-one relation with the fact table at the leaf level so that approach is not that common.

Have a look at www.kimballgroup.com or google on degenerate dimensions.

From what I have seen they will take a little longer time to process than one-to-many dimensions.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your advices. So it works for all attributes in the dimension wizard with fact table selected as the data source? E.g. for some attibutes with only a few distinctive values (which we can put them into junk dimensions, then in this case, we dont necessary to create separate junk dimensions for these attributes which are originally residing in the fact table, instead we can use the dimension wizard to create new cube dimension with the fact table as data source)?

Therefore, in this case, we have to define the fact relationship as fact(degenerate) relationship for all cases where the dimension memembers are derived from the fact table?

Hope my question is clear.

With best regards,

Yours sincerely,

|||

Hi Helen.

You can create standard dimensions with attributes from the fact table without marking them as fact table dimension in the dimension usage tab. It also possible to mark one relation between the fact table /measure group and a dimension as a fact table dimension. Both ways will work and if you avoid the fact table relation type(dimension usage) you can make more than one.

Attributes with a few distinct values normally qualifies as standard dimensions and can be placed in a dimension table with a one-to-many relation with the fact table.

Junk or fact table dimensions normally have a one-to-one relation with the fact table.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you very much for your very kind advices and help.

It then sounds like it is better to create standard dimensions with attributes from fact table which will enable more than one relationship types between the fact table and the derived dimension table.

Hopefully I am right.

Thank you again.

With best regards,

Yours sincerely,