Building Making It Happen
Building Making It Happen
  Sign-in         Register
    
Principles and Rules Listing Page
Handling of Null foreign Keys in fact tables
There are always situations where the key linking the fact table to a dimension table is NULL (meaning it is not available or applicable).
 
This page of 'Principles and Rules' is linked to:  Data Warehousing, Data Analysis/OLAP, BI platform Tools Evaluation, BI business intelligence end-to-end view,


For example "Order Delivery Date" key in "Orders" fact table, is a future date, as the order is still under processing. In this case the date is not available. The best way to handle it is to give a surrogate key, and have that surrogate key link to a record having a description (say) this date is yet not applicable. In case of an accumulating fact table, when the date happens, one can over-write the record.

Sometimes that foreign key is not available, due to the data quality issues of the source systems. In this case the approach is similar, and that is to assign a special surrogate key linking to a record in the dimension table. For example, if the invoice number is not available, you can assign a special surrogate key, which will link to a special record in the dimension table, which will have the description the "invoice number is not available". If you want to assign more specific reasons related to the invoice number not being available, you can think of having multiple special surrogate keys linking to multiple special records, with each carrying a different description. That helps you at a later stage in case you want to do some data enhancement/fixing.
The same approach will be applicable in case the fact.

   Access more details on this page   

Quick Feedback- Was this information helpful ?
Relevant Links to this page
Principles & Rules → Data Warehouse application is not limited to Analytics → Principles & Rules → Store as much detailed and granular data in data warehouse as possible → Principles & Rules → Data Normalization is not the best approach in Dimensional modeling → Principles & Rules → Keep the same names and definitions for all data elements → Principles & Rules → You cannot have a super-flexible Data warehouse → Principles & Rules → Dimensional models can be extensible and scalable → Principles & Rules → Data Marts should be ideally based upon a business process and not on a department. → Principles & Rules → Business Intelligence competency groups should be well-linked with business → Practice Techniques → Aggregation Queries on slowly changing Dimensions → Practice Techniques → Documenting your data-integration system → Principles & Rules → For a Data Warehouse/Data-Mart solution, analyze well, but be decisive → Principles & Rules → Maintain a trail of the key dimensional elements from source system to loaded → Principles & Rules → Conformed dimensions are must for cross-drilling → Practice Techniques → Checksum Approach for identifying the changed records from source systems → Principles & Rules → Dimensional model has to be aligned to the Entity-Relationship → Principles & Rules → Always Use Conformed Dimensions → Principles & Rules → You may not be a able to have a perfect ETL → Practice Techniques → Handling Sparse Dimensional tables → Principles & Rules → Do not separate the parent and child line item data → Practice Techniques → Managing time-stamps across multiple time-zones → Practice Techniques → Recording events in multiple currencies → Practice Techniques → Handle different units of measure in the same fact table → Principles & Rules → Handling of Null foreign Keys in fact tables → Principles & Rules → Dimension Attributes as NULL → Principles & Rules → Don't rely too much on Meta Data Tools to enforce Business Intelligence → Principles & Rules → Don't wait for universal models for Data Marting → 
 
Back
Featured Pages
Data Group Master
Customer Data Correction and Techniques
Don't create a hype on Data Quality
Don't fix before root cause Analysis

Make 'Executable' Strategy
Maximize Results
Maximize People
Manage Execution

Featured Pages
System Quality Assessment Tool
Data Warehouse Project Definition
Drill (horizontal) and Cross (horizontal) Navigation
Source to Target Table