By: Paul Schuliger
Business Intelligence Consultant
Anyone who’s looked into a master craftsman’s toolbox knows that they keep an extensive variety of tools. So many so, that it seems only half of them are ever used. But the craftsman doesn’t have the same thought. The craftsman knows that each tool has its special time and place to be utilized.
The SQL Date Matrix tool is a business intelligence tool that has its purpose, but is often left on the shelf to be slowly covered in dust and ultimately forgotten. But to the business intelligence expert, the SQL Date Matrix is an invaluable solution when used in the right situation.
Here’s what the SQL Date Matrix does: it solves the problem of needing multiple and different time periods in a column format within a report. These time periods are often unrelated, making traditional SQL reporting very challenging.
Areport with multiple time periods typically includes week-to-date (WTD), month-to-date (MTD) and year-to-date (YTD) columns. These aggregations are fairly straightforward and do not require any special tool. However, the SQL Date Matrix is needed when the requirement is to have WTD, MTD and YTD, along with a column for Same Month Last Year, YTD with no Weekends, YTD with only Weekends, Previous 13 weeks, First Half of the Month (15 days) or Second Half of the Month (16th day to end of month). These specialized time periods are very difficult to code using regular SQL or a reporting tool without custom code.
Essentially, the Date Matrix is a unique database table that utilizes SQL aggregation to transform data stored vertically (in multiple rows) into data stored linearly (in one row). The tool is database agnostic, and therefore can be implemented in any database.
What comprises the Date Matrix?
The Date Matrix table is comprised of four different components.
Take a look at the date matrix below. For the following illustrations, the granularity of the Date Matrix is a single month in a year.
There are four time periods that are defined in the table (Current Month, Last Month, Same Month Last Year and Year-To-Date). The time periods are represented by the 0’s and 1’s assigned in their respective column for each row. The time periods (multipliers) are assigned in relation to the current ‘Grouping Value’. All of the multipliers shown below are assigned a 0 or 1 according to the current time period being 201608.
NOTE: The multipliers have to be created for all distinct ‘Grouping Values’ that are available to your reporting solution. This will be discussed further in part two of this article.
The definition of the time period that the multipliers represent determine how far back each ‘Grouping Value’ must go to provide coverage. If the time period “Last 24 Months” is added to the Date Matrix, then each ‘Grouping Value’ would have 24 rows to cover that specific time period. If there was another time period that was “Last 36 Months” then there would be at least 36 rows per ‘Grouping Value’.
The Date Matrix featured has 13 rows per ‘Grouping Value’. It needs 13 rows to properly cover the “Same Month as Last Year” time period.
The Date Matrix is used in combination with the Sales table to provide the aggregation that’s necessary to get the different time periods into the same row. Below is the sample Date Matrix and the Sales table shown next to each other.
How to implement a Date Matrix
– Join the Date Matrix JOIN_VALUE field to the Sales table FiscalMonthfield.
– Use the aggregate SUM function with each Time Period.
– Use the time period Multipliers to arithmetically multiply against the Sales Amount field from the Sales table.
– Use the GROUP_VALUE field from the Date Matrix to perform the aggregation.
Preaggregated Resultset
The chart below illustrates how the Date Matrix works within a query. Look at each row within the Current Month time period. The Multiplier (0 or 1) is multiplied by the Sales Amount. For the Join Value of 2015-08, the Sales Amount ($1,200) is multiplied by the multiplier (0) to produce the column result of 0. All values in the column result in a zero except the last row, which pertains to the Current Month for the ‘Grouping Value’ of 2016-08. There, the Multiplier is a 1, which gives a result of $1,600. The final results for the Current Month time period will be the value of $1,600 once all rows are aggregated.
Aggregated Resultset
Here is the final resultset from the Date Matrix query. This specific example brings back a single row resultset showing multiple and different time periods.
Now imagine if the resultset was more than a single row. What if it included the same information for your entire fiscal year by month, or multiple years? You would then be able to make tremendous progress in getting the data you want out of your system in the requested format. And when your customer comes back and asks for another time period – like the last 13 weeks – you now have a tool that can quickly do the job.
Knowing the value of the Date Matrix, we strongly recommend that all business intelligence teams have this SQL tool in their arsenal. We’ll be following up this article with another on the creation and population of the Date Matrix. Be sure to subscribe and stay tuned for more.