Costing Based COPA extraction in BW
This document gives a brief introduction of SAP CO-PA module & underline database tables. It covers how to extract CO-PA data in BW, Creation of datasource, enhancement of CO-PA datasource, BW modeling for CO-PA extraction, realignment of CO-PA Data and its impact on BW delta loads.
Introduction to CO-PA:
- Controlling Profitability Analysis evaluates the profitability of an organization by strategic business units.
- Apart of daily transactions, it captures the details of various discounts, expenses, promotions and thus gives the organization actual profit details. It helps to analyze the market trends & make better decisions.
- Organizations can also submit their different forecasts or targets for various organizational units for various periods in CO-PA.
- This helps the organization to compare the plan & actual sales done so that the performance of different business units can be analyzed in better way and they can take the necessary steps for corrections.
- An organization identifies its operating concern which constitutes of a combination of Characteristics (Customer, Product, Company Code, Controlling Area etc) & Value fields (Sales Qty, Gross Sales, Discounts, Expenses, Net Sales, Trade Promotions, Coupons etc) to analyze its profitability.
- Characteristic combination gives a level of analysis, i.e. against which fields the sales information is to be analyzed.
Types of Profitability Analysis:
- Costing-based Profitability – is the form of profitability analysis that groups costs and revenues according to value fields and costing-based valuation approaches.
- Account-based Profitability – is a form of profitability analysis organized in accounts and using an account-based valuation approach
The Costing Based CO-PA is mostly used and it has been considered here. * * !https://weblogs.sdn.sap.com/weblogs/images/252181068/Photo1.png|height=269|alt=image|width=623|src=https://weblogs.sdn.sap.com/weblogs/images/252181068/Photo1.png|border=0!
- All fields other than Profitability Segment Number added in the operating concern are available for datasource. Profitability Segment Number is not available automatically while creating datasource as it is being used in CO-PA for internal purpose.
- In the case of costing-based profitability analysis, you can include Characteristics from the segment level, characteristics from the segment table, characteristics from the line item, value fields, unit fields and calculated key figures.
- Custom Characteristics name should be started with WW
and value fields name should be started with VV , where XXX you own description.
You can then specify which fields are to be applied as the selection for the CO-PA extraction.
- InfoObjects are only created automatically for value fields and user-defined characteristics if the datasource is mapped to an InfoSource using the source system view in the SAP BW Administrator Workbench.
- In this case, a dialog box appears in which you can choose Application Proposal. With this function, any missing InfoObjects are created for value fields (key Figures), measure unit fields, and the user-defined characteristics when the InfoSource is generated.
- For each CO-PA-specific field with a name consisting of five characters, the system generates a corresponding InfoObject in the background. The name of the InfoObject generated consists of the field name (five characters) with the prefix 0G_x, whereby x=C for characteristic, x=A for amount, x=Q for quantity, and x=U for unit of measure. In this way, InfoObject 0G_CWW001 is generated for characteristic WW001.
- As in BI 7.0 version we are no longer using Infosource, we can first replicate the CO-PA datasource as 3.X datasource to create the infoobjects for custom characteristics and value fields automatically. Then we can delete that datasource from BW and replicate as 7.0 datasource. All Infoobjects will be remain there in the system.
- CO-PA datasource cannot be enhanced. If we have to add/delete fields from datasource Operating Concern has to be modified with the addition/deletion of fields. Now that new field cannot be adjusted with the existing datasource. Datasource has to be deleted and recreated.
- If you created the datasource with the same as old datasource name, no need to create transformations in BW newly. We have to replicate the new datasource and transformations/DTPs have to be re-activated.
CO-PA Delta Mechanism
- Generic Delta in CO-PA based on Time Stamp. While creating CO-PA datasource it will be generated automatically.
- When data is extracted from CO-PA, a “safety delta” of half an hour is used for the initialization and the delta upload. This always ensures that only records that are already half an hour old since the start of the upload are loaded into SAP BW. Half an hour was chosen as the safety delta to overcome any time differences between the clocks on the different application servers.
- Transaction KEB2 shows all the detail information about CO-PA datsource, what is the timestamp set for next delta, what is the time stamp for repeat,what is the last request updated etc.
- CO-PA extraction doesn’t use the delta queue. CO-PA delta is based on timestamp, which is updated after each delta extraction. CO-PA datasource reads delta from the CO-PA tables directly.
- If delta fails for realignment run in ECC, then we have to reinitialize the whole CO-PA data to get the changed data in BW and without this delta load is not possible.
- We can change the timestamp of delta using KEB5 transaction if require and wrong use of this can lead to inconsistency which cannot be resolved. We can use this function to simulate the initialization of the delta procedure for a DataSource and to set the time stamp of the DataSource to a desired value. We can set the delta replication from a different DataSource. This is sometimes necessary for example, after an upgrade from R/3-Release 3.x to 4.5
For failed delta in case of CO-PA since there is no delta queue and therefore no repeat delta queue as well. But since the SAP system is inbuilt to handle these issues in a way that timestamp will not be updated until the extraction jobs complete successfully in R/3 system and that’s why the next scheduled delta will bring the correct records.
Data management in CO-PA *
- It is generally advisable to directly load CO-PA data in BW to Cube due to high volume of data. Delta process for CO-PA datasource is CUBE. CO-PA Extractors deliver a real delta, which means they give you the changes in your key figures. So key figure should be added in BW dataprovider.
- Logical partitioning should be done on Cube based on Fiscal Year/Period.
- To manage high volume of CO-PA data in BW historical data need to be archived. Based on reporting requirement, data till current year or previous year can be kept on the Cube and rest old data should be archived.
Realignment in CO-PA
You can change the characteristics posted earlier or input values for a newly introduced characteristic for the existing profitability segments using realignment.
However this can be done provided the derivation logic for the characteristic being changed is based on the characteristics already existing in the CE4XXXX table. Realignment only touches the CE4XXXX table (XXXX = operating concern).
Effect on BW:
- Whenever the CO-PA data is realigned, the entries in the profitability segment are updated.
- Such a mechanism to align all the transactional CO-PA data records against the updated Profitability
- When the CO-PA realignment happens in the source system delta load fails in BW with the error message as ‘Delta Update is no longer possible due to Realignment in the Source System’; no further records are updated in the delta queue.
- We have to delete all data from BW dataprovider and reinitialize the data in BW to get corrected records. After this we can again schedule the delta load.
* *CO-PA BW report validation with R/3
Let’s consider a Profit by State BW report restricted by Fiscal Year – 2012 and Fiscal Period 001.2012 (APR) as shown in below screen. And Query is giving output of value fields Pounds Sold and Gross Sales for each State of Sale.
- Now to validate this data with the source go to Transaction KE24 in R/3 source system.
- Enter your Operating Concern name on which CO-PA datasource has been created. And select Costing-based Radio Button.
- As BW query is restricted on Fiscal Year/Period use the same restrictions here in KE24 and click on Execute.
- You have two radio button options when accessing KE24 under mode of access. While “Read according to Current structure” shows the document with changes (changes after realignment run happens in ECC) whereas “Read as Posted” will show the document as originally posted.
- Select the necessary fields which need to be validated with BW report by clicking on Change layout button from menu and transfer the desired fields in left hand side Displayed Columns section.
- Now you will get the data in the screen and you can match it with BW report.