Adding new attributes to a dimension

View previous topic View next topic Go down

Adding new attributes to a dimension

Post  robhale on Fri Feb 20, 2009 5:48 am

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!
avatar
robhale

Posts : 10
Join date : 2009-02-03
Location : NSW, Australia

View user profile http://blog.une.edu.au/robbi

Back to top Go down

Re: Adding new attributes to a dimension

Post  Nancy Rinn on Fri Feb 20, 2009 10:24 am

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

Absolutely!

Nancy Rinn

Posts : 23
Join date : 2008-08-18
Location : MN

View user profile http://www.kimballgroup.com

Back to top Go down

Re: Adding new attributes to a dimension

Post  BoxesAndLines on Fri Feb 20, 2009 2:27 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Adding new attributes to a dimension

Post  robhale on Fri Feb 20, 2009 5:40 pm

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.
avatar
robhale

Posts : 10
Join date : 2009-02-03
Location : NSW, Australia

View user profile http://blog.une.edu.au/robbi

Back to top Go down

Re: Adding new attributes to a dimension

Post  BoxesAndLines on Sun Feb 22, 2009 3:25 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Adding new attributes to a dimension

Post  robhale on Sun Feb 22, 2009 4:18 pm

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.
avatar
robhale

Posts : 10
Join date : 2009-02-03
Location : NSW, Australia

View user profile http://blog.une.edu.au/robbi

Back to top Go down

Re: Adding new attributes to a dimension

Post  ubethke on Mon Mar 02, 2009 12:55 pm

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

ubethke

Posts : 28
Join date : 2009-02-03

View user profile http://www.business-intelligence-quotient.com

Back to top Go down

Re: Adding new attributes to a dimension

Post  robhale on Mon Mar 02, 2009 5:51 pm

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.
avatar
robhale

Posts : 10
Join date : 2009-02-03
Location : NSW, Australia

View user profile http://blog.une.edu.au/robbi

Back to top Go down

Re: Adding new attributes to a dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum