1

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:

Snowflake schema

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.

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.

|improve this answer|||||
  • Ah ok thanks I understand. Makes a lot sense. When I include the exact timestamp into the fact table the date dim only needs a granularity of one hour, because that's the minimum query time frame we need to search for. Even then the include fact rows containing the exact timestamp. But is the strategy to fill the fact table correct? – Steffen Mangold Jan 20 at 22:35
  • @SteffenMangold: it depends if you need to support also queries with no aggregation in time (but maybe filters or aggregates for other dimensions). If that's the case, you will probably need all those fact rows. But if a drill-down to one hour is all you require in every query, consider to pre-aggregrate the fact data hourly (which means you can leave the exact timestamp completely out of the model). That could reduce the size of the OLAP database tremendously. – Doc Brown Jan 21 at 6:44

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.