Multiple Dates in a fact table

Analytical Need

Fact tables may include multiple dates per record. For instance, a record can have an open date, a closed date, an updated date etc.

Modeling Challenge

We need one time dimension since we don’t want the user to use multiple date fields. We need one date field to filter them all.

Solution

Example:

This is the fact table:

Step 1:

Create a custom query from the fact. It will contains the fact itself with just one type of date. There are 2 options to build it:

Option 1:

Union all the tables received, resulting in one fact table containing duplicate keys, one date field and a date type.

Result :

SQL:

SELECT DISTINCT f.TicketID, f.[Open Date] AS Date,'Open' AS DateType 
FROM Original f 
WHERE f.[Open Date] IS NOT null 

union 

SELECT DISTINCT f.TicketID, f.[Closed Date],'Closed' AS DateType 
FROM Original f 
WHERE f.[Closed Date] IS NOT null 

union 

SELECT DISTINCT f.TicketID, f.[Updated Date],'Update' AS DateType 
FROM Original f 
WHERE f.[Updated Date] IS NOT null

Option 2:

Keep the fact tables separate and name the tables according to date type in it.

Result:

Image 1. Result tables

SQL:

SELECT DISTINCT f.TicketID, f.[Open Date] 
FROM Original f 
WHERE f.[Open Date] IS NOT null 

SELECT DISTINCT f.TicketID, f.[Closed Date] 
FROM Original f 
WHERE f.[Closed Date] IS NOT null 

SELECT DISTINCT f.TicketID, f.[Updated Date] 
FROM Original f 
WHERE f.[Updated Date] IS NOT null

Step 2:

Connect the custom dim table (distinct union of all possible dates or the full list of dates from the date CSV file) to the fact table/s

Dashboard:

Option 1: You will need to filter the measures according to the date type field