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:
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
Option 2: You will need to take the measures from the correct fact table
Attachments
- 131 KB - MultipleDates.dash
- 519 KB - Multiple Dates.ecdata
