By: Paul Schuliger
BI Consultant at Key2 Consulting
This is the second article about the use of the Date Matrix in reporting applications. The first article can be found here if you missed it. The Date Matrix is a SQL tool that is used to solve the problem of needing multiple and different time periods in a column format within a report. It’s a table that looks like the table below. Be sure refer to the part 1 of this post for a complete understanding of each part’s purpose in the table below.
This article will cover the actual creation and implementation of the Date Matrix. Remember that the Date Matrix shown in this article is just an illustration of what it can do and that there are many more possibilities in your own implementation.
Let’s get started with the creation and implementation of the Date Matrix. The SQL DDL below can be used to create the SQL table in any database, as this is a SQL tool and is not dependent on a specific flavor of a database. The DDL clearly shows that there are three field groups that need to be created: Grouping fields, Join fields, and the Multipliers.
(Figure 1)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE [DateMatrix] ( [GROUP_DATE] [datetime] NOT NULL, [GROUP_VALUE] [varchar](50) NOT NULL, [GROUP_YEAR] [int] NOT NULL, [GROUP_QUARTER] [int] NOT NULL, [GROUP_MONTH] [int] NOT NULL, [JOIN_DATE] [datetime] NOT NULL, [JOIN_VALUE] [varchar](50) NOT NULL, [JOIN_YEAR] [int] NOT NULL, [JOIN_QUARTER] [int] NOT NULL, [JOIN_MONTH] [int] NOT NULL, [M_CURRENT_MONTH] [int] NULL, [M_LAST_MONTH] [int] NULL, [M_SAME_MONTH_LAST_YEAR] [int] NULL, [M_YTD] [int] NULL |
The next step is to populate the Date Matrix with all of the necessary records. The example shown above has 13 records that cover one grouping for the period of August 2016 (the specific value is 201608). Refer to the Date Matrix (figure 1) above to help in understanding the code.
The SQL stored procedure described below (figure 2) is used to create the records that populate each Grouping Value. Keep in mind that the hardest part of the stored procedure is often the Date logic that is necessary to create the multipliers. For example, creating a multiplier that reflects a rolling 12 months or 18 months would need extra attention in ensuring that the logic is accurate.
The stored procedure starts with the declaration of variables. There is one variable for each field in the Date Matrix table, along with a couple of local variables that are used during the processing.
(Figure 2)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DECLARE @v_group_date DATETIME , @v_group_value VARCHAR(50) , @v_group_year INT , @v_group_quarter INT , @v_group_month INT , @v_join_date DATETIME , @v_join_value VARCHAR(50) , @v_join_year INT , @v_join_quarter INT , @v_join_month INT , @v_join_desc VARCHAR(100) , @v_loop_current INT , @v_loop_join INT -- Multipliers , @v_cm INT = 0 -- Current Month , @v_cm_prev_01 INT = 0 -- Last Month , @v_cm_last_year INT = 0 -- Same Month Last Year , @v_cy_ytd INT = 0 -- Year To Date -- Variables used in logic for the Multipliers , @v_current_year INT = 0 , @v_current_month INT = 0 |
The next step is critical in the stored procedure as it controls the dates that are used in the stored procedure. The @v_loop_join variable represents the Join Value (or 201508) that is being processed within the Grouping Value (201608), which is represented by the @v_loop_current variable.
Setting these variables determines what time periods are going to be processed within the Grouping Value. You can see the start of the looping functionality in Figure 3. The rest of the code in the loop is focused on creating the Multipliers that are necessary for the specific Join Value record within the current Grouping Value.
The reason that the first value for the Join Values is 201508 is that it’s necessary to go back 13 months to properly determine the multiplier “Same Month Last Year”. So when building your implementation, make sure to determine how far back in time you need to go in order to produce the correct results. If you are going many years back in time, know that your Date Matrix will be have many records, which could be a performance issue.
In this simple illustration, the dates are set up to run one grouping value at a time. If you’re reading this article I’m going to assume that you have the ability to add logic that would automatically change the Grouping Value and continue processing for other values.
(Figure 3)
|
1 2 3 4 5 6 7 8 9 10 |
-- Starting values for the JOIN VALUE (start time period) and the GROUPING VALUE (current) SET @v_loop_join = 201508 -- 13 months prior to the current year quarter SET @v_loop_current = 201608 -- Set the current Year and Month (GROUPING_VALUE) SET @v_current_year = CAST(SUBSTRING(CAST(@v_current_year AS VARCHAR(6)), 1, 4) AS INT) SET @v_current_month = CAST(SUBSTRING(CAST(@v_current_month AS VARCHAR(6)), 5, 2) AS INT) WHILE @v_loop_join <= @v_loop_current BEGIN |
Setting the Grouping Values and the Join Values
Using the variables that were declared for each field at the top of the stored procedure, each variable is assigned a value that is then used in the INSERT statement below. The four variables in Figure 3 are used to populate the variables shown in Figure 4 with some date string manipulation needed to create a specific field of datatype DATE.
(Figure 4)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- Set the JOIN_VALUE Year and Month SET @v_join_year = CAST(SUBSTRING(CAST(@v_loop_join AS VARCHAR(6)), 1, 4) AS INT) SET @v_join_month = CAST(SUBSTRING(CAST(@v_loop_join AS VARCHAR(6)), 5, 2) AS INT) -- Assign the values for the GROUP and JOIN fields in the Date Matrix SET @v_group_date = CONVERT(nvarchar(30), (CAST(@v_current_month AS varchar(2)) + '/01/' + CAST(@v_current_year AS varchar(4))), 101) SET @v_group_value = @v_loop_current SET @v_group_year = @v_current_year SET @v_group_quarter = CASE WHEN @v_current_month < 4 THEN 1 WHEN @v_current_month < 7 THEN 2 WHEN @v_current_month < 10 THEN 3 ELSE 4 END SET @v_group_month = @v_current_month SET @v_join_date = CONVERT(nvarchar(30), (CAST(@v_join_month AS varchar(2)) + '/01/' + CAST(@v_join_year AS varchar(4))), 101) SET @v_join_value = @v_loop_join SET @v_join_quarter = CASE WHEN @v_join_month < 4 THEN 1 WHEN @v_join_month < 7 THEN 2 WHEN @v_join_month < 10 THEN 3 ELSE 4 END SET @v_join_desc = DATENAME(MONTH, @v_join_date) + ' ' + CAST(@v_join_year AS varchar(4)) |
Multiplier Logic
This is where the real work is done in determining the Multipler logic. In this example, there are four Multipliers with straight forward logic – current month, last month, current month last year, and year-to-date (YTD). If you were to add other Multipliers, this is where the logic would be created. And the logic is used to create the correct values for each specific Join Value that the stored procedure loops through. For each time through the looping structure, ensure that your logic is correct for each multiplier individually.
(Figure 5)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
-- Multiplier - Current Month IF @v_current_year = @v_join_year AND @v_current_month = @v_join_month SET @v_cm = 1 ELSE SET @v_cm = 0 -- Multiplier - Last Month IF @v_current_year = @v_join_year AND (@v_current_month - 1) = @v_join_month SET @v_cm_prev_01 = 1 ELSE IF (@v_current_year - 1) = @v_join_year AND (@v_current_month - @v_join_month) = -11 SET @v_cm_prev_01 = 1 ELSE SET @v_cm_prev_01 = 0 -- Multiplier - Current Month Last Year IF (@v_current_year - 1) = @v_join_year AND @v_current_month = @v_join_month SET @v_cm_last_year = 1 ELSE SET @v_cm_last_year = 0 -- Multiplier - Year To Date IF (@v_current_year) = @v_join_year SET @v_cy_ytd = 1 ELSE SET @v_cy_ytd = 0 |
After all of the Multipliers are completed and the values are accurately populated, the next step is to use all of the variables within the Join Value and insert them into the Date Matrix. Below is the insert statement that is used to do so.
(Figure 6)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- After all Multiplers are configured insert the row into the Date Matrix INSERT INTO DateMatrix ( [GROUP_DATE], [GROUP_VALUE], [GROUP_YEAR], [GROUP_QUARTER], [GROUP_MONTH] , [JOIN_DATE], [JOIN_VALUE], [JOIN_YEAR], [JOIN_QUARTER], [JOIN_MONTH], [JOIN_DESCRIPTOR] , [M_CURRENT_MONTH], [M_LAST_MONTH], [M_SAME_MONTH_LAST_YEAR], [M_YTD] ) VALUES ( @v_group_date , @v_group_value , @v_group_year , @v_group_quarter , @v_group_month , @v_join_date , @v_join_value , @v_join_year , @v_join_quarter , @v_join_month , @v_join_desc , @v_cm , @v_cm_prev_01 , @v_cm_last_year , @v_cy_ytd ) |
Once the insert statement is run, the stored procedure increments the looping value (which is the Join Value) and proceeds to process the next Join Value. In the illustration, the Join Value would be changed from 201508 to 201509 after the first record is processed. You can see that there is a check in the logic to determine if the current month for the Join Value is the 12th month of the year. If so, the code correctly makes the Join Value the 1st month of the next year.
(Figure 7)
|
1 2 3 4 5 6 7 8 9 |
-- Increment the Join Year and Month variables IF SUBSTRING(CAST(@v_loop_join AS VARCHAR(6)), 5, 2) = '12' BEGIN SET @v_loop_join = CAST(CAST(@v_join_year + 1 AS varchar(4)) + '01' AS INT) END ELSE SET @v_loop_join = @v_loop_join + 1 END -- WHILE loop |
Remember that the goal of the Date Matrix is to ease the development of your reporting application by providing a database table that allows for easy aggregation of multiple and different time periods. The table can be used to join with fact tables to get results like those shown in figure 8 (below) as an example. The Date Matrix is an excellent table to have (in your Data Warehouse or Data Mart) that all users or reporting systems can take advantage of.
(Figure 8)
Though it does take effort to initially create and populate the Date Matrix table, the functionality that it provides for reporting purposes is well worth the time! If you find yourself developing SQL to create WTD, MTD, or YTD comparisons, use the Date Matrix to do it for you.
You can be creative with the different reporting periods that you put into the system. And you can surprise your user community with varied timeframes that are not easy to come by in typical reporting solutions – like 7 weeks running, or the just the weekend days in each month.
The Date Matrix used in this article is done for Months but there can be separate tables that are used for a Weekly Date Matrix or if your database holds many years of historical date you can create a Yearly Date Matrix.
The Date Matrix is not needed for every report but when necessary it’s very helpful!
If you would like the complete code in an electronic format please send us an e-mail here at Key2 Consulting. We would be glad to send it to you. And if you implement the Date Matrix, we would love to hear how you have done so.