Key Assignment in a Calendar Table that Has Duplicate Dates

Question

Post reply

Key Assignment in a Calendar Table that Has Duplicate Dates

dj1202

SSCommitted

Points: 1829
More actions
June 15, 2012 at 1:46 pm

#262411

Hello all,
I am building a few tables (dimensions and 1 fact table). So far I have a Products table, Customers table, Employees table, and a Calendar table, as my dimensions essentially, each with their own primary key (except Calendar table). Then I have a Sales table that references the primary keys of all the other dimension tables, as foreign keys, and has its InvoiceNumber column set as the Primary Key. The problem I am having is assigning the Primary Key in the Calendar table. There are duplicate dates (each correspond with a unique invoice number). Therefore, it will not let me assign. Any suggestions on how one should approach this?
Thanks in advance!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

dj1202 (6/15/2012)
Hello all,
There are duplicate dates (each correspond with a unique invoice number).

You've answered your own question right here. Date, Invoice Number

Although, a date dimension doesn't normally include duplicates. I believe it would include singular values and a surrogate key. This key is then linked to the fact data.

Thats how I would do it, but I could be wrong.

dj1202 SSCommitted Points: 1829 More actions · Answer 2

Thank you MysteryJimbo. I don't know why I assumed all dimensions should have a primary key. I went ahead and assigned the InvoiceNumber column as a foreign key that referenced the primary key in the Fact table. I guess I have a pesky habit of second guessing myself.:-D Thanks a bunch.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

I'm hoping that I'm reading this incorrectly. Why would you put invoice numbers in a "Calendar" table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

dj1202 (6/15/2012)
There are duplicate dates (each correspond with a unique invoice number). Therefore, it will not let me assign. Any suggestions on how one should approach this?

I'd have to go with Jeff on this one... the Fact to Dimension seems laid out wrong.

The Calendar Dimension, in how I usually set them up, should be keyed off the date which is stored in the Fact table and then you dimension out anything else you'd need (Holiday, it's a Wednesday, Quarter 2, whatever). This way the date stays with the fact (INT vs. INT, what's the diff) and you dimension any supporting data off.

What decisions did you have to make that had you desire to ship the date and invoice number off to the dimension?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

dj1202 SSCommitted Points: 1829 More actions · Answer 5

Evil Kraig F (6/15/2012)
dj1202 (6/15/2012)
There are duplicate dates (each correspond with a unique invoice number). Therefore, it will not let me assign. Any suggestions on how one should approach this?
I'd have to go with Jeff on this one... the Fact to Dimension seems laid out wrong.
The Calendar Dimension, in how I usually set them up, should be keyed off the date which is stored in the Fact table and then you dimension out anything else you'd need (Holiday, it's a Wednesday, Quarter 2, whatever). This way the date stays with the fact (INT vs. INT, what's the diff) and you dimension any supporting data off.
What decisions did you have to make that had you desire to ship the date and invoice number off to the dimension?

Thanks guys. I realized that the layout was all sorts of messed up. Subsequently removed the InvoiceNumber column, used DISTINCT to remove the duplicate Dates, and assigned Date the Primary Key value. Seems to be fine now. However, as I add more sales to the Invoice/Fact table, I would like the Calendar table to update with the newly added dates. Would I need to run an SSIS package everytime the Invoice/Facts table is updated for the dates in the Calendar dimension table to resynch? Thanks again.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

dj1202 (6/15/2012)
Evil Kraig F (6/15/2012)
dj1202 (6/15/2012)
There are duplicate dates (each correspond with a unique invoice number). Therefore, it will not let me assign. Any suggestions on how one should approach this?
I'd have to go with Jeff on this one... the Fact to Dimension seems laid out wrong.
The Calendar Dimension, in how I usually set them up, should be keyed off the date which is stored in the Fact table and then you dimension out anything else you'd need (Holiday, it's a Wednesday, Quarter 2, whatever). This way the date stays with the fact (INT vs. INT, what's the diff) and you dimension any supporting data off.
What decisions did you have to make that had you desire to ship the date and invoice number off to the dimension?
Thanks guys. I realized that the layout was all sorts of messed up. Subsequently removed the InvoiceNumber column, used DISTINCT to remove the duplicate Dates, and assigned Date the Primary Key value. Seems to be fine now. However, as I add more sales to the Invoice/Fact table, I would like the Calendar table to update with the newly added dates. Would I need to run an SSIS package everytime the Invoice/Facts table is updated for the dates in the Calendar dimension table to resynch? Thanks again.

That's one approach. Usually I just pre-fill for 10 years or so. Most solutions are re-written or heavily modified after 5 (in my experience) so that covers most eventualities, and it's easy enough to plug another 10 years in as maintenance if you need it.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA