Datawarehouse: Recycle Rejects [Archive]

jweyers

01-24-02, 10:16

Hi - If anyone could help, it would be much appreciated.

Sometimes in our datawarehouse, we receive the updates for the facts before the dimensions come through. eg, sometimes we'll receive sales for an item, before the new item details can be added to the Item dimension. So the sale is rejected (we have reject tables for each fact).

But now we'd like to be able to recycle these rejects - putting them into the production table. The problem is, for every load, we have an audit key. Each fact is assigned the load's audit key, regardless of whether it is rejected or not. But we can't just load the rejected fact using the same audit key, as we may not manipulate the audit details. And we can't just assign a new audit key, because if a specific load needs to be rolled back for some reason, then the new additions will not be rolled back. And rejects can be recycled for 3 months - in which case there would be far too many audit keys linked to the original load.

Any ideas? How is this handled elsewhere?
Thanks,
Janine

jimw

01-20-03, 15:17

Although this probably will not help you Janine, I have a related question based on your comment below.

>(we have reject tables for each fact).

I am inheriting a database and have recently received a new requirement from the customer - they would like us to be able to store partially populated "objects" in the database. What this means is that they will populate only some of the columns of a table and hope to populate the rest of them sometime in the future. We have built our database tables with a large number of constraints as the underlying C++ code that accesses the data does little to no error checking (i.e. all references are assumed to be NOT NULL). The user interface application also does little error checking of the data itself - in fact it uses the constraint checking of the database to enforce most of the business rules.

I can imagine a number of ways of doing this and I am looking for opinions on which approach may be better and have found little-to-nothing on the web.

We are currently using Oracle 8i 1.7 (although I believe this is more of a database design question than an implementation question).

Some approaches :

1) Keep the very constrained table, putting any rejected inserts and updates into a rejects table (basically a table that has the same format as the original table, however, without the constraints). Therefore the C++ code will not have to change, but the GUI code will have to look into two tables to edit the object.

2) Relax the constraints on the table, and build a view that performs the validation of the business rules. In this way there would be one underlying table with all of the objects, and then the view would present to the C++ code just the objects that are valid.

3) Build a table with no constraints, adding data into the table. Then through an AFTER INSERT or AFTER UPDATE trigger, move or copy the object to a more constrained table that could be used by the C++ code.

4) Build the single table with no constraints and add a column hold a TRUE/FALSE on whether the object is valid according to the business rules. This column could be populated with an AFTER INSERT and AFTER UPDATE trigger.

5) Remove all of the constraints and make the C++ programmer and the GUI programmer handle all of the business rules.

I believe that any of the approaches could be made to work, just looking for guidance on whether there is a more "standard or accepted" way of doing this.

Is there a good database design book out there? - something comparable to the Gang of Four object Oriented Book "Design Patterns".

Thanks,

Jim W