ETL Re-aggregation (REP_REBUILD_LOG)

[Adam_W]

I had some problems with historical reporting this week and now I need to force ETL to re-aggregate some data.  At the moment, some reports are missing their daily stats, but if I run an hourly report the data is present.

In Datamart 7.1 you could run a simple statement to delete rows from REP_REBUILD_LOG from the date you require aggregation.  However, it looks like nothing has been written to this table since I upgraded to 7.2.

The data is definitely there so has the method of aggregation changed in 7.2?

[Adam_W]

OK I found a script for 7.2 which should achieve my aim, however it's for an Oracle DBMS.  Long shot I know, but is anyone clever with both Oracle and SQL able to tell me how to achieve the same thing in SQL?!

-------------------------------------

whenever sqlerror exit failure

set serveroutput on

spool update



create or replace procedure EXECUTE_IMMEDIATE(cmd in varchar2) is

cur integer;

rc integer;

begin

cur := dbms_sql.open_cursor;

dbms_sql.parse(cur,cmd,dbms_sql.native);

rc := dbms_sql.execute(cur);

dbms_sql.close_cursor(cur);

exception

when others then

if dbms_sql.is_open(cur) then

dbms_sql.close_cursor(cur);

end if;

raise;

end;

/



declare

cur integer;

vname varchar2(50);

aname varchar2(50);

tname varchar2(50);

rc integer;

begin



cur := dbms_sql.open_cursor;

dbms_sql.parse(cur,

'select a.REP_VIEW_ID, a.PAR_AGG_COL_NAME, b.TABLE_NAME '||

'from REPORT_VIEW a, REP_TO_TAB c, REPORT_TABLE b '||

'where a.PAR_REP_VIEW_ID is not null '||

'and a.PAR_REP_VIEW_ID=c.REP_VIEW_ID '||

'and c.TABLE_ID=b.TABLE_ID '||

'and b.INFO_TYPE=''TIME_DIM'' '||

'and b.PHYSICAL_TYPE=''TABLE'' '||

'and a.PAR_AGG_COL_NAME=''AGG_BY_HOUR'' ',dbms_sql.native);



dbms_sql.define_column(cur, 1, vname, 50);

dbms_sql.define_column(cur, 2, aname, 50);

dbms_sql.define_column(cur, 3, tname, 50);



delete PENDING_AGG;



rc := dbms_sql.execute(cur);

LOOP

IF dbms_sql.fetch_rows(cur) > 0 then

dbms_sql.column_value(cur, 1, vname);

dbms_sql.column_value(cur, 2, aname);

dbms_sql.column_value(cur, 3, tname);

EXECUTE_IMMEDIATE('insert into PENDING_AGG( REP_VIEW_ID, AGG_KEY, CNUMBER) select distinct '||vname||','||aname||', 0 '

||'from '||tname||' where to_char(END_TIME, ''YYYYMMDD'') >= ''yyyymmdd'' ');

ELSE

EXIT;

END IF;

END LOOP;

dbms_sql.close_cursor(cur);

DBMS_OUTPUT.PUT_LINE('PENDING_AGG table updated');

commit;



end;

/



drop procedure EXECUTE_IMMEDIATE

/