cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

mceclip1.png 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.

mceclip2.png 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.

mceclip3.png 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:
multiple_dates.PNG
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
multiple_dates_unified_table.PNG
Option 2: You will need to take the measures from the correct fact table
multiple_dates_separated_table.PNG

Attachments 

 - 131 KB - MultipleDates.dash
 - 519 KB - Multiple Dates.ecdata
Rate this article:
Version history
Last update:
‎03-02-2023 08:50 AM
Updated by:
Contributors