We are trying to get "missing rows" from our fact table and, in the end, join them to our dimension table.
Trying the approach here: https://www.experts-exchange.com/questions/27968510/Oracle-11g-How-to-insert-missing-rows-in-to-a-sequence-based-on-a-date-and-a-group.html
proved way too resource intensive and for a single row takes over an hour to return.
The fact table has 12 Million records and the dimension table has 1200 or so. The issue we are facing is that there are not enough dimensions in the dimension table to order by it alone and use a LEAD/LAG approach to fill in the missing data with an outer join.
The fact table has orders in it, by date and interval. One date has to have 24 intervals (hourly) for the dimension table to lookup. So 21 = X, 20 = Y, etc.
Without having the fact table populated from a vendor API with all 24 periods of time (intervals), we cannot get a full set of data which is causing issues downstream.
We always need every interval for every day, although, some intervals come in at 96 (fifteen minute periods of time). This is where we need to get the data back with a full 24 or 96 records, join to the dimension table and get the accurate flag associated with the record.
Same issue as described above in the link, just a small twist on it.
Any suggestions would be greatly appreciated.