T-SQL Date Dimensions with ConnectWise PSA

ConnectWise PSA is a Microsoft SQL Server OLTP-based application commonly used by IT Managed Services Providers for managing their businesses.  ConnectWise provides a mechanism for entering and storing service tickets, time worked by technicians on service tickets, sales items, client billing, and other finance information.  For any Managed Services Provider, it tends to be the heart of the business.

A Date Dimension is a concept typically used in a Data Warehouse for implementing Time Intelligence which allows for querying on say “year to date” or for the quarter.  A Date Dimension is created through the creation of a Date Table.  A Date Table is a table which contains every date for a time period (with no gaps within) and numerous properties for each date.  Some of the most common properties are:

  • The Date, as a DATE or DATETIME
  • The DateKey, which is an INT that shows the date in YYYYMMDD form
  • Whether or not the date is a work day or not
  • Numerous date parts of the date, such as the month number, month name, day of the week, week number, quarter, year, and many more.
While the exact fields which make up the date table will vary based on the needs of the business, the above items tend to be some of the most common.

Continue reading