We have all come across times when our customer wants to know how the organization is currently doing. They often want to know how they are measuring up against this time last year or against the projected measure. The most common examples of these request are a year-to-date calculation and a budget vs. actual analysis. In this blog post I will describe how to efficiently address these common business requests.
Year-To-Date (YTD) Calculations
Our customer has stated that they wish to show a YTD metric for sales which can be broken down into quarterly and monthly metrics as well. In all of my other blog posts I have modeled some dimension or fact to show you how to address the requirement at hand, in this scenario I will not. The reason is because we already have done the work to address this requirement in previous blogs. Let’s look how we already addressed this issue with an example.
In the star schema shown above we can see that we are storing purchase transactions by customer, product, and store on a daily basis. Looking at our date dimension we see that a we have designed this as a flattened hierarchy with a single date belonging to a month, quarter, and year. Because we have stored the date information in this way, we can easily sum this fully additive transaction fact information by one of the attributes (month, quarter, and year) of the date dimension.
SELECT DATE_YR_NBR
,PRDCT_ID
,PRDCT_DESC
,SUM(SALE_QTY) SALE_QTY
,SUM(SALE_TOT) SALE_TOTAL
FROM FACT_SALE
JOIN DIM_DATE ON SALE_DATE_KEY = DATE_KEY
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
WHERE EXTRACT(YEAR FROM SYSDATE) = DATE_YR_NBR
GROUP BY DATE_YR_NBR, PRDCT_ID, PRDCT_DESC
ORDER BY 5 DESC, 4 DESC;
SELECT DATE_MONTH_ID
,DATE_MONTH_NAME_YR
,PRDCT_ID
,PRDCT_DESC
,SUM(SALE_QTY) SALE_QTY
,SUM(SALE_TOT) SALE_TOTAL
FROM FACT_SALE
JOIN DIM_DATE ON SALE_DATE_KEY = DATE_KEY
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
WHERE EXTRACT(YEAR FROM SYSDATE) = DATE_YR_NBR
GROUP BY DATE_MONTH_ID
,DATE_MONTH_NAME_YR
,DATE_YR_NBR
,PRDCT_ID
,PRDCT_DESC
ORDER BY DATE_MONTH_ID, 5 DESC, 4 DESC;
An enhancement to the DIM_DATE dimension, which would address other potential requirements, would be to add seasonal or holiday advertising flags to the dimension allowing you to compare data at a year over year level for those seasonal or holiday advertising promotions which move from year to year. Again, the DIM_DATE dimension shows its power and flexibility in addressing customer requirements.
Budget vs. Actual Analysis
Another common requests from customers is to show budget vs. actuals. To address this situation, we only need to add a fact table to hold the budget data. Please remember to make sure the budget fact table is at the appropriate grain of the transaction fact, so as to be able to report at the lowest grain possible, and rolling up as needed.
In the above model I added a budget fact, rolling it up to the month, while the transaction fact shows the sale transactions by day. With this sale budget fact, we can precisely compare our month/year to date budget against actual sales. Below are a couple of examples of the queries that could be used to answer the requirement.
SELECT YR_KEY
,PRDCT_ID
,PRDCT_DESC
,SUM(BUDGET_QTY) BUDGET_QTY
,SUM(BUDGET_TOT) BUDGET_TOT
,SUM(SALE_QTY) SALE_QTY
,SUM(SALE_TOT) SALE_TOT
FROM (
SELECT MONTH_YR_KEY YR_KEY
,PRDCT_ID
,PRDCT_DESC
,SUM(SALE_QTY) BUDGET_QTY
,SUM(SALE_TOT) BUDGET_TOT
,0 SALE_QTY
,0 SALE_TOT
FROM FACT_SALE_BUDGET
JOIN DIM_MONTH ON SALE_MONTH_KEY = MONTH_KEY
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
WHERE EXTRACT(YEAR FROM SYSDATE) = MONTH_YR_KEY
GROUP BY MONTH_YR_KEY, PRDCT_ID, PRDCT_DESC
UNION
SELECT DATE_YR_NBR YR_KEY
,PRDCT_ID, PRDCT_DESC
,0 BUDGET_QTY
,0 BUDGET_TOT
,SUM(SALE_QTY) SALE_QTY
,SUM(SALE_TOT) SALE_TOT
FROM FACT_SALE
JOIN DIM_DATE ON SALE_DATE_KEY = DATE_KEY
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
WHERE EXTRACT(YEAR FROM SYSDATE) = DATE_YR_NBR
GROUP BY DATE_YR_NBR, PRDCT_ID, PRDCT_DESC
)
GROUP BY YR_KEY, PRDCT_ID, PRDCT_DESC
ORDER BY 5 DESC, 4 DESC;
SELECT MONTH_KEY
,MONTH_NAME_YR
,PRDCT_ID
,PRDCT_DESC
,SUM(BUDGET_QTY) BUDGET_QTY
,SUM(BUDGET_TOT) BUDGET_TOT
,SUM(SALE_QTY) SALE_QTY
,SUM(SALE_TOT) SALE_TOT
FROM (
SELECT MONTH_KEY
,MONTH_NAME_YR
,PRDCT_ID
,PRDCT_DESC
,SUM(SALE_QTY) BUDGET_QTY
,SUM(SALE_TOT) BUDGET_TOT
,0 SALE_QTY
,0 SALE_TOT
FROM FACT_SALE_BUDGET
JOIN DIM_MONTH ON SALE_MONTH_KEY = MONTH_KEY
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
WHERE EXTRACT(YEAR FROM SYSDATE) = MONTH_YR_KEY
GROUP BY MONTH_KEY, MONTH_NAME_YR, MONTH_YR_KEY, PRDCT_ID, PRDCT_DESC
UNION
SELECT DATE_MONTH_ID MONTH_KEY
,DATE_MONTH_NAME_YR MONTH_NAME_YR
,PRDCT_ID
,PRDCT_DESC
,0 BUDGET_QTY
,0 BUDGET_TOT
,SUM(SALE_QTY) SALE_QTY
,SUM(SALE_TOT) SALE_TOT
FROM FACT_SALE
JOIN DIM_DATE ON SALE_DATE_KEY = DATE_KEY
JOIN DIM_PRODUCT ON SALE_PRDCT_KEY = PRDCT_KEY
WHERE EXTRACT(YEAR FROM SYSDATE) = DATE_YR_NBR
GROUP BY DATE_MONTH_ID, DATE_MONTH_NAME_YR, DATE_YR_NBR, PRDCT_ID, PRDCT_DESC
)
GROUP BY MONTH_KEY, MONTH_NAME_YR, PRDCT_ID, PRDCT_DESC
ORDER BY MONTH_KEY, 5 DESC, 4 DESC;
As you can see from this blog adhering to the dimensional modeling standards will provide you with great flexibility and ease of use when answering period to date requests from your customer. Could you create physical fact tables to save these queries? Of course you can, but I recommend only creating additional data structures when absolutely necessary. If you find yourself repeatedly reprocessing YTD from previous years, then sure, go ahead and make a new fact table to store this data and reap the performance gains these data structures provide.