Recently a client wanted all the row counts from specific tables on a given scheme. The list of tables to have their row counts documented was at about 50, which accounted for about 90% of the table objects in the scheme. At first I was going to use the “Update Row Count” feature in OBI as this is where the project was grounded. Then, I thought there had to be an easier approach. Using the script below I was able to get the counts and simply eliminate the objects that where in the not needed 10%. The database we were using is Oracle so the PL/SQL won’t work for MS SQL Server. I will try to find the old SQL Server code I used to do this on a previous project and post it here as well at a later time.
set serveroutput on
for x in (select table_name
order by table_name) loop
'select count(*) from '
||x.table_name into row_cnt;
a variation of this is:
Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',
to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
order by table_name
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.
Ultimately the goal of commentary in OBIEE is to have a system for persisting feedback, creating a call to action, and recognizing the prolific users.