Datasource: A Datasource is not only a structure in which source system fields are logically grouped together, but also an object that contains ETTL related information.
There are two types of Datasources:
- Application Specific: These are SAP given datasources, they further divided into
- BI content
- Customer generated Datasources
- Cross-Application: User-created Datasources
- Generic Datasources
Business Content: It is a complete set of BW objects developed by SAP to support OLAP tasks. It contains pre-defined roles, workbooks, queries, infocubes, key figures, characteristics, update rules, infosources, transformations, DTP’s etc.
SAP delivers Business content in ‘D’ version, then in order to use it, we need to activate it, then the version changes to ‘A’ version. Changes are saved in ‘M’ version.
SAP delivers BC in new ‘D’ version
Changes are saved in the ‘M’ version
You need to activate BC before working with them
Installing Business Content:
We install the BC :
- After the content release upgrade
- After installing a content support package
- Goto RSA1 –> in the modelling select BI content tab.
In the BI content tab, you can see that the window is divided into three parts.
- In the left hand side of the window, you determine how the objects are viewed in the middle window.
- In the middle of the window, you select the objects that you want to activate.
- In the right-hand side of the window, you will simulate and install the business content.
- 1.Only necessary objects: if you select this option, then only necessary objects will be installed.
- In data flow before: If you select this option, system will automatically add, the objects that lie below the object you selected, according to the dataflow.
- In data flow afterwards: If you select this option, the system will automatically add, the objects that lie after the object you selected, according to the dataflow.
- In data flow before and afterwards: If you select this option, for the given object, the system will add objects that are before and afterwards in the dataflow.
- e.g. If you select the cube 0ic_c03, system will add all the objects below the cube like Datasources, transformations, dtps etc, and objects that comes afterwards like queries, workbooks etc.
- Collect automatically(default setting): Objects are collected automatically
- Start manual collection: objects are collected, when you click on gather dependent objects
Check the objects after collection to install.
While installing, check the install boxes available for objects.
Install all below: the objects and the dependent objects are checked for the installation.
Donot install all below: The checkboxes are removed when you select this option.
Match X or copy:
When the object is available in the active version, you have to decide whether to keep the active version or to install the latest SAP delivered version of the object.
when you check the match, then the customer version of the object is merged with the new SAP version and a new customer version is created. If you do not check the match, SAP delivered version of the object is overwritten.
There are four options in Install:
- Simulate installation
- Install in background
- Install and transport
- Simulate installation: The system runs a test to check whether there are any errors.
- Install: installation happens in the foreground.
- Install in Background: Installation happens in background, you can monitor the request in SM37.
- Install and transport: All the objects are installed and then written to the TR automatically.
Finance is a back-bone of any organisation, and for that reason, SAP provides FI module which records, collects or processes the financial transactions or information on a real-time basis to fulfill the reporting requirements. It is tightly integrated with other modules like MM, SD, PP, HR, and CO.
The sub-modules of FI are:
- FI-AA(Asset Accounting) : It deals with financial side, like depreciation, insurance etc. Starting with Procurement of assets and ending with scrapping or sales.
- FI-AP(Accounts Payable) : It deals with vendor transactions with payment program for making payments to the vendors.
- FI-AR(Accounts Receivable) : It deals with customers and receivables, with credit management functionalities and ‘dunning’ program.
- FI-GL(General ledger) : It gets simultaneous postings from FI-AA, FI-AR, FI-AP. All accounting postings are recorded here, to provide up to date visibility of the financial accounts.
- FI-SL(Special Purpose ledger) : It provides summary information from multiple applications at a level of detail that the user defines.
- FI-LC(Legal consolidations) : It deals with financial operating results of the companies within a group to provide overall results for the group.
- FI-BL(Bank accounting) :
- FI-FM(Funds management) :
- FI-TM(Travel management) :
We can extract the financial data at totals level/ line-items level. In general, we will use R/3 line item tables as the datasource for extracting the data to allow drill down capability from summarized data to line item data.
Example of Installation:
We are going to install the cube 0FIAA_C01 in the Business content.
1. In order to check the dataflow for the cube 0FIAA_C01, you have to goto Metadata repository, there click the Business content. Now select the Infocube and search for the Infocube 0FIAA_C01.
2. Double-click on the Infocube. Then double-click on the option ‘Network display of dataflow’, here you can see whole dataflow for this infocube.
3. Go to RSA5 in ECC side, and Activate the three datasources shown above in the figure. Once activated, those datasources must be available in RSA6.
4. Now, Go to Business Content tab, Select the cube 0FIAA_C01, group objects according to your requirement, then install it.
5. After BC installation is completed, replicate the datasources. Once you done that, now go to the Infocube 0FIAA_C01, select it, and right-click. Now select the ‘show data flow’ option. The below is the dataflow for the cube.
6. Now, as that the dataflow is completely installed, create Infopackages for the datasources, and load the data.
7. Once the data is loaded successfully, you can click on manage of infocube, and monitor the requests. Similarly, you can go to LISTCUBE and check the data in the cube.
LO extraction gives better performance with reduced volume of data. It provides us a single solution for all logistic applications. LO extraction is a detailed extraction, updated with batch process (V3 update). Only data that are changed from the BW perspective are added to the delta queue.
LO data extraction: customizing cockpit:
The document flow for LO data extraction is shown below:
- Whenever document is created, it is stored in the respective database table via communication structure.
- The structure of the datasource is called extract structure.
- The data from DB table is passed on to the datasource through Extract structure.
Datasource naming convention:
The SAP standard datasources has a specific naming convention provided by SAP.
- e.g: 2LIS_11_VAHDR
In this datasource,
2 is a symbol for logistics, all datasources belong to logistics, start with 2.
LIS stands for LO information structures.
11 is a application number.
VA further specifies that the datasource is a sales order.
HDR means header data, it specifies the type of data.
HDR – Header data
ITM – Item data
SCL – Schedule line
Different types of Updates are available:
- V1 update: It is called as synchronous update. Whenever document is created (or) posted, it is going in DB table and extraction tables/update tables at the same time.
- V2 update: It is called as asynchronous update. Whenever document is posted, the data is stored in the DB tables first then in the update tables/extraction tables.
- V3 update: It is Asynchronous update with Background. When the document is posted, It will be posted directly to BW delta queue via update tables. You can schedule a background job for v3 update.
LBWQ is a trsn for QRFC monitor
RSA 7 is a trsn for BW Delta Queue
SM13 or SMQ1 is a trsn to see extraction tables or update tables.
There are three types of Delta methods in LO data extraction.
- Direct Delta: Data is transferred to BW delta queue directly for each document posting.
- Queued Delta: Data is transferred to QRFC monitor, then to the BW delta Queue. Good for higher volumes of data, and frequent updates.
- Non-serialized V3 update – The data is directly posted to the BW delta Queue, but the sequence is not guaranteed.
Controlling and Profitability Analysis (CO-PA) is mainly used for Sales and contribution margin reports. It helps you determine how profitable your market segments are, with CO-PA, you will be able to define which segments, like customer, product, geography, sales organization, of the market are required for analyzing the operating results/profits. CO-PA mainly focuses on external market segments profitability.
There are two types of reports in CO-PA
- Costing-based : It is used to valuate sales orders, billing documents to determine the deductions and costs.
- Account-based : By using cost and revenue elements, It provides you with a profitability report, which is reconciled with FI.
The transaction code for creating CO-PA datasource is KEB0.
Here, we will create a CO-PA datasource as shown in the figure above. We are going to create Costing based datasource.
For that, you need to first go to transaction code KEB0. Then give the datasource name, it should start with 1_, here our DS name is 1_CO_PA_Profit.
Give the operating concern, as shown in figure, and execute.
Now, give the descriptions and field name for partitioning as shown above. Click on Infocatalog symbol above. You will be asked for the developer key, fill the key and continue.
Now, you need to select the selection fields. Once you select it, these fields will be available as selection parameters in RSA3, and InfoPak.
- Selection: If you select the selection checkbox, the field will be used for selection.
- Hide field: If you select the Hide field, the field is hidden in the BI system.
- Inversion: It is used for data inversion.
- Field only: When you enhance the datasource, this option is available for the added ‘Zfields’. When you select this option for the field, that field cannot be used for selection.
Now, Save the datasource. The CO-PA datasource is created in ECC side. You can go to RSA6 and check this datasource.
In RSA3, you can check the number of records this datasource contains, as shown in the figure below.
As you can see from the figure, this datasource contains 10,264 records. You can extract these records by creating dataflow in BW.
Steps in BW:
- Replicate the datasource, right-click the CO-PA application component, and replicate.
- Take the field names and search for the appropriate infoobjects in RSOSFIELDMAP(Database table).
- Create DSO, create transformation and DTP, target as DSO.
- Create Cube, Create transformation and DTP, target as Cube.
- Load the data first to the DSO, then to the cube.
This document may contain the definitions, and the architecture of the book:
Data warehousing with SAP BW7. By christine mehrwald, sabine morlock.
Please visit my website for my blogs on SAP BI.