Should Degenerate dimensions be part of facts in business view

[bloggerman]

I have some degenerate dimensions in my facts. Should i show those degenerate dimensions as part of the fact in the business view or should i create a query subject dedicated to only the dimensional information in addition to the fact.

This confusion because we have the concept of true facts and true dimensions in cognos.

[jive]

The best answer I could give you it's not from me it's from wikipedia and I think it's really appropriate in this case:
wikipedia said:
The decision to use degenerate dimensions is often based on the desire to provide a direct reference back to a transactional system without the overhead of maintaining a separate dimension table.
Wikipedia end:
-----
As I can read: if you need that degenerate dimension to produce report you didn't have to choice to made it available for the report author. To make a query subject or it's more relate to the clarity of the model you made. If I have to do the same I will do a query subject to be sure everything it's OK.

Thanks.

[bloggerman]

The descriptive information in the fact is at the transactional level i.e. each row has its own probably unique description. So, there is a 1-1 relation between fact amounts and descriptions. So, in such a case i was wondering what would be the relation between the two query subjects. If i have 1..1 relation than there would still be amiguity since the fact is also at the 1 end of the cardinality. Thanks.

[jive]

Hi,

If you build those description in dimension, that's mean you will be able to analyse your fact by that dimension. If it's one=one it's also mean if you used that dimension you will have list or crosstab with all the line include in your fact table, because as you say, it's one=one.

I think if there is possibility to regroup those descriptions under a common descriptive like for example:
Descriptive who have 'by sale department' should be under "sale department claim" or something else. It will be more "normal".

The one on one it's not a problem by itself it's the usage you do. Most of the time we want to aggregate tons of facts to total, average, median or else but if you used that degenerate dimension and the relation is one on one you lost most of the functionality in Cognos.

Here in the project I work we have some tables related one=one to the fact , but those tables are only used for specifics query. We store more sensible,personal, confidential information in those table so If a person ask for a report and  they have the permission to see those in formations we will add the information they need.

[cognostechie]

Bloggerman -

Cognos recommends True Facts and True Dimensions only when you are using the regular scenario for modelling. What I mean by regular is that there are seperate Dimension tables and Fact Tables. In that case, the Fact tables usually have ID's/Codes to join to the Dimension table, not the Dimension information itself.

If your Fact table already has the Dimension information (Ex: Customer Name, County Name etc), there is no need to seperate it into True Dimensions or True Facts. You can definately do so if you
want to do it only for presenting it that way to the users.

[MFGF]

Leaving degenerate dimension items in a "fact" query subject in your model can result in incorrect aggregation in multi-fact stitch queries, so you should probably avoid doing this if you have multiple linked stars in your model.

MF.

[cognostechie]

Yup ! I forgot about that.

Maybe you should seperate the Dimension information in another Query Subject and group the dimension information so that you have only one row for one Dimension value. Join to the Fact Query Subject with 1:N cardinality basis.

[dsds]

I came across this thread while looking for the relevant...What if i have two tables. Each table has facts and dimensional data...and i need to join them by 1..1...do we then need to separate into true facts and true dimensions...My guess is no

[RobsWalker68]

Hi,

The decision to seperate the tables really depends on your modelling philosophy. If you are looking to implement a star schema based model with multiple facts and conformed dimensions then yes you should split facts and dimensions otherwise the concept doesn't work.

As an aside on degenerate dimensions generally if the grain of your fact table is at the child level but it also holds a parent key e.g. invoice line fact that holds the parent invoice number then yes this should remain as a degenerate key within the fact table and not physically implemented as a seperate dimension.  If you had a billion row fact table you don't want to create a near billion row dimension table as well. Obviously, if the degenerate key is large such as a document url then you might consider a seperate dimension.

When implemented within Cognos should you be in a multi fact scenario based on the degenerate dimension then create a virtual degenerate dimension to conform the facts. 

Below is a useful kimball design tip on the use of degenerate dimensions.

http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT46AnotherLook.pdf

Cheers

Rob

[cognostechie]

I agree with Rob. Moreover, how data is agrregated does not depend only on the dimension information present in the Fact table but rather on how and whether the determinants have been set. The join cardinality and determinants work together to determine how the SQL should be generated for the rollup.

I have always left the degenerate dimensions and junk dimensions in the Fact table itself because creating a seperate dimension for that will attract one more join unneccacarily and slow down the query. Moreover, by leaving them in the Fact table, you can use them for slicing and dicing data and also for grouping data easily. It will yield correct results in multi-fact queries if you have the determinants set correctly because it will not start summing up the data based on degenerate keys if something else makes the data uinque.