How to quickly create a Date dimension?

Any DWH (Data Warehouse) needs a date dimension to expose data related to a date (order date, delivery date ...) with certain hierarchical levels of data organization like month, quarter, year. With biGENIUS, setting up a date dimension only takes a few clicks.

By Claire Pierreclos

Noob Hack

The Runtime Config (RC) schema contains a table valued function and a view that provide source data for the date dimension.

Based on this view RC.DateRange, you can build a source system and use the generated stage object to build your different Date level and the hierarchical Dimension:

You can configure the start and end of date range with the Project Custom Properties ScdValidFrom and ScdValidTo:

All the details can be found in our community site.

Pro Hack

Now that you have a date dimension that fits your needs, it can be interesting to configure a readable surrogate key rather than an identifier that means nothing at first reading. This will make it much easier to analyse the data in your fact tables, for example.

To configure it, you should map the ID of the Dimension leaf level with a readable ID like the DateKey YYYYMMDD provided by the RC.DateRange view for example.

You will have this kind of result: readable surrogate keys for the dates Foreign Keys (highlighted in yellow) instead of usual incremental number surrogate keys (highlighted in green):