cancel
Showing results for
Did you mean:

# Multiple Dates in a fact table

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:

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

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