• Products
    • Observe

      Keep tabs on your world in real-time, reducing a cacophony of activity from massive amounts of raw content into orderly, easy to consume data

    • Orient

      Our low-code, composable, distributed, and event-driven predictive analytics workflow engine mines for actionable insights hidden in data at any scale

    • Dominate

      This insight interaction and decision support application takes you beyond self-service, visual exploration of piles of insights & provides auto-ML powered actionable, scenario-specific advice to achieve the goals you specify

  • Markets
    • Government

      At BigBear.ai, we take pride in providing mission-critical services and solutions to our government customers. This includes Data Analytics, Systems Engineering, and Cyber services.

    • Commercial Markets

      BigBear.ai provides innovative AI solutions to companies across commercial markets, ranging from Space to Media to Shipping and Transportation.

  • Company
    Just Announced
    Press Release
    BigBear.ai Names Former Intelligence Officer Tony Barrett as President of Cyber and Engineering Sector
    • Col – 1
      • About

        We help governments and businesses make the decisions that change markets and define outcomes

      • Investor Relations

        We operationalize artificial intelligence and machine learning at scale through our end-to-end platform

      • Partners

        We work with our technology partners to customize products for the private and public sectors

    • Col – 2
      • Team

        Our executive team brings decades of world-class experience

      • Newsroom

        News articles from our press room

  • Careers
    Apply Now
    AWS Principal Architect
    Location:

    Columbia, Maryland

    Description:

    Our AWS CCoE is a collaborative, diverse, and highly-capable team which is key to Company growth. We are seeking an AWS Cloud Architect to be a key member of our AWS Cloud Center of Excellence. This position will be based out of Columbia, MD and is fully remote.

    Category: Engineering

    • Col – 1
      • Explore Jobs
        • Returning Applicant Login
        • Current Employee Login
      • Culture
      • Benefits
      • Military and Veterans
      • Product and Technology
      • Join Our Talent Community

        Sign up to receive personalized alerts and stay up to date on job openings right for you

  • Resources
    Featured
    Video
    Panel Discussion: AI/ML Applications to Support DHS
    • Col – 1
      • Blog

        See what’s new in artificial intelligence, machine learning, and data analytics

      • Resource Library

        Browse our resource library and discover more about our products and solutions

      • Newsroom

        News articles from our press room

  • Contact
BigBear.ai
  • Products
    • Observe

      Keep tabs on your world in real-time, reducing a cacophony of activity from massive amounts of raw content into orderly, easy to consume data

    • Orient

      Our low-code, composable, distributed, and event-driven predictive analytics workflow engine mines for actionable insights hidden in data at any scale

    • Dominate

      This insight interaction and decision support application takes you beyond self-service, visual exploration of piles of insights & provides auto-ML powered actionable, scenario-specific advice to achieve the goals you specify

  • Markets
    • Government

      At BigBear.ai, we take pride in providing mission-critical services and solutions to our government customers. This includes Data Analytics, Systems Engineering, and Cyber services.

    • Commercial Markets

      BigBear.ai provides innovative AI solutions to companies across commercial markets, ranging from Space to Media to Shipping and Transportation.

  • Company
    Just Announced
    Press Release
    BigBear.ai Names Former Intelligence Officer Tony Barrett as President of Cyber and Engineering Sector
    • Col – 1
      • About

        We help governments and businesses make the decisions that change markets and define outcomes

      • Investor Relations

        We operationalize artificial intelligence and machine learning at scale through our end-to-end platform

      • Partners

        We work with our technology partners to customize products for the private and public sectors

    • Col – 2
      • Team

        Our executive team brings decades of world-class experience

      • Newsroom

        News articles from our press room

  • Careers
    Apply Now
    AWS Principal Architect
    Location:

    Columbia, Maryland

    Description:

    Our AWS CCoE is a collaborative, diverse, and highly-capable team which is key to Company growth. We are seeking an AWS Cloud Architect to be a key member of our AWS Cloud Center of Excellence. This position will be based out of Columbia, MD and is fully remote.

    Category: Engineering

    • Col – 1
      • Explore Jobs
        • Returning Applicant Login
        • Current Employee Login
      • Culture
      • Benefits
      • Military and Veterans
      • Product and Technology
      • Join Our Talent Community

        Sign up to receive personalized alerts and stay up to date on job openings right for you

  • Resources
    Featured
    Video
    Panel Discussion: AI/ML Applications to Support DHS
    • Col – 1
      • Blog

        See what’s new in artificial intelligence, machine learning, and data analytics

      • Resource Library

        Browse our resource library and discover more about our products and solutions

      • Newsroom

        News articles from our press room

  • Contact
Home Blog Period-To-Date Facts
Blog

Period-To-Date Facts

Jim McHugh
June 20, 2019
  • Share
  • Share

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.

product_sale_428x400

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.

product_sale_budget_500x310

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.

Leave a comment Cancel reply

Your email address will not be published. Required fields are marked *

BigBear.ai

2022 BigBear.ai • All Rights Reserved.

  • Privacy Policy
Corporate Headquarters

6811 Benjamin Franklin Drive, Suite 200
Columbia, MD 21046
Tel: 410.312.0885 • Email: [email protected]

CareersContact