• 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
    Julie Peffer and Josh Kinley
    Press Release
    BigBear.ai Announces Key Leadership Appointments in Finance and Corporate Development
    • 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
    Data Scientist/Analyst
    Location:

    San Diego, California

    Description:

    BigBear.ai an exciting opportunity for a Data Scientist/Analyst to join our team in San Diego. This position can be onsite in San Diego, CA office or remote.

    Category: Computer/Data Science

    • 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
    Fostering AI Adoption - Dr. Reggie Brothers - GovCon DIFFERENT Podcast
    Dr. Reggie Brothers
    Video
    Fostering AI Adoption - Dr. Reggie Brothers - GovCon DIFFERENT Podcast
    • 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
    Julie Peffer and Josh Kinley
    Press Release
    BigBear.ai Announces Key Leadership Appointments in Finance and Corporate Development
    • 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
    Data Scientist/Analyst
    Location:

    San Diego, California

    Description:

    BigBear.ai an exciting opportunity for a Data Scientist/Analyst to join our team in San Diego. This position can be onsite in San Diego, CA office or remote.

    Category: Computer/Data Science

    • 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
    Fostering AI Adoption - Dr. Reggie Brothers - GovCon DIFFERENT Podcast
    Dr. Reggie Brothers
    Video
    Fostering AI Adoption - Dr. Reggie Brothers - GovCon DIFFERENT Podcast
    • 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 Late Arriving Facts
Blog

Late Arriving Facts

Jim McHugh
September 21, 2019

Late arriving facts are those transactions which are delayed in arriving to the warehouse. For Type I data warehouses there is no real impact but for Type II warehouse models there is a minor challenge that the ETL developer must remember to handle to ensure the late arriving transaction corresponds with dimensional attributes at the time of the transaction.

If we have a Type I warehouse or are always assured that we only receive fact data at the same time as the dimensional data then we can use the following simple query where we find the current active record for the dimension’s natural ID which is found in the fact record:

SELECT dimensional_key

FROM dimension_table

WHERE dimension_natural_id = {natural_id from fact}

AND dimension_actv_rcrd_fl = 1;
slide1_500x121
Figure 1

Taking a look at the Type II data warehouse challenge, we cannot assume that the active dimensional record is the correct record for the fact. Therefore, we need to modify the ETL workflow process (Figure 1) to address the challenge of the possible changing of dimensional data since the “old” fact occurred.

To address this issue, we need to add an additional check when associating the dimensional keys to the fact table. We must find the dimensional key value where the transaction date key is between the dimensional active record start and end dates (Figure 2 & Figure 3) to ensure the accuracy of the data at the time of the transaction.

slide2_500x63
Figure 2
slide3_500x71
Figure 3

The query needed to find this record is slightly different since we need to find the record between two dates instead of by finding the most active record:

SELECT dim_key

FROM dimension_table

WHERE dim_natural_id = {natural_id from fact}

AND {trnsctn_dt from fact} BETWEEN dim_actv_rcrd_strt_dt AND dim_actv_rcrd_end_dt;

Indexing Tip: Only index the natural identifier on the dimension for best performance. Including the date columns in the index will not improve performance. It will only make your index larger and thereby reduce the performance of the index. Remember dimensions are supposed to be wide and shallow. If you have a rapidly changing dimension you will need to find a way to eliminate the attributes causing the dimensional change.

In conclusion, make sure you know your data. Profiling of your data and full knowledge of your customer’s business process is critical to a successful implementation of your data warehouse.

In my next blog, I will discuss the challenge of late arriving dimensional records and the impact it has on the accuracy of the data in the data warehouse.

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