Inventory Aging Bucket Range changes

Currently in the OOTB OBIA 11.1.1.x version Inventory buckets defined with 10 days interval range.
In this article we will see how to change the bucket interval(lets make 30 days interval range).

In BIACM , we have provided below two domain mappings to map Inventory aging bucket interval ranges for respective bucket names under Supply Chain and Order Management Analytics offering.

W_EXPIRING_TIME_BUCKET

W_ONHAND_AGING_TIME_BUCKET

In order to make bucket range changes ,we have to add new domain members.

Now, Select W_EXPIRING_TIME_BUCKET and click edit.

Here we can notice source and target domain members where source domain members represents start and end ranges in terms days inline with target domain members(bucket names).

Our task is to change these buckets interval range 10 days to 30 days interval range.

Here source domain members are all editable ,so we can simply enter new values as per our requirements.

Change bucket source range values from 

To (adding interval 30 days)

Now add new bucket ranges in Target Domain Members as shown below.

Click on + symbol to add new domain member for new bucket

 Add the below details with the new Bucket range and click OK

In the similar way, we need to add ranges for 31-60, 61-90, 91-120 and >120.

Once the above changes are done, from the list of LOV’s we can add the respective target domain values and assign them as below.

Under Target Domains, Replace 1-10 Bucket Range by newly created 1-30 Bucket Range.

In similar fashion ,replace remaining bucket ranges as well with new bucket ranges.

Bucket 11-20 replace by new Bucket 31-60
Bucket 21-30 replace by new Bucket 61-90
Bucket 31-40 replace by new Bucket 91-120
Bucket 41-9999999 replace by new Bucket 121-9999999

Once we done all the above changes, then domain mapping looks like this.

Click Save and Close.

Repeat same for W_ONHAND_AGING_TIME_BUCKET domain mapping.

But here we have target domain members which are overlapping.
Example: Bucket 1-30 and 1-10 range in biacm will cause issue in identifying proper bucket name if you have range days between 1-10.i.e when you try to identify bucket for inventory which has age of 9 days in inventory. It will give two buckets 1-10 and 1-30 since 9 falls under those two buckets.

Hence we need to remove these old buckets (10 days interval) which are causing issues. But there is no way to delete them from BIACM front-end.

Here is the way to handle them from backend schema xx_BIACOMP.

c_domain_member_map_num_v (c_domain_member_map is main table in this view) is the source table used for loading range domain values into W_DOMAIN_MEMBER_MAP_NUM_G warehouse table.
Hence we need to delete records from c_domain_member_map for these 2 domains.

Execute below delete statement to remove old domain members. 

--Create backup table

create table c_domain_member_map_bkp as select * from c_domain_member_map;

--Delete old bucket ranges 

delete from c_domain_member_map where TRG_DOMAIN_MEMBER_CODE IN ('1-10','11-20','21-30','31-40','41-99999999')

and TRG_domain_key in (select domain_key from C_DOMAIN where DOMAIN_CODE in ('W_ONHAND_AGING_TIME_BUCKET','W_EXPIRING_TIME_BUCKET'));

commit;

Set committed=0 in W_ETL_LOAD_DATES table for W_DOMAIN_MEMBER_MAP_NUM_G table scenario to make this go for full load to pick up new records in next incremental load.

--Create backup tables

create table W_DOMAIN_MEMBER_MAP_NUM_G_BKP as select * from W_DOMAIN_MEMBER_MAP_NUM_G;

create table W_DOMAIN_MEMBER_G_BKP as select * from W_DOMAIN_MEMBER_G;

create table W_DOMAIN_MEMBER_LKP_TL_BKP as select * from W_DOMAIN_MEMBER_LKP_TL;

--Set committed flag to 0 to make it full load

update w_etl_load_dates set committed=0 
where package_name='SILOS_SIL_DOMAINGENERAL_DOMAINNUMMAPS';

commit;

Note: W_DOMAIN_MEMBER_G and W_DOMAIN_MEMBER_LKP_TL records will be updated with newly entered domain values (i.e 1-30,31-60,61-90,91-120 and 121-9999999) in the incremental run based on last_update_dt of those values if we run immediately after the changes. If we planned to run load after some days then we need to set enough PRUNE_DAYS to get those records.

Start incremental load. And check for data in the table for new bucket ranges.

select * from W_DOMAIN_MEMBER_MAP_NUM_G 
where TRG_DOMAIN_CODE in ('W_EXPIRING_TIME_BUCKET',’W_ONHAND_AGING_TIME_BUCKET’);

Make sure there are no overlap ranges under the same domain.

After the load we can notice new buckets in obiee report.