Calendar Dimensions | CLEARIFY

Version 10 Calendar Dimension Architecture

The date calendars have been revamped with several goals in mind:

• Combine fiscal and transaction date calendar dimensions
• Enhanced presentation options
• Only keep fields which might actually be used
• Power BI friendly

We have made the following changes:

• Version 7.9.536 removed:
  • IS Last Day of Week
  • IS Last Day of Month
  • IS Last Day of Quarter
  • IS Last Day of Year
• Version 10.x removed:
  • Day Number Since 1995 (Handled better in Power Query)
  • Week Number in Year (not possible)
  • Current/Total Period (was used years ago as a custom feature for one client)
• Version 10.x added:
  • Day is Working Day
  • "Presentation Friendly" Fields

A "presentation friendly" field would be a month, quarter, year with a text qualifier e.g. Q 2 or FY 2001.

In addition, the new dimension names are prefixed as vd_Cal_ instead of the old vd_CalYr

Here is an example of the old vs new:

QQube 7.9.536  vd_CalYrTxnDate	QQube 10.x   vd_Cal_TxnDate

We have further segmented calendar dimensions into two camps:

1. If it is the "main engine" of the data model such as transaction date
2. If it embellishes a report or analytic such as LiabilityPeriodStartDate

Let's use Vehicle Mileage as an example.  Trip End Date is the main driver of the data model, so we can perform drill downs.  Trip Start Date is not the main driver - just a field that would augment the big picture.

vd_Cal_TripStartDate	vd_Cal_TripEndDate

In some cases a data model might have several "driver" calendar dimensions.  For instance Sales has one for transaction dates and one for Open Sales Orders Shipping Date (as well as one each for Service Date and Fully Paid Date).

Power (Query/Pivot/BI) Notes

• We performed the sort of certain fields by other columns e.g. Month Name is sorted by the Month Number field.
• We have added running consecutive number fields for year, quarter, month, week, and day using M Language.  Better for comparisons and removes the need to be marked as a "date table".
• All date fields are formatted as MM/DD/YYYY.
• We created consistent hierarchies for drill downs
• We use a particular set of hidden and visible fields
• We have turned off the "Auto date/time for new files" in Power BI.

Our architecture does NOT require that a calendar table be marked as a "date table" in PowerPivot or Power BI. 

Example of the Transaction Date Dimension in the Sales Data Model :