[AccessD] Using Modified Julian Days

Charlotte, Robert, Gustav and all,

This regards the date fact table, and Kimball's discussion of it, as quoted
by Gustav. I'll start with a quote from Charlotte:

>If you were using surrogate keys and
>meaningless values as the PK, the dates in the main records would need
>to be inserted *from* the date table, and that isn't generally the way
>the thing is used.

The tricky thing is, it is far harder to make a 'meaningless' PK in this
situation than it might appear.

Take the most commonly used surrogate key: the autonumber. You would
typically order your Date fact table from the least recent date to the most
recent, i.e. in ascending order. If your table creation routine runs
flawlessly, your default autonumber PK would generate values: 1, 2, 3, ...
n, with n being the number of records. Meaningless? Hardly. Its the number
of days since 'Day 0' in your fact table, which is the day before the first
date in the table.

I suppose, if you were truly nihilistic, you could use a random autonumber
and make it truly meaningless, but I can't see any sense in that. My point
is, with any common sense ordering and keying of dates, your PK has meaning.

So, if your key has a meaning, namely the number of days since an arbitrary
day 0, why not take advantage of it? What if there was a widely recognized
standard for numbering days in this manner?

Enter the Modified Julian Day. The MJD is a standard day numbering system
based on the old Julian Day count. The MJD was introduced in the 1950's by
space scientists, and is recognized by several international standards
bodies. It is used by the US Naval Observatory and the National Institute of
Standards in their timekeeping duties.

My proposal is:

Key your date fact table using MJDs
======================================
This has several advantages:
* MJDs can be easily stored as a Short integer. This makes indexing the date
fact table efficient.
* MJDs are easy to calculate (see below)
* Date fact tables generated in different locales using different date
ranges would match if keyed using MJDs.
* The MJD foreign key values in the main records would not have to be looked
up using the date fact table. When the main record is created or modified,
the MJD values can be calculated on the fly, with perfect assurance that
they will match the primary key in the date fact table.
* You can do a number of date related calculations on the main records
themselves, without having to resort to the date fact table. Examples:
beginning and end date ranges, and days elapsed

Here is code I wrote for converting Dates to MJDs and vice versa:

<code>
' 0 Hours, 17 November 1858 AD
Private Const mcDte_MJD_Day0 As Date = #11/17/1858#

' Returns the Modified Julian Date for a specified Date
Public Function Date_MJD(dteUDdate As Date) As Long
    ' Calculate the number of days since Day 0
    Date_MJD = CLng(DateDiff("d", mcDte_MJD_Day0, dteUDdate))
End Function

' Returns the Date for a specified Modified Julian Date
Public Function MJD_Date(lngMJD As Long) As Date
    ' Add MJD to Day 0 to find the Date equivalent
    MJD_Date = CDate(DateAdd("d", lngMJD, mcDte_MJD_Day0))
End Function
</code>

If you agree that a meaningless PK doesn't make sense in this instance, you
may find that using MJDs gives you extra versatility, while preserving the
advantages of the date fact table.

-Ken