June 15, 2012 at 12:59 pm
Hello all, I am creating a table, named DimCalendar, which has the following columns:
DateID (date)
Quarter
Year
Month
Week
Day
InvoiceNumber
There are duplicate dates in the DateID column, but I would like to assign a column as a primary key in this table. This DimCalendar table corresponds with a Sales, or Facts Table, where InvoiceNumber is the Primary Key. The problem is that there are duplicate dates for the same InvoiceNumber (many to one relationship). Any ideas on how to approach this? Thank you
June 15, 2012 at 2:15 pm
dj1202 (6/15/2012)
Hello all, I am creating a table, named DimCalendar, which has the following columns:DateID (date)
Quarter
Year
Month
Week
Day
InvoiceNumber
There are duplicate dates in the DateID column, but I would like to assign a column as a primary key in this table. This DimCalendar table corresponds with a Sales, or Facts Table, where InvoiceNumber is the Primary Key. The problem is that there are duplicate dates for the same InvoiceNumber (many to one relationship). Any ideas on how to approach this? Thank you
Two questions, why there are duplicate values for DateID and more important what is doing the InvoiceNumber column on DIM_CALENDAR table?
Having said that, in general DIM tables have a surrogate primary key, not a natural primary key.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 15, 2012 at 2:35 pm
PaulB-TheOneAndOnly (6/15/2012)
dj1202 (6/15/2012)
Hello all, I am creating a table, named DimCalendar, which has the following columns:DateID (date)
Quarter
Year
Month
Week
Day
InvoiceNumber
There are duplicate dates in the DateID column, but I would like to assign a column as a primary key in this table. This DimCalendar table corresponds with a Sales, or Facts Table, where InvoiceNumber is the Primary Key. The problem is that there are duplicate dates for the same InvoiceNumber (many to one relationship). Any ideas on how to approach this? Thank you
Two questions, why there are duplicate values for DateID and more important what is doing the InvoiceNumber column on DIM_CALENDAR table?
Having said that, in general DIM tables have a surrogate primary key, not a natural primary key.
After your post and questions, I'm realizing that what I created is not really a calendar dimension table after all. Rather, it's just an extension of the Facts/Sales/Invoice table, giving more details about the date...
There are duplicate values for DateID because there are unique InvoiceNumbers that have the same Date values (several sales that have been made on the same day). Thanks for pointing that out. Is there anything you can recommend to clean up this situation? Sorry if confusing.
June 15, 2012 at 2:58 pm
I had tried to clean it up using the following code, but it is still returning duplicate Date values:
SELECT DimCalendar.DateID, DimCalendar.Quarter, DimCalendar.Year, DimCalendar.Month, DimCalendar.Week, DimCalendar.Day
FROM DimCalendar
RIGHT OUTER JOIN (SELECT DISTINCT(DateID) FROM DimCalendar) AS DistinctDate
ON DimCalendar.DateID = DistinctDate.DateID
This removes the OrderNumber column but it still returns the duplicate dates associated with the OrderNumber values.
June 17, 2012 at 5:52 pm
dj1202 (6/15/2012)
PaulB-TheOneAndOnly (6/15/2012)
dj1202 (6/15/2012)
Hello all, I am creating a table, named DimCalendar, which has the following columns:DateID (date)
Quarter
Year
Month
Week
Day
InvoiceNumber
There are duplicate dates in the DateID column, but I would like to assign a column as a primary key in this table. This DimCalendar table corresponds with a Sales, or Facts Table, where InvoiceNumber is the Primary Key. The problem is that there are duplicate dates for the same InvoiceNumber (many to one relationship). Any ideas on how to approach this? Thank you
Two questions, why there are duplicate values for DateID and more important what is doing the InvoiceNumber column on DIM_CALENDAR table?
Having said that, in general DIM tables have a surrogate primary key, not a natural primary key.
After your post and questions, I'm realizing that what I created is not really a calendar dimension table after all. Rather, it's just an extension of the Facts/Sales/Invoice table, giving more details about the date...
There are duplicate values for DateID because there are unique InvoiceNumbers that have the same Date values (several sales that have been made on the same day). Thanks for pointing that out. Is there anything you can recommend to clean up this situation? Sorry if confusing.
Well... Since InvoiceNumber column is already on the Facts/Sales/Invoice table and you can have (or most probably already have) a DIM_DATE or DIM_CALENDAR table, let that DIM_DATE or DIM_CALENDAR table give whatever details are needed about a particular date.
The date of the sale/invoce should be stored on the facts/sales/invoice table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply