How to handle incoming time-series data in a data warehouse?

Question

I am new to data warehousing (having learnt this in my school days) and is looking to do a data warehouse as a side project. Below is a simple data warehouse design I came up with:

The data warehouse have 2 dimension tables and 1 fact table. 1 dimension table contains datetime data and the other contain the device data. The fact table contains the device incoming data values captured at the field. The data granularity in the fact table is 5 minutes.

I am confused on how time-series is being handled and will appreciate if someone can clarify this. Assuming one row of data coming from the device looks like this:

    datetime        drive_a   drive_b   drive_c   shaft_a   shaft_b   shaft_c   total_output
02/01/2022 13:05      4.2       3.2       7.4       5.3       8.2       6.4        4563.2

This will be processed and stored into the fact table in the data warehouse.

How do I handle the datetime column from this incoming data since the dateKey is not the same format as the datetime incoming data?

I am thinking that the fact table need another column called dateTime_raw, but that defeat the purpose of a dim_datetime table isn't it since my datetime is already in my fact table?

P.S: Sorry if my question is confusing; trying my best to explain since I am not proficient in this field.

Answer 1

As mentioned in the comments, you'll need a way to relate your timeseries fact data fact_deviceValues back to your datetime dimensions table dim_datetime. That should be by the datetime in both tables via a foreign key referencing from fact_deviceValues to dim_datetime.

The datetime should be the units of measure which you want to store your timeseries data for e.g. your example of 02/01/2022 13:05 has hours and minutes. That probably makes the seconds field unneeded in the dim_datetime table (unless you do plan to store data at the granularity of seconds).

Your parent dim_datetime table would have 1 row in it for every minute of every hour of every day, within a reasonable timeframe that covers your data and use cases. For example, if you needed to cover about 100 years worth of data, your dim_datetime table would have somewhere around 53 million rows (60 minutes * 24 hours * 365 days * 100 years = ~53 million) in it.

Whereas your child fact_deviceValues table would hold the actual datetime of the event that occurred in the timeseries. Multiple events could happen at the same datetime and therefor multiple rows would exist with the same value relating back to the same single parent row in the dim_datetime table.

If you find your dim_datetime table is too large, you may find a better architecture to make it a dim_date table instead that only stores 1 unique row per date. Then your fact_deviceValues table can store the date separately from the time, and can be joined to the dim_date table for the dimensions specific to dates (which are usually more abundant than dimensions specific to times). But this depends on your use cases, and if there are any dimensions specific to times you actually care about (any I can think of are usually derivable from the time itself anyway).