Adding new attributes to a dimension

We're an Oracle 10g warehouse site and still get nervous when adding new attributes to our ageing dimensions. The issue stems from not being able to reorder columns in Oracle. So your new attributes either sit at the bottom of the table (not good) or you have to do a create table new_dim as select a,b,c,d...n from old_dim and hope you don't miss a column. We wrote a function this week to at least help get all the column id's and avoid that terror moment when you realise you just dropped an attribute, maybe a T2 attribute with lots of history...eeeek!
The code and more info is at http://blog.une.edu.au/robbi/2009/02/20/attribute-dementia/ if you're interested. Hope its ok to post blog links Ms Moderator!

robhale wrote: Hope its ok to post blog links Ms Moderator!

Absolutely!

Why is it bad to add the new columns to the end of the table? The only hit in performance you'll have is if you've managed to put all of your null columns at the end of the table and then adding a new not null column at the end. This performance hit should not be significant.

Ah, fair question. The reason for having columns in a specifc order is not performance-related. We're a university, so our main student dimension has attributes about the student - name, address, postal code etc. Yesterday we decided to add an attribute for an email salutation which was a derivation of given name(s) and preferred name where stated - so we can address an email 'Dear Rob' or 'Hi Rob'.

If we'd just put that attribute at the end of the table it would be separate from all the other name-related attributes. Obviously we have ways of shuffling the order in the presentation layer but it makes our job of managing 'back of house' so much easier if they are visibily and physically together.

Another point is that at the 'end' of the table are all our dimensional meta columns for SCD management - row_last_update_dt, row_create_dt, row_is_current etc. Its nice to have all them neatly at the end of every dimension and not shuffled up the order.

So you make a valid point and I must admit the temptation is just to add columns and not worry about the above at times but overall it is a good strategy and one we're sticking with for now.

You could create a view. Or rename the old table, create the new one, load the new one. In the grand scheme of things, it doesn't get much more straightforward. Simple testing would verify the values were copied over correctly.

Creating a view is masking over the underlying physical arrangement, as I said - it helps when managing large dimensions to have everything physically located when browsing tables in the database itself. Maintaining and managing yet another view (when we already have many associated with role-playing dimensions) is not an overhead I'd like to have just to resolve this issue.
If you check out the code I linked to you'll see that 'create the new table' is precisely what we do - the function just helps ensure you get every single attribute and don't miss any. I agree, indeed it is simple, but it is also simple to miss a column and anything that helps to lessen that risk surely has to be good. In my experience simple testing does pick things up but relying on this alone is not something I'd advise. Also, I've found that the simple things are often the ones that trip us up or get overlooked.
Anyway, feel free to ignore the code, it was just offered as something that might be of use.

I know this is a pain in Oracle. Have you ever looked at online table redefinition?

Have a look at the documentation.

Also there is a good post here: http://www.orafaq.com/node/4

Hi ubethke, thanks for the post. I did actually check this stuff out last month but it seems more suited to an OLTP database than a nightly -updated warehouse. We have the luxury of the warehouse being fixed between each nightly ETL so don't have to capture DML using materialised views. I notice that even with DBMS_REDEFINITION you still need to create the new table manually in the form:

SQL> exec dbms_redefinition.start_redef_table('scott', 'emp', 'emp_work', -
> 'emp_id emp_id, ename ename, salary salary', -
> DBMS_REDEFINITION.CONS_USE_PK);

So I think there is (currently) no shortcut to the problem and it just needs a bit of patience and care and of course lots of testing.