Working with FACT Date Timestamp Data - Tips & Best Practice Advice

[#1]

What is the best practice for working with date time stamp data in fact tables.

Calculating Time Durations

Let us say that we have a fact table (> 100,000 records) with a number (ten or more) of fields containing date timestamp data (for example 2/08/2006 7:07:00 PM).

If I want to caluclate the duration between a number of these fields should I;

1. Create additional columns and perform these calculations in the LOAD statement
2. Calculate these values in chart s'on the fly'

Grouping Date Timestamps

What about grouping these dates?

Let us say we have the following date timestamps for the first 4 records in our fact table;

• 2/08/2006 7:07:00 PM
• 2/08/2006 7:09:00 PM
• 2/08/2006 7:12:00 PM
• 2/08/2006 7:13:00 PM

I would like to report these on a 'Day', 'Month' and 'Year' basis and would need to aggregate them accordingly. Should I;

1. Create additional columns and convert these records in the LOAD statement
   • For example a 'YEAR(field_name)' statement would convert all the records to 2006 and store them in a YEAR column
   • I would need 4 fields probably
     • DAY, MONTH, YEAR and DAY_MONTH_YEAR
2. Calculate these values in charts 'on the fly'
3. Create a DIM table and refer to this
   • Obviously this wouldn't go down to individual minutes but it could go down to day granularity
   • i could then round the date time stamp I have 'on the fly' when it is required (in charts) and derive MONTH and YEAR values from there
4. Possibly a mixture of 1. and 3.
   • Convert to a day in the LOAD statement
   • Use this field to refer to the DIM table to get aother data

[amars]

Hi,

FOR TIME DURATIONS

For calculating TIME DURATIONs, check for Interval Function, that will help you identify the difference in Days and Hours.

Try doing it at the Load script as much as possible because, that way the result are available for computation later in charts. Doing it at chart level will lead to more calculations/ memmory issue while the chart is loading.

GROUPING TIME DURATIONS

For Groping Time Durations, u can create an Additional Date Fields using Date(Floor(DateField)) As NewDateField syntax in Load Script.

Again to create different Time Dimensions, you can create a Master Calendar in your data model. search for Master Calendar in Community (http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar).

Thanks....