[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_load_dim.delete;
l_dim_lock.delete;
l_dim_lock.delete(l_dim_lock.count);
l_dim_level_delete dim_level_delete_tv;
l_dim_delete_flag boolean;
bsc_aw_utility.g_options.delete;
l_oo.delete;
/*we cannot get multi locks if there are deletes. so we first pre-load delete values into memory. if deletes are involved, we get full lock */
load_dim_delete(p_dim,l_dim_property,l_dim_level_delete,l_dim_delete_flag);
lock_dim_objects(p_dim,l_dim_delete_flag);
if l_dim_delete_flag then
merge_delete_values_to_levels(l_dim_level_delete);
l_oo.delete;
/*we now need to handle any deletes*/
if l_dim_delete_flag then
execute_dim_delete(l_dim_level_delete);
clean_bsc_aw_dim_delete(l_dim_level_delete);
delete are handled in the following way
delete table has 2 columns. dim_level and delete_value. say we have geog dim. city,state,country
we want to delete all cities in ca and ca
the table has
'city' 'SF'
'city' 'LA'
'state' 'CA'
the program already has support for marking limit cubes. once the program runs, this already has happened.
we now need to clean up the values from the dim.
Q:if we clean up CA and we still retail some cities in ca, what will happen to the relation. for example, sacramento
will say parent=ca while ca is gone. will agg on this relation error out? No. did a prototype to verify this. if CA is gone,
AW will take care of removing CA from the relation or at-least not considering it anymore
*/
procedure load_dim_delete(
p_dim varchar2,
p_dim_property varchar2,
p_dim_level_delete in out nocopy dim_level_delete_tv,
p_delete_flag out nocopy boolean) is
--
l_level varchar2(300);
p_delete_flag:=false;
p_dim_level_delete('ALL').delete_name:='ALL';
p_dim_level_delete('ALL').delete_values.delete;
l_oo.delete;
bsc_aw_utility.merge_value(p_dim_level_delete('ALL').delete_values,l_oo(i).object);--ALL will hold the levels involved
load_delete_dim_level_value(l_oo(i).object,upper(l_oo(i).object),p_dim_level_delete);
if p_dim_level_delete(l_oo(i).object).delete_values.count>0 then
p_delete_flag:=true;
l_oo.delete;
bsc_aw_utility.merge_value(p_dim_level_delete('ALL').delete_values,l_oo(i).object);--ALL will hold the levels involved
load_delete_dim_level_value(l_oo(i).object,upper(l_level),p_dim_level_delete);
log_n('Exception in load_dim_delete '||sqlerrm);
/*5064802. cannot delete in multi mode. pre-load deletes into memory. if there are any deletes, then get rw lock */
procedure load_delete_dim_level_value(
p_dim_level varchar2,
p_select_level varchar2,--useful in case of rec dim. we need to delete the virtual parent level also
p_dim_level_delete in out nocopy dim_level_delete_tv) is
l_stmt varchar2(4000);
l_stmt:='select distinct delete_value from bsc_aw_dim_delete where dim_level=:1';
log(l_stmt||' '||p_select_level||bsc_aw_utility.get_time);
p_dim_level_delete(p_dim_level).delete_name:=p_dim_level;
p_dim_level_delete(p_dim_level).delete_values.delete;
open cv for l_stmt using p_select_level;
fetch cv bulk collect into p_dim_level_delete(p_dim_level).delete_values;
log('Fetched '||p_dim_level_delete(p_dim_level).delete_values.count||' rows'||bsc_aw_utility.get_time);
log_n('Exception in load_delete_dim_level_value '||sqlerrm);
procedure execute_dim_delete(p_dim_level_delete dim_level_delete_tv) is
l_levels dbms_sql.varchar2_table;
l_levels:=p_dim_level_delete('ALL').delete_values; --levels also include virtual rec dim parent level
delete_dim_level_value(l_levels(i),p_dim_level_delete(l_levels(i)).delete_values);
log_n('Exception in execute_dim_delete '||sqlerrm);
/*bsc_aw_dim_delete is created by loader. so keep the sql dynamic
5064802: when we delete values, we cannot be in multi attach mode. must get full rw lock
lock_dim_objects will lock the dim in full mode if there are deletes reqd*/
procedure delete_dim_level_value(p_dim_level varchar2,p_delete_values dbms_sql.varchar2_table) is
Begin
for i in 1..p_delete_values.count loop
bsc_aw_dbms_aw.execute('mnt '||p_dim_level||' delete '''||p_delete_values(i)||'''');
log_n('Exception in delete_dim_level_value '||sqlerrm);
procedure clean_bsc_aw_dim_delete(p_dim_level_delete dim_level_delete_tv) is
l_levels dbms_sql.varchar2_table;
l_levels:=p_dim_level_delete('ALL').delete_values;
l_stmt:='delete bsc_aw_dim_delete where dim_level=:1';
log('Deleted '||sql%rowcount||' rows '||bsc_aw_utility.get_time);
log_n('Exception in clean_bsc_aw_dim_delete '||sqlerrm);
bsc_aw_dbms_aw.execute('mnt '||l_olap_object(i).olap_object||' delete all');
to know which values have got deleted
this creates the program on the fly, executes it and drops the program
NO COMMIT!!!
remember: multiple dim loads can be happening. so we cannot have just one program for all
dim levels. the same level cannot be loaded in 2 sessions
*/
procedure dmp_dim_level_into_table(p_dim_level_list dbms_sql.varchar2_table) is
Begin
for i in 1..p_dim_level_list.count loop
dmp_dim_level_into_table(upper(p_dim_level_list(i)));
bsc_aw_dbms_aw.execute_ne('delete '||l_name);
bsc_aw_dbms_aw.execute('delete '||l_name);
l_kpi_limit_cubes.delete;
l_kpi_aggregate_markers.delete;
procedure lock_dim_objects(p_dim varchar2,p_dim_delete boolean) is
--
l_lock_objects dbms_sql.varchar2_table;
if p_dim_delete then --full rw lock
bsc_aw_management.get_workspace_lock('rw',null);
we have to acquire locks and update in a certain order . else we get
ORA-37023: (XSMLTUPD01) Object workspace object cannot be updated without dimension workspace object.
we cannot update a relation before a dim. so when we get locks, we first get dim, then relations, then variables
*/
procedure get_dim_objects_to_lock(p_dim varchar2,p_lock_objects out nocopy dbms_sql.varchar2_table) is
--
l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
l_bsc_olap_object.delete;
bsc_aw_utility.execute_stmt('delete bsc_aw_temp_pc');
bsc_aw_utility.execute_stmt('delete bsc_aw_temp_vn');
l_stmt:='insert into bsc_aw_temp_vn(name,id) select '||p_child_col||',count(*) from '||p_denorm_source||' group by '||p_child_col;
l_stmt:='insert into bsc_aw_temp_pc(parent,child,id) select '||p_parent_col||','||p_child_col||',rank() over(partition by '||
p_child_col||' order by '||p_parent_col||') from (select denorm.'||p_parent_col||',denorm.'||p_child_col||' from '||
p_denorm_source||' denorm,bsc_aw_temp_vn t1,bsc_aw_temp_vn t2 where denorm.'||p_parent_col||'=t1.name(+) and '||
'denorm.'||p_child_col||'=t2.name and t2.id=nvl(t1.id,0)+1)';
bsc_aw_md_api.update_olap_object(p_dim,'dimension',p_dim,'dimension',null,null,'operation_flag','loaded');
bsc_aw_md_api.update_olap_object(l_oo(i).object,'dimension',l_oo(i).object,'dimension',null,null,'operation_flag','loaded');
/* 5064802 cannot recreate dim programs. do this: load all dim once again and also load the values from bsc_aw_dim_delete table
from now on, hopefully, there will not be attempt to delete values that do not exist in dimensions. newly created dim will have robust strategy
to handle cases where dim delete values are not yet in aw dim*/
procedure upgrade_load_sync_all_dim is
l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
l_dim_level_delete dim_level_delete_tv;
l_delete_flag boolean;
/*add delete values into the dim levels */
bsc_aw_md_api.get_bsc_olap_object(p_dim,'dimension',p_dim,'dimension',l_oo);
load_dim_delete(p_dim,l_oo(1).property1,l_dim_level_delete,l_delete_flag);
if l_delete_flag then
merge_delete_values_to_levels(l_dim_level_delete);
l_oo.delete;
/*5074869 in the load programs, the delete values are handled only when the dim levels have parents. if this is the top level and
bsc_aw_dim_delete has values not in the dim, we can run into the issue of value not valid error
best way is to merge these delete values into the dim levels anyway */
procedure merge_delete_values_to_levels(p_dim_level_delete dim_level_delete_tv) is
l_levels dbms_sql.varchar2_table;
l_levels:=p_dim_level_delete('ALL').delete_values;
for j in 1..p_dim_level_delete(l_levels(i)).delete_values.count loop
bsc_aw_dbms_aw.execute('mnt '||l_levels(i)||' merge '''||p_dim_level_delete(l_levels(i)).delete_values(j)||'''');
log_n('Exception in merge_delete_values_to_levels '||sqlerrm);