0

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: enter image description here

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.

4
  • What are some example values of dateKey? Your dim_datetime table will essentially need a field that has the same granularity of dates and times as your fact_deviceValues so you can relate the two together. By your example, it looks like that granularity is to the minute.
    – J.D.
    Feb 5 at 5:11
  • Hi J.D. I am thinking of putting incremental ID to dateKey to uniquely identify each row. I do not understand what you mean by same granularity of dates and times. Currently I do not have a field for this datetime in the fact table (this is one of the part I am confused if its needed). Am I right to say dim_datetime should not have seconds field? Should I keep the datetime data string as it is in the fact table (if so, how does this field works with dim_datetime, rather what is the purpose of dim_datetime in the first place?)? Appreciate if you have some examples. Thanks! Feb 5 at 6:23
  • I don't understand your subsequent questions but basically, you example shows your fact table has a datetime field even though your picture doesn't show that? Your example also shows that datetime field has hours and minutes but not seconds. So your dim_datetime table should store datetimes with hours and minutes (doesn't need seconds) as a single field, to make joining easier.
    – J.D.
    Feb 5 at 13:39
  • 1
    Thanks J.D and sorry for the confusion. Indeed my example picture do not have the datetime field because I am confused at whether it needs to have (I figured now that this field is a must). In dim_datetime, I should have a datetime that is in the same format as the one in my fact table? Therefore the dim_datetime will have a FK datetime from fact table (all other fields remain the same as per example?)? Does it make sense to still have the hour, minutes in dim_datetime? Feb 5 at 15:41

1 Answer 1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.