BIA Index rollup

former_member184494 · ‎08-20-2008

The follow up to my previous blog on BIA Index build - I decides to look into the index rollup

*Index rollup : After the BIA index is built - the subsequent data loads will have to be rolled up into the BIA for reporting. If this is not done then the data will not be available for reporting. This process is the same as rolling up a request into an Aggregate. Here there is no compress after rollup option - the data is compressed by default and the BIA does something called Vertical compression / column compression instead of the standard row level compression that we are all familiar about. Caution : Even if you switch off the BIA Indices for reporting - the rollup is required.*Get the list of requests that are to be rolled up. Here there is a BIA Index but no Aggregate.

RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_INSTANCE_FOR_RNR 633666 LINE 43
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_TSTATE_FOR_RNR 2 LINE 243
Status transition 2 / 2 to 7 / 7 completed successfully
RSS2_DTP_RNR_SUBSEQ_PROC_SET SET_TSTATE_FURTHER_START_OK LINE 261

Set the status of reporting on the cubes before rolling up the BIA Index. <CUBE> ACT <CUBE> ACT <CUBE> ACT Statistics UID of indexing job: '4AVGUJ241MF68T94VA6S7GTU5' (RSDDSTATTREX/RSDDSTATTREXSERV) Generate statistics for the cube - this is similar to Indexing - have found the same job getting triggered when INdex creation happens.+Start indexing the S Tables first... <CUBE> ACT <CUBE> ACT Loading data to index BIW_BIC:SYBSPACK (records '0000000002', job '0' ) YF2IG_C07 ACT Caution: Key figure '<XXXXX>' is of type "FLOAT" Caution: Key figure '<XXXXX>' is of type "FLOAT" Caution: Key figure '<XXXXX>' is of type "FLOAT" Caution: Key figure '<XXXXX>' is of type "FLOAT" Caution: Key figure '<XXXXX>' is of type "FLOAT" Caution: Key figure '<XXXXX>' is of type "FLOAT" Caution: Key figure '<XXXXX>' is of type "FLOAT" Caution: Key figure '<XXXXX>' is of type "FLOAT" Caution: Key figure '<XXXXX>' is of type "FLOAT" <CUBE1> ACT Here it also checks where else these characteristics / index on S table is used in BIA and indicates if those cubes are active or not. Loading data to index BIW_BIC:XYBSPACK (records '0000000002', job '0' ) Total number of indexed reocrds in index 'BIW_BIC:SYBSPACK' after COMMIT: '2' Loading data to index BIW_BIC:SYMATERL (records '0000000007', job '0' ) <CUBE> ACT <CUBE> ACT Loading data to index BIW_BIC:<CHARACTERISTIC> (records '0000000007', job '0' ) Total number of indexed reocrds ( Looks like a type in SAP.. )in index 'BIW_BIC:<CHARACTERISTIC>' after COMMIT: '7' Loading data to index BIW_BIC:<CHARACTERISTIC> (records '0000000002', job '0' ) Total number of indexed reocrds in index 'BIW_BIC:<CHARACTERISTIC>' after COMMIT: '2' Loading data to index BIW_BIC:F<CUBE> (records '0000043030', job '0' ) Total number of indexed reocrds in index 'BIW_BIC:F<CUBE>' after COMMIT: '43,030' Loading data to index BIW_BIC:D<CUBE>P (records '0000000001', job '0' ) Roll up BIA index Statistics UID of indexing job: '<JOB ID>' (RSDDSTATTREX/RSDDSTATTREXSERV) Index for table '<CHARACTERISTIC>' is being processed Index of master data table(s) for InfoObject '<CHARACTERISTIC>' Here all the characteristics indicated as nav attributes of the characteristic liested above are indexed. This includes Nav Attributes and also other characteristics whch are part of the master and also part of the data load. Index for table '<NAV ATTR>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index of master data table(s) for InfoObject '<CHARACTERISTIC>' Index for table '<NAV ATTR>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index of master data table(s) for InfoObject '<NAV ATTR>' Index for table '<NAV ATTR>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Check which index to load - Main Index or Delta Index...this is for the SID Tables. Delta mode for index '<NAV ATTR>' set to 'Off' (duration: '0.035387') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') Index of master data table(s) for InfoObject 'YSKU7' Index for table '<NAV ATTR>' is being processed Delta mode for index '<NAV ATTR>' set to 'Off' (duration: '0.052726') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') The restriction by Join comes because a Join Index is being created between the S table and X tableThis is because of the Nav Attribute being indexed..more information on Join Indexes No new data for index of table '<NAV ATTR>' *Other characteristics that are not part of any master table are being indexed.* Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC( Line ITEM)>' is being processed Delta mode for index 'SYBILLDOCU' set to 'Off' (duration: '2.243841') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') Index 'BIW_BIC:<CHARACTERISTIC>' for BIA index filled (written records '25'): Prepare optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Commit optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<MASTER CHARACTERISTIC>' is being processed Delta mode for index '<CHARACTERISTIC>' set to 'Off' (duration: '0.046333') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') Index of master data table(s) for InfoObject 'YCUSTMER' Index for table '<CHARACTERISTIC>' is being processed Delta mode for index '<CHARACTERISTIC>' set to 'Off' (duration: '0.011561') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') No new data for index of table '<CHARACTERISTIC>' Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Delta mode for index '<CHARACTERISTIC>' set to 'Off' (duration: '0.050789') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') Index 'BIW_BIC:<CHARACTERISTIC>' for BIA index filled (written records '2'): Prepare optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Index of master data table(s) for InfoObject '<CHARACTERISTIC>' Index for table '<CHARACTERISTIC>' is being processed Delta mode for index '<CHARACTERISTIC>' set to 'Off' (duration: '0.038126') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') Index 'BIW_BIC:<CHARACTERISTIC>' for BIA index filled (written records '2'): Prepare optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Commit optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Commit optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Delta mode for index '<CHARACTERISTIC>' set to 'Off' (duration: '0.048498') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') Index 'BIW_BIC:<CHARACTERISTIC>' for BIA index filled (written records '7'): Prepare optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Index of master data table(s) for InfoObject '<CHARACTERISTIC>' Index for table '<CHARACTERISTIC>' is being processed Delta mode for index '<CHARACTERISTIC>' set to 'Off' (duration: '0.009456') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') Index 'BIW_BIC:<CHARACTERISTIC>' for BIA index filled (written records '7'): Prepare optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Commit optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Commit optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Commit optimize:+

The previously optimized data is made visible. If you perform rollback for an index, the system rolls back the data to the last commit optimize. [Source | http://help.sap.com/saphelp_nw2004s/helpdata/en/43/5391420f87a970e10000000a155106/content.htm] : Basically since this is a master data index - guess the rollback of master data is being built in for if you roll back a request in the master data - the commit optimize takes over here. Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Index for table '<CHARACTERISTIC>' is being processed Delta mode for index '<CHARACTERISTIC>' set to 'Off' (duration: '0.055343') Read-/fill mode: 'D' (Restriction by JOIN with 'RSDDTREXNEWSID') Index 'BIW_BIC:<CHARACTERISTIC>' for BIA index filled (written records '2'): Prepare optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Commit optimize for BIA subindex 'BIW_BIC:<CHARACTERISTIC>': Index for table '<CHARACTERISTIC>' is being processed Now the fact table is being indexed : Index for table 'F<CUBE>' is being processed Delta mode for index 'F<CUBE>' set to 'Off' (duration: '2.155832') Read from 'F-' fact table Index 'BIW_BIC:F<CUBE>' for BIA index filled (written records '43030'): Prepare optimize for BIA subindex 'BIW_BIC:F<CUBE>': Commit optimize for BIA subindex 'BIW_BIC:F<CUBE>': 43030 records only and gets finished quite quickly - took about 1-2 minutes or so Had to remove the times for easier presentation Now the dimension tables get indexed.*Request Index : Index 'BIW_BIC:D<CUBE>P' for BIA index filled (written records '1'): Prepare optimize for BIA subindex 'BIW_BIC:D<CUBE>P': Commit optimize for BIA subindex 'BIW_BIC:D<CUBE>P': Index for table 'D<CUBE>1' is being processed Index for table 'D<CUBE>3' is being processed Index for table 'D<CUBE>2' is being processed Index for table 'D<CUBE>6' is being processed Index for table 'D<CUBE>4' is being processed BIA index for InfoCube '<CUBE>' rolled up successfully Now this is also request based - based on the number or requests that are to be rolled up the set of activities above will repeat...*Now a check is made to check if there are any aggregates on the cube that have to be rolled up. In this case there are'nt any... No rollup necessary Rollup is finished: Data target <CUBE>, from 633,526 to 633,666 Job finished

Net net the BIA index rollup is fine .. only issues observed so far are :

1. Rollup after addition of Nav Attributes to the cube - sometimes warnings are shown...

2. Deletion of data - it deletes from the BIA index also - but then sometimes gets locked and in most cases requires a full Index rebuild. - here you can see a check box for Delete from BIA Indices in the selective deletion window - have'nt tried it out yet..

Will see if I can put together something on Maintenance activities for BIA after this... and have some presentations on BIA which should come in handy to publish some more material.

Arun Varadarajan

BIA Index rollup

Are you there, SAP? It's me, Jelena

Integration Point of MM-FI-SD in SAP ERP

SAP Project System - A ready Reference ( Part 1 )