Load for Date dimension table of a warehouse

Question

I have a general question about loading data into a data warehouse (DW). This is basically a followup to an older question of mine. I have a general understanding problem about fill the [Date] dimension.

Setup

I have a IoT base business model basically like this:

• Project
  • Location
  • Customer
  • [...]
  • DataSource
    • Device -ValueType
      • Data (not include in the relational database)

Its currently persisted by a relational database.

Effort taken

For storing mass data I designed a data warehouse model (snowflake) to allow effective storing into a DW.

Looks currently like this:

I read a lot about designing a proper warehouse and settings up an ETL process. I identified all your dimension tables as SCD and use the temporal tables feature of MSSql to solve updates.

Question

Let assume only my fact and [Date] dim table exists. I have problems to understand how to perform proper inserts into the DW. If I understand correctly a default pattern would be using a staging table (on MSSQL, for example, a Polybase table) and then perform batch operations like:

1. clean staging table
2. fill staging table
3. query for not existing dates
4. insert not existing dates
5. move staging to fact table with references to date dimension

This could be optimized for example with MERGE statements.

• Did I understand that correctly?
• Are there better best-practice strategies like that?

One last hint: Because the DeviceDate is IoT data there will be very unique dates (more timestamps). So I prefill all possible timestamp makes no sense for me.

Answer 1

Having a time dimension table (in this case "dim.date") with a TimeStamp column holding the time stamp of each record of the fact table at the finest possible granularity (like miliseconds or finer) is not very sensible for a data warehouse. In the worst case, this would lead to a number of records in that dim table which is almost equal to the number of entries in the fact table.

Instead, the "dim.date" table should exactly hold the records which represent the smallest time slice for which there is a requirement of creating aggregates. For example, if the smallest time slice for an aggregate is an hour, simply create one record per hour. The timestamp itself can be part of the fact table, in case you really need to support queries which use that value, or it could be left out if there is no such requirement.

So if your data warehouse "cube" represents, for example, the data of a whole year, and you want to support drill-downs down to an hour as the smallest aggregate, your dim.data table will require 365x24=8760 records, which can easily be prefilled for that year. And even if you want to drill down to a minute, 365x24x60 gives you 525.600 records, which is I guess still quite low compared to the number of entries to be expected in the fact table over this period, so prefilling should still be simple.

As a general rule, make sure you know which kind of queries you need to support (and which not), then model your data warehouse accordingly.