Time dimensions, like date dimensions, are useful when you want to store the properties of a point in time for a fact. If you want to record the time of a sale, for example, you do not store the time in the fact record. You store the time key that points to the dimension record.
With the attached script you can easily generate a time dimension yourself. First you run the script that creates the table. Then you fill the table by running the procedure. Finally, you create the function as well. With this function you can convert a datetime field (or only a time) to a tijd_id.
01_Generate_Table_dim_Tijd.sql
Frequently asked questions
What is a time dimension in a data warehouse?
A time dimension stores the properties of a point in time, like a date dimension. You then store the time key in the fact record, not the time itself.
How do I generate a time dimension myself in SQL Server?
First run the script that creates the table. Then fill the table by running the procedure. Finally, create the function.
Which scripts do I need for a time dimension?
You need three scripts. First 01_Generate_Table_dim_Tijd.sql for the table, then 02_Generate_Procedure_Generate_dim_Tijd.sql to fill the table, and 03_Generate_fn_GetTimeKey.sql for the function.
What does the function fn_GetTimeKey do?
The function converts a datetime field, or only a time, to a tijd_id. With that id you link a fact to the time dimension.
How do I store the time of a sale in a data warehouse?
Do not store the time in the fact record. Store the time key that points to the record in the time dimension.
Comments
Related blogs
How do I create a Period Dimension?
Script all databases and objects
Decrypt Stored Procedure, Views, Functions in SQL Server