How to Model Fact table having 1:1 relationship with key Dimension attributes

[snpr01]

hi all,

Here is the scenario:
Dimension attributes and Fact table for that Dimension attributes has one-to-one relationship. These dimension attributes are the key attributes for the purpose of fact table.
The fact will be a factless fact.
Other dimensions such as several Date dimensions, person dimensions etc would join to this fact table.

Is it okay to have just one fact table with all the dimensional attributes in addition to the surrogate keys joining to other dimensions ?

I'm aware that it is not good practice to put dimension attributes (with an exception to degenerate dimensions) on the fact table. But, I'm trying to find proper reasoning for creating a dimension that is 1:1 with the fact table.

In my case, the advantage of having dimension attributes in the fact table are..

1. The same table can be used as both dimension and fact table aliases with same surrogate key in OBI (OBIEE) repository. (I'm aware that this is being specific to OBIEE, but I'm assuming that similarities are going to be there in other BI softwares as well)
2. Why load two tables (dimension and fact) and maintain two seperate ROW_WIDs when dimension and fact are 1:1 (We have ROW_WID (PK) on fact tables as well) ?

I'm trying to justify the reasons for separating the dimension attributes into a different table. Again, is it necessary ? Would there be any future needs that I'm not aware of now?

Any thoughts would be very helpful!

[LAndrews]

Pretty generalized post.

More information is required in order to provide some thoughts.

What is the business process you are modelling? What is the Grain of the Fact? What dimension do you feel is a 1:1 relationship to the fact?

Typically, you do not have a 1:1 relationship between dimension & fact.

[Jeff Smith]

Why does the dimension table have a one to one relationship with the Fact Table?

[hang]

If a single dimension attribute has 1-1 relationship with fact, then it should be a degenerate dimension within the fact table. If the combined attribute set has 1-1 with fact, you should break it up and apply junk dimension concept for low cardinality attributes.

[John Simon]

I've done this, simply because there were a few degenerate dimensions that didn't need to be in the fact table.
The situation was for airline tickets.
The ticket number, tour code, PassengerNameRecord and a few other details were only used for drill through reports and were never aggregated. So it made sense to remove these attributes and include in a dimension that was used only very rarely.

[hang]

Ticket number seems to be a typical example of degenerate dimension. Tour code may not be 1-1 related to fact so it might belong to a separate dimension. PassengerNameRecord could be 1-1, if you don’t bother having a Passenger dimension. However I would incline to have a Passenger dimension for a good BI system to track all the historical passengers. So that’s my idea of breaking up a combined dimension so that you have proper degenerate dimensions and standalone dimensions forming a healthy star schema.

[John Simon]

We had a passenger dimension, but the PassengerNameRecord is a unique value separate to the ticketnumber. The tour codes were usually sparsely populated without a lot of overlap.

As I said, this TicketDetail dimension was only ever used on a drill-through action returning a small number of records. So it made sense to me to remove these from the fact table for performance, and use the TicketDetail dimension when required, without making the fact table unnecessarily wide.

[Jeff Smith]

If the information in the dimension is rarely used, keeping it in it's own dimension may have a performance benefit on the fact table. If it's used a lot, then putting it in it's own dimension will cause a performance hit and may take up additional space if you have to create indexes to improve the performance of the join.

Ask your DBA if there is a benefit from making the fact table narrower. If there is and the info in the dimension is rarely used, go with a seperate dimension. If there is no performance improvement or if the dimension would be frequently joined to the fact, then integrate them.

[snpr01]

Thanks all for valuable input. I am able to model the fact table and would like to run it by you all.

Modeling a fact table for prospective customer leads. This fact table is for maintaining the occurrence of prospective customer lead inquiry. It would be their(lead's) first inquiry with the system. There are no metrics captured at this event. Hence a factless fact table. The fact table would be called as lead inquiry fact table.

There aren't any demographic attributes of the lead captured at the time of inquiry.

The grain would be 'one record per each lead'. Additional dimensions collected are..

Inquiry date (Date dimension),
Inquiry source, type, group, code... (forms a dimension table. Has Natural key that comes from source).
Referral source, type.. (another dimension. Has Natural key)

In addition to the above set of attributes a bunch of Flags and attributes also need to be maintained. These additional fields are also going to be used in the analysis. So, I'm trying to decide where to put these additional attributes..

out of state flag (Y/N values)
medicare flag
medicaid flag
... 4 more flags

5 more attributes that come from different source tables. - Thinking of making a junk dimension table with all possible combinations. (800-900 records)

How to handle the flags?
Should I make ...

• One junk dimension table with 16 rows (4 attributes with 2 values each. all possible combinations of Y and N) ?
  
• One dimension table and call it as Flag dimension that has one column two rows (in addition to surrogate key). And, use aliases of this table for Medicare, Medicaid.. and other flag WIDs?
  
• Or put them on the fact table itself?
  

The number of leads is around 1 million + and growing.

[Jeff Smith]

It really depends on how the elements in the potential junk dimension are used with other facts. if the Medicare and medicaid flags are going to be used with Facts that don;t have the other flags, then it might be wise to put the medicare and medicaid flags in one junk dimension and the other flags in another.

One thing about flags - it's temping to shove the flags in a junk dimension in a way that the columns are Dim_Key, Flag 1, Flag 2, Flag 3, etc. But, flags don't have much meaning in a cube of a report. In addition to the Flags (0/1 fields), I tend to add an Indicator for Yes/No and a descriptor for the flag, such as "Medicare/Not Medicare".

[snpr01]

That helps!

[Sponsored content]