Best Practice for Degenerate Dimensions

[Robert Tooker]

Hi,

Was wondering what people think is best practice for degenerate
dimensions in the bm&m layer, where those dimensions are related to a
single fact table. I can see three options:
1) Leave in logical fact table
2) Push to own dimension table with same physical source
3) Create alias of fact as dimension in physical layer and map to
dimension in bm&m

Number 3 is going to bad for performance as will do a self join for
every query, number 2 could lead to a proliferation of single-use/non-
conformed dimensions, and number 1 feels a bit unnatural with
dimensions in the fact.

Any thoughts?

Robert

[Jit Dutta]

When you have a degenerate fact i.e., is used to calculate metrics as well as display attributes I don't think you need to create an alias of the fact. You should be able to use the same alias used in the logical fact in your logical dim also. There won't be a self-join in the query. This is used quite extensively in OBIApps (eg, Order management module) and I'm currently using the same concept in my custom DW project.

Thanks,

Jit

----------------------------------------
> Date: Wed, 28 Jul 2010 07:24:21 -0700
> Subject: [OBIEE EMG] Best Practice for Degenerate Dimensions
> From: robert...@gmail.com
> To: obiee-enterpri...@googlegroups.com

_________________________________________________________________
The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4

[Stewart Bryson]

I always model degenerate dimensions into a logical dimension. Even when those attributes seem like they relate to only a single fact table today... they may not tomorrow. For instance, what if you next need to deliver a fact table at a snapshot grain that is based on the single fact table... or perhaps a pivoted view of the same table? Also... I don't like to have that "what is a degenerate dimension" conversation with the end user. We model degenerate dimensions for ease and performance... but that doesn't mean we have to deliver it to the end user that way.

See this blog posting by Venkat which goes through the pros of this approach: http://www.rittmanmead.com/2010/01/20/oracle-bi-ee-10-1-3-4-1-modeling-degenerate-dimensions-fact-attributes/

Stewart

[Steve Devine]

I agree that 2 is best for all the reasons Stewart mentioned
particularly future proofing. In the Presentation Layer I often bring
them together again so that the Dummy dimension and fact are displayed
as a single folder.