1. Which teacher did not teach any student?
2. Which student was not taught by any teacher?

Why not? Because fact-less fact table only stores the positive scenarios (like student being taught by a tutor) but if there is a student who is not being taught by a teacher, then that student’s key does not appear in this table, thereby reducing the coverage of the table.

Coverage fact table attempts to answer this – often by adding an extra flag column. Flag = 0 indicates a negative condition and flag = 1 indicates a positive condition. To understand this better, let’s consider a class where there are 100 students and 5 teachers. So coverage fact table will ideally store 100 X 5 = 500 records (all combinations) and if a certain teacher is not teaching a certain student, the corresponding flag for that record will be 0.

SELECT t.* 
FROM Sales t
WHERE t.sales_date > (select nvl(
                                 max(b.loaded_until), 
                                 to_date('01-01-1900', 'MM-DD-YYYY')
                                )
                      from batch b
                      where b.status = 'Success');

where “batch” is a separate table maintained at target system having minimal structure and data like below

Batch_ID  Loaded_Until  Status
1         22-Mar-2012   Success
2         23-Mar-2012   Success

However, things may get pretty complicated if your fact is a special type of fact called “snapshot fact”. Let’s understand them below.

Loading Incident Fact

Incident fact is the normal fact that we encounter mostly (and that we have seen above in our sales table example). Records in these types of facts are only loaded if there are transactions coming from the source. For example, if at all there is one sale that happens in the source system, then only a new sales record will come. They are dependent on some real “incident” to happen in the source hence the name incident fact.

Loading Snapshot Fact

As opposed to incident fact, snapshot facts are loaded even if there is no real business incident in the source. Let me show you what I mean by using the above example of customer and sales tables in OLTP. Let’s say I want to build a fact that would show me total revenue of sales from each customer for each day. In effect, I want to see the below data in my fact table.

Sales fact table (This is what I want to see in my target fact table)

Date          Customer    Revenue
22-Mar-2012   John        6.50
22-Mar-2012   Ryan        2.00
23-Mar-2012   John       10.50
23-Mar-2012   Ryan        2.00
23-Mar-2012   Bakers'    75.00

even if no sales was made to Ryan on 23-Mar, we still show him here with the old data. Similarly for John, even if goods totaling to $4.00 was sold to him on 23-Mar, his record shows the cumulative total of $10.50.

Now obviously the next logical question is how to load this fact using incremental loading? Because incremental loading only brings in incremental data – that is on 23rd March, we will only have Bakers’ and John’s records and that too with that day’s sales figures. We won’t have Ryan record in the incremental set.

Why not a full load

You can obviously opt-in for full load mechanism as that would solve this problem but that would take the toll on your loading performance.

The solution?

One way to resolve this issue is: creating 2 incremental channels of loading for the fact. 1 channel will bring in incremental data from source and the other channel will bring in incremental data from the target fact itself. Let’s see how does it happen below. We will take the example for loading 23-Mar data.

Channel 1: Incremental data from source

Customer    Revenue 
John        4.00
Bakers'    75.00

Channel 2: Incremental data from target fact table (last day’s record)

Customer    Revenue
John        6.50
Ryan        2.00

Next we can perform a FULL OUTER JOIN between the above two sets to come to below result

John       10.50
Ryan        2.00
Bakers'    75.00