Single Record Dimensions in SQL Data Warehouse, seems improper, how else can I provide for these needs?

Question

The business has a need for some single value dimensions:

DIM_BuildDate - store datetime of the DW build, with latest inventory date
DIM_CurrentAccountingPeriod - what is the accounting period now (at build date)
DIM_CurrentExchangeRate - what is the currency exchange rate now

Perhaps they could all roll into a single dimension with attributes for each value, but that is not my concern.

This seems wrong. The values change daily or periodically, making them slowly changing dimensions, at best. However, there is some genuine usefulness in storing those values with the DW.

• If a transactional job had failed, the DW build might have inventory data that is two days old, and that's important. So I store it in DIM_BuildDate.
• Often, the calendar date does not match up with the accounting period, especially near the beginnings and endings of months. So I store that as DIM_CurrentAccountingPeriod.
• There is a FACT_ExchangeRate which stores exchange rate values over time, but the business people want an easy way to access "Current Exchange Rate."

I want to be practical, and offer useful objects such as these, but I suspect these dimensions do not conform to a proper Kimball implementation.

What is another way to provide for these requirements in the DW?

Answer 1

It's certainly wrong to create a dimension with single records per each metric - your star schema will bloat.

Build Date needs to be added, there is no way around it (it's a unique piece of data that you don't otherwise have in your star schema).

Current Exchange Rate can be easily added to your Tabular model as a calculated DAX measure (just select rate where date = build date). No need to store it in a separate dimension. As a measure, it'd be much easier to use in calculations.

Fiscal dates can be modeled either as a separate (Fiscal) calendar table, or you can simply add fiscal dates as attributes to your calendar table (i.e, "Fiscal Date", "Fiscal Year", etc).

Similarly, you can flag "current" period in the calendar table (i.e., add a field "Period Type", with values "Current Period", "Past Period" ("Future Period" if you need)). It can be used as a slicer, or as DAX filter. The same applies for "current" fiscal period - it's just another attribute in a calendar table.