What is the thought process behind choosing a date as a dimension attribute or a fact table key to a date dimension?

In my specific instance I'm modeling contracts and we want to track the value and square meters of contracts according to:

• when the contract was signed
• when the contract started
• the date of the notice of termination
• terminationdate

Most contracts haven't been terminated so most contracts don't have a date for notice of termination or termination date.

The fact table contains the measures contract value and square meters.

Do these dates make more sense as attributes of the contracts or as keys to a date dimension? We want to be able to query and see the value of the contracts signed or terminated during an arbitrary time period. If I use a time dimension I get all attributes for it like months, quarters, tertials, years and so on which I guess is a advantage.

Would snow flaking the dates make even more sense, keep them as attributes in the contract dimension but relate them to the date dimension?

Molotch,

I think the answer depends on the answer to following questions:

- What information are you gathering in the dimension contract

- next to measures, sqt meters and dates, what is the other info in the fact

- Are there multiple fact lines for one contract?

-If the answer to the last question was yes: are these dates then linked to the fact line, or the contract?

Thank you for answering.

In my first iteration I wanted to put alot of things in the contract dimension. Customer, landlord, property (building), start date, end date, renewal time, contract type, and so forth. But the more I read it seems as if many of the attributes are dimensions of their own or facts. Ie I want a customer dimension, and a landlord dimension, probably a property dimension (building) and a date dimension. So either I use the fact table as the join table between these dimensions and scrap the contract dimension (or use just a smaller amount of attributes). Or I duplicate alot of information in the different dimensions, which seems like the wrong approach.

Lets say I have a customer dimension. Does that mean I shouldn't have a customer attribute in any other dimension. The relation should be modeled through the fact table (factless or not) not as an outrigger attribute or duplicated customer attributes?

It's sort of the same problem as the dates. They can be attributes with plain values, outriggers to a date dimension, or just be dimensions with keys in the fact table. When to choose what?

The fact table should only consists of the measures contract value and square meters, then fk to the different dates, landlord, customer, maybe some sort of organizational dimension (unless I make a region attribute for the landlord dimension). First I planned of having one line per contract, but maybe it's better to have one line per incident and have a dimension for the type of incident instead? Ie lower the grain of the fact table to incident per contract.

• Edited by Molotch Tuesday, January 07, 2014 3:27 PM

Molotch,

I would guess you need a fact table called FactContact and this linked to different dimensions. (Customer, LandLord, Property, Dates,...)

This consist of Measures and your Foreign keys to the mentioned dimensions.

Linking dimensions to each other is no capital crime :) It all depends on how you want to use this. A Property for instance can have a landlord linked to it. (If a property has one landlord and the landlord can have multiple properties)

If hower you want to best performance also foresee the landlord key on the fact. This will make quering my easier and faster. (There are some consiteration when doing this espacially if you are thing of current situation vs historical correct situation)

For date it's the same. You can have dates on dimensions and on fact. on small trick I use with dates is not let SQL generate a key, but use the format YYYYMMDD as the key for a date dimension. This way you can already have date information in your fact or dimension without having to link to date dimension.

• Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Monday, January 13, 2014 7:11 AM
• Marked as answer by Molotch 4 hours 7 minutes ago

It's slowly coming together.

Found this thread about linking a mini dimension as outrigger or through facts, cleared some confusion up, and a tip on how to avoid SCD:s by breaking out attributes into own dimensions and link them through the fact table instead.

http://forum.kimballgroup.com/t54-design-query-on-dimensions

Since my contract and customer/landlord dimensions have the same grain (there's no instance when I can use the contract dimension as a key in the fact table when I can't use cutomer or landlord since it's a one to one relationship) there's really no point in making them outrigger dimensions, just link them through the fact table and be done with it.