Something that we all have to do from time to time in a dimensionally modelled world is add new attributes to an aging dimension. Our Student dimension is a regular for updates and minor surgery and consequently the process has increased risk as there are more attributes that could get forgotten in a rebuild. Accidently rebuilding a dimension and missing an attribute is a disaster, especially if that has been T2′d over time. It may be impossible to recover without a database restore and if you only noticed today then…

Incredibly, in Oracle 10g there is still no way to reorder columns in a physical table so the only way to neatly add an attribute to a dimension is to add the new column to the end of the physical table and then create a new copy of the table using ‘create table dimension_new as select a,b,c,d..n from dimension_old.

This is pretty straightforward when your dimension has 10 attributes but what about when it has 50? SQL Developer, TOAD and other tools all have ways of letting you get the DDL for a table, but you usually still need to fiddle with the output (remove column types and lengths etc) before you can run it. That fiddling gives opportunity for error.

So here is the solution that we created today - a function to call with the before and after table name that gives you a create statement for the new table. You then just need to put the new column in the right spot. It’s not pretty but it works and it reduces the risk. I used it to add a new attribute to our Student dimension tonight and life was less complicated. Big thanks to Coops for hacking this together in about 5 minutes. Please take the code and use it if you can. Even better, improve it and post it back.

2nd March 2009 update: Check here for a Kimball forum discussion on this

create or replace function bi_column_shift_2k(p_old_table_name varchar2,
p_new_table_name varchar2) return varchar2 as

v_create varchar2(100) :=
'create table '|| p_new_table_name || ' as select ';
v_from   varchar2(50) :=
' from '||p_old_table_name;
v_column_list
varchar2(2000);
v_column_shift_2k varchar2(2000);

  cursor
get_table_cols_cur is
    select distinct
ltrim(SYS_CONNECT_BY_PATH(column_name, ','),',')
column_name
    from
       (
       select
         tc.table_name,
tc.column_name,
         count(*) OVER ( partition by tc.table_name )
cnt,
         ROW_NUMBER () OVER ( partition by
tc.table_name
                              order by tc.column_id )
seq
       from sys.all_tab_columns tc
       where tc.table_name =
p_old_table_name
       order by
column_id
        )
    where
      seq=cnt
    start
with
      seq=1
    connect by prior
      seq+1=seq
    and
prior
      table_name = table_name;
  get_table_cols_rec
get_table_cols_cur%ROWTYPE;

begin

  for get_table_cols_rec in
get_table_cols_cur loop
    v_column_list :=
get_table_cols_rec.column_name;
  end loop;

  v_column_shift_2k :=
v_create || rtrim(v_column_list,', ') || v_from;

  return
v_column_shift_2k;

end;

• Previous Entry: e-Motional Outpourings
• Next Entry: War of Attrition (Part 1)