Ever had that problem when, in reporting, some records just wouldn’t show because, basically, you were dealing with bad data? It happens more too often that data is loaded with empty id fields, simply because someone forgot to foresee an alternative or because of the all to known these-fields-will-never-be-empty assumption!
If you are working with a simple star schema, this can be solved pretty easily. One possibility is to provide a ‘default’ record for every dimension so that this ‘default’ record will match with all the fields that have an empty id field for that dimension. You can add this ‘default’ record in your ETL (or ELT for those hipsters out there!) step (if you have one), or you can use a nifty little feature called instead of triggers, in case you don’t have an actual step that transforms the data. I worked with this approach on a small project and found out it can be very useful.
The idea here is that you create an updatable view, where the logic needed to create the extra ‘default’ record is embedded in the view. Next, create the instead of triggers on that view, so that update, delete or insert statements can still be used. The major advantage here is that you can use that view as a table in your code, but you’ll always have that ‘default’ record, just in case.
I created a small model to illustrate this. Firstly, I created some products and some super-salespeople, both neatly suited in a dimension. Next, I used the dbms_random.value package to generate some dummy data for my fact table (if you need these scripts, just let me know in the comments below). The model is based on the logic that every day, just one person made a sale, just to keep it simple.
You can see my awesome model, with randomised data, at work in the screenshot above. Notice that there is no data for example the 2nd of december, or the 9th, or the 25nd (understandable), or the 26th! I suspect this can be caused by some faulty data, because my salespeople are some of this great universe’s finest!
I already have my products table, so I’ll start by creating the view that adds the default member;
create view v_dim_product as
select ID, name, description from dim_product
select 0, 'n/a', 'not available' from dual;
Next, I add the instead of triggers;
One for the inserts;
CREATE OR REPLACE TRIGGER ioft_insert_product
INSTEAD OF INSERT ON v_dim_product
FOR EACH ROW
INSERT INTO dim_product(ID, name, description) VALUES(:NEW.ID, :NEW.name, :NEW.description);
One for the updates;
CREATE OR REPLACE TRIGGER ioft_update_product
INSTEAD OF UPDATE ON v_dim_product
FOR EACH ROW
UPDATE dim_product SET name = :NEW.name, description = :NEW.description WHERE id = :OLD.id;
And one for the deletes;
CREATE OR REPLACE TRIGGER ioft_delete_product
INSTEAD OF DELETE ON v_dim_product
FOR EACH ROW
DELETE FROM dim_product WHERE id = :old.id;
Since I selected the id of my default member to be ‘0’, I’ll add the following complex join condition in my reporting model (OBIEE in this case).
And that’s about it! I can now use the view in my code as if it was a table and I’ll never lose the default member, or have the problem that some data won’t show.
Adding all of this, my report now shows data for all the days in the month (yes, even on the 25th, but who can refuse the friendly neighbourhood, euuhhmm, reporter?), even though the salespeople forgot to register the product!
Good luck and happy newyear!
PVE / JUVO