Technical Articles
ISU – Sales Statistics Extractor (0UC_SALES_STATS_02)
Introduction
Few years back when I started with my SAP IS-U/BW support project, I had to go through the process of learning on how the extraction takes place from SAP source system for special extractors such as Sales Statistics and how the delta queue is updated.
I had to go through many posts and reach my seniors on how to perform the data extraction.I have tried to share the points gathered and lessons learnt from my experience below.
IS-U Sales Statistics (Delta created by Mass Activity) – 0UC_SALES_STATS_02:
Sales Statistics is the standard industry specific solution for an Utility company.
- It helps in evaluating consumption and revenue.
- Statistics is generated for all invoiced billing documents with consumption and revenue data.
- The posting or consumption period serves as a time basis.
- It acts as a basis for optimizing rate structure and pricing.
- It provides standardized and process related analytical information and acts as a reliable source.
- 0UC_SALES_STATS_02 data source is available from the ISU release 4.62
- BIW_ISU_ESTA is the extract structure.
- ISU_BW_SALES_STATISTICS is the function module
- Delta mechanism – Only New Records via Delta Queue (NEWD)
- BWESTA01 is the standard exit for 0UC_SALES_STATISTICS_02
- Function Group: XE71ESTA01
- Function Module: EXIT_SAPLE71ESTA01_001.
Steps to extract Sales Statistics data to BW:
-
- Trigger Full load in BW to establish connection between ECC and BW. The no.of records loaded is 0.
- Trigger Init-without data transfer in BW, this is to initialize the delta queue in RSA7 (ECC).
- RSA7 – Delta Queue is initialized.
- Trigger the mass run EBW_DQ_SS with the Date ID and Identification and pass the Posting Date as a selection (huge selection range). Prior to mass run (EBW_DQ_SS) “Reconciliation Closure” Job needs to be executed and completed.
- Set the Technical settings (Variant and No.of Jobs), Save the parameters and Schedule Program Run.
- Trigger Full load in BW to establish connection between ECC and BW. The no.of records loaded is 0.
-
- On completion of mass run in ECC, the delta queue in RSA7 gets filled.
RSA7 – Delta queue for 0UC_SALES_STATS_02 is filled.
- Trigger the Delta load in BW to extract the records.
- On completion of mass run in ECC, the delta queue in RSA7 gets filled.
- Load data from PSA to further data targets in BW
Additional Information:
- Mass activity pushes data to delta queue only if there are any closed Reconciliation Keys for the day.
- Creation of new Invoice/Print Document leads to an entry in Invoicing tables such as ERDK and other tables and in DBESTA_BWTRIG table. Delta since last request is existing in this table.
- EBW_DQ_SS mass run looks into DBESTA_BWTRIG entries and if Reconciliation Key for the relevant Print Doc is closed, then the same documents are pushed to delta queue. Tcode – FPF3 shows the status of Reconciliation Key. “Postings can no longer be made” means that the Reconkey is closed.
- To close the Reconciliation Key use Tcode – FPF2.
- Once data is available in delta queue, it can be extracted to BW.
- Once the Print Doc is pushed to Delta queue the entry in DBESTA_BWTRIG is cleared.
- DBESTA_BWPROTH is the history table which records all the extraction history.
Lessons Learnt:
- What happens if a Full load is triggered when the Delta is already set.
- The full load would not result in any records, instead an entry in DBESTA_BWTRIG is created. “Created by Request” field in DBESTA_BWTRIG is filled with Full IP Request ID.
- While the next Delta is triggered as per schedule then the above full load selection is also extracted along with delta records (New records).
- If the selection in Full load was already extracted before then the delta load would result in duplicates.
- It is better we do not disturb the delta load by triggering Full load in mid of the schedule.
- The full load would not result in any records, instead an entry in DBESTA_BWTRIG is created. “Created by Request” field in DBESTA_BWTRIG is filled with Full IP Request ID.
- There is no chance of missing documents because until the relevant Reconciliation Key is closed in ECC, it is not extracted to BW and it is staged in DBESTA_BWTRIG. On Reconkey closure it would be available for extraction.
- Tcode –
- FPF2 – Close Reconcilation Key
- FPG1 Transfer of FI-CA Totals Records to General Ledger
- Closing Reconciliation Keys –
- Each reconciliation key for which the values are transferred to the general ledger must be closed first.
This ensures that no other postings are carried out for this reconciliation key and that the recorded values cannot be changed.
- Each reconciliation key for which the values are transferred to the general ledger must be closed first.
Conclusion
This post will be helpful for BW consultants who are new to SAP ISU and would have to perform Sales Statistics Extraction.
Thank you Mamatha, for sharing steps for sales statistics extraction. This is very helpful.
Thank you Pruthvi.
Hi Mamatha
I did all the steps you mentioned above but when I execute InfoPackage it show no data available as shown in below screen shot:
However when I check in the RSA3 it shows 2.3million records. and RSA7 also shows filled delta queue.
There is no entry in SM58 and no stuck IDoc.
You helpful reply is highly appreciated.
Thanks
ISKhan
Hi Imran,
The screenshot attached by you shows its a Full request.
Could you please let me know if you triggered Full Infopackage first and then Init without data Infopackage and later executed the mass activity EBW_DQ_SS.
Kindly note on completion of mass activity Delta queue would be populated, now execute the Delta infopackage to pull the data from Delta queue to BW.
Hi Mamatha
Thanks for your reply. Actually it's a Full Repair Load for selected data. As you know if I init again the current delta is disturbed.
So, after successful execution of EBW_DQ_SS of selected data and closure the recon. key I executed the full repair infopackage but No data available message shows as I posted the screen shot above.
Now what you suggest?
Good info Mamatha, it’s really helpful. And if possible, please share the debugging steps for this extractor in ECC.
Good Day,
That you for sharing your knowledge, this is great!
Question: How do i cater for documents with alternative posting dates as it affects reconciliation in fpt3 and fpt7.
Kindly advise.
Thank you,
KD
after successfully loaded data , how to get only delta records, everytime we need to run EBW_DQ_SS, if that is the case what will be the selections, anybody can help.
Regards,
Sreenivas
Hi Nelus,
To fetch the delta records you will always need to run the mass activity - EBW_DS_SS.
Please note when you are executing the mass activity, you will have to enter the Date ID and Identification and General Selection would be on Posting Date.
Example : Posting Date = 01/01/1000 to 12/31/9999 or from the day you went live with SAP could be your start and end would be 12/31/9999.
Once the mass activity is completed, trigger the delta infopackage from BW side.
Could you please tell me how to perform re-int for this data source, I don't want to bring history records.
Hi Mamatha,
we have few problems with this extractor at the moment. we are missing few reversed document from 18/07/2022 that were posted in source system after 18:00HR to 21:00HRS. We are finding it hard to extract (missing 24 installation) from source system to BW system. Could you please share some light on this ?
Thanks,
Ashwin
Hi Mamatha,
Can you explain a bit on variant maintenance.
We are unable to maintain a range for our recon keys.
Regards,
Siddharth M