Working with the Time data type in Tabular Model

According to Microsoft's documentation for data types supported in Tabular Model, we can see that the Time data type is not supported. Attempting to import a Time data type into a Tabular Model doesn't cause an error, but it will just ignore the column as if it never existed. The closest we can get is Datetime data type, but this does not answer question like:

What is the average time for the first or last transaction of the day, for this month?

Because applying average on Datetime will get you different result comparing to average of Time by itself.

Therefore, we will be discussing an alternative way of importing Time into Tabular Model and be able to apply aggregate function to it.

For example, we have a time entry table that contains user login-in times as follow:

Let’s try to import this table into SSAS with Query Editor mode

Even when all columns are selected in the query, you can see that time column has disappeared.

The Workaround

To workaround this problem, we need to convert time data type into an integer, which indicates the number seconds since midnight. This can be done with the following query:

SELECT *,
DATEDIFF(SECOND, 0, DATEADD(Day, 0 - DATEDIFF(Day, 0, CAST(time_in as datetime)), CAST(time_in as datetime))) as time_in_sec,
DATEDIFF(SECOND, 0, DATEADD(Day, 0 - DATEDIFF(Day, 0, CAST(time_out as datetime)), CAST(time_out as datetime))) as time_out_sec
FROM dbo.time_entry

Which yields the following result:

The reason to CAST Time into Datetime is that SQL Server does not have TIMEDIFF function to find out the differences between midnight and our current time entry.

Now let’s create a view from the query above and try to reimport into SSAS again. Now we can see the imported result includes the converted elapse time from midnight:

Creating Measures

Once our data preparation is done, let’s move to Power BI and create time measure base on our new elapse time columns.

Once you have imported from SSAS source, we can create the following measure:

Average Time In = TIME(INT(AVERAGE(time_entry_view[time_in_sec])/3600),INT(MOD(AVERAGE(time_entry_view[time_in_sec]),3600)/60),0)

Now, by connecting to our Tabular model using Power BI, let’s try dragging our newly created Average Time In measure into the Matrix Visual. We should see the following result:

The default Power BI formatting will choose Date/Time data type for you, so let’s change the format to Time (HH:mm) by clicking on Average Time In in Fields Panel and navigating to the Model Tab:

Which results in the following:

Let’s repeat the steps for Average Time Out as well:

Conclusion

As you can see, even though SSAS is a powerful tool, there are some unexpected integration problems that you might encounter when you try to import your data. With this workaround, we can apply aggregate functions on Time data more accurately and consistently.

This elapse time format can allow you to find out the duration by simply subtracting the start time and end time then use DAX to convert back into HH:mm format like the example we have shown as well. 

BizOne's consultants are experts in providing data integration solutions. Contact us today if you'd like to schedule a free consultation to discuss your requirements!