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

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:

 

Community_Admin_3-1634477019757.png

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 : 
Community_Admin_4-1634477093316.png

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:

Community_Admin_5-1634477169833.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

Community_Admin_6-1634477235911.png

Option 2: You will need to take the measures from the correct fact table

Community_Admin_7-1634477235917.png

Attachments 

 - 131 KB - MultipleDates.dash

 - 519 KB - Multiple Dates.ecdata

 

 

Version history
Last update:
‎10-17-2021 06:30 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request