Skip to Content

SAP BW and Business Content Datasources: in pursuit of the origins

In the middle of the night, lying on the bed, I am woken up by feeble voices whispering “Can someone tell us how to locate an R/3 table-field mapped to an infoobject in my (std) transfer structure?”.

To be honest I have no haunted bedroom and I usually sleep the sleep of the just, but, speaking seriously, if I have to think to one of the most unanswered post in BI Forums, certainly my thought goes to the question “how can I find the R/3 source of a specific object in BW ?!”.
Sometimes some good Samaritan (as How to locate which R/3 table-field is mapped to BW) arms himself with patience and tries to give a good answer.
But often this is not so easy. And just think SAP invests a lot of efforts to give as many info as possible…but, as usual, a serious chef rarely discloses his secret ingredient !!!


As already discussed in one of my previous LOGISTIC COCKPIT – WHEN YOU NEED MORE – First option: enhance it ! Business Content (BC) has represented (one of) huge added value(s) for a customer (often already owner of a SAP product – usually R/3 – and inclined to buy a Business Intelligence solution), and it played (and still plays) a leading role in SAP Business Warehouse’s success.

Into SAP Glossary, BC is defined as “predefined role and task-related information models that can be suited to enterprise-specific requirements. BC makes the supply of information available to roles in an enterprise, that require this to complete tasks. BC consists of roles, workbooks, queries, InfoCubes, InfoObjects, InfoSources and update rules, as well as extractors, for SAP R/3, SAP New Dimension Applications and for additional selected applications.”.

So, on one hand BW’s comprehensive BC enables a quick and cost-effective implementation. On the other hand, it provides a model that can be used as a guideline during implementation using experience gained from other implementations.

Ok, but what about BC related documentation ? In order to have this quick and cost-effective implementation (due to a drastic reduction of the needed development phase), it’s pleonastic saying that I have to know (once identified my functional and technical requirements) WHAT I have to use (from BC) and HOW using it to satisfy my requirements.

About WHAT, SAP Help Portal is the kingdom of this kind of info.


About HOW SAP Service Marketplace is the best source in which news, product documentation, technology articles and many others useful things are featured.


Often not holden in due consideration, there are also the SAP Best Practices for Business Intelligence, that contain the documentation for the installation and configuration of solutions based on a lot of ‘functional’ scenarios.
Just put it to the test !


(Sure, if you want to know WHAT and HOW all in one go, you have to come to /community [original link is broken]…but, if you are here, maybe you already know that and agree with me !!!)

If you have correctly understood what you have to do and how, you arrived at a good point, but now some philosophical question can arise.
Indeed, especially if the number/value you see arriving in your BW seems not so familiar, you can wonder:…

(or “Desperately seeking something with which reconcile”)

Anyone working inside the BW world knows that very often implementing a flow can be difficult, but we all would rather build hundreds of ODS than see our boss rushing into our room and requiring BW data perfectly reconciled with R/3 (“…to the last penny, please !”).

Fortunately, SAP acknowledges the relevance of this issue and gives us some useful tool, first of all the Data Consistency section in the Service Marketplace and, new entry, a very scheming How To Paper.
Last, but not least, from PI-BASIS 2005.1 SAP plans to deliver reconciliation datasources, gradually for every application.

The problem is that this kind of reconciliation tricks takes into account BW data (after integration, transformation and other typical datawarehousing amenities) compared to R/3 data, but what if we don’t know with which we have to compare BW data ?
Not always this is so banal, especially if you don’t have deeply functional knowledgment of the involved area/module.

This is the reason for what it’s more probable to see questions as “How can we determine the origin tables of datasource X” rather than “Which method can we use to reconcile our BW data”…


That is, sometimes (but anyway this method should be the first step to take into account…) it’s enough to go to, insert the datasource name in the search engine and, if you are lucky, you can find a this ready-to-use hint:


But, unfortunately, not always we can thank our lucky stars and, sometimes, we can also find, after a similar search, not so intelligible informations…


As you can see from the above picture, in this case the origin table information has been replaced by a mysterious source…and I challenge anyone to try to understand from where (in R/3) this data comes (if you don’t have any experience in FI-AA, clearly…).


In the underground world of BW-worms, there is a table that is a real source of useful information, a sort of X-ray of every available datasource in the system: ROOSOURCE table.
Let’s start to have a quick look to it.


The most important fields are contained in the include ROEXTRACT:


  • DataSource Extraction Methods (EXMETHODS)

Just knowing what is the extraction method of a datasource and our search of the origins achieves a vast improvement!


A datasource can extract data in several ways:

V‘: over a transparent table or a database view.

D‘: using the fixed values of a domain (useful for very small amounts of data).

F1‘: using a function module, which distributes the data in packages by itself (useful for large quantities of data).

F2‘: as above, using a function module, but with a more simplified interface compared to ‘F1’ (see documentation for data element ROFNAME_S in SE11 for more details).

Q‘: using a functional area of an ABAP query or using an infoset of the infoset query (depending from the release).

A‘: with an append-datasource via BADI.

  • DataSource Extractor (ROEXTRACTO)

This field contains, depending on the extraction method, what we need to know (or, at least, the place where we can look for…) !!!

V‘: name of transparent table or database view using which (generic) extraction is to take place. In this case it’s enough to go to SE16 with this name and the game is over !

D‘: name of domain whose texts are to be extracted (as in the picture below for KOART domain, inside 0ACCT_TYPE_TEXT datasource, via SE11).


F1‘: name of function module for extraction. Go to SE37 to check the extraction code (take a look to a nice template in function group RSAX, FB RSAX_BIW_GET_DATA).

F2‘: as above but the template, always in function group RSAX, is FB RSAX_BIW_GET_DATA_SIMPLE)

Q‘: name of functional area or infoset query (look at SQ01 transaction).

A‘: DDIC name of append structure for reasons of consistency

  • Name of the provider structure for an InfoSource (ROSTRUC)

Here you will find the name of the structure that will pass data after the extraction and this is what we see when R/3 data arrives in BW (even if there could be also a userexit in the middle, the structure of every record will remain the same).

  • DataSource uses generic interfaces (ROGENFLAG)

In this case the datasource uses the generic transfer structure to transfer data through the table parameter e_t_data, typed at runtime. This category includes only datasources generated by the customer (as in LIS or CO-PA).

  • DataSource Delivers Consistent Data with Synchronous Access (ROVIRTFL)

This field shows if the datasource supports direct access from a virtual infocube in BW.

Now, some consideration.

Anything is custom is not involved in this weblog: if something has been built by us (or by someone else, with related technical documentation…), we should know from where this datasource extracts data (apart from loss of memory…)!

On a total of 1512 delivered (std) datasources (PI 2003_1_470), we have this distribution:
A (1), Q (3), F2 (7).
Most of A, Q and F2 datasources are verification and test ones (practically speaking, not relevant).
And, on the podium, D (162, all text master datasource), F1 (623) and V (716).

We already saw that there is no problem with domains and tables/views, so the only obstacle in finding out what we need (the tables of origin) is represented by the extraction done through a function module. And this is the case of the most used transactional extractors.
Often, a single function module can be used by several (and functional similar) datasources: MCEX_BW_LO_API for every LC datasources (more than 60!), MCS_BIW_LIS_API for the extraction of LIS (more than 20), MDEX_MATERIAL_MD (for all datasource extracting from knvk, knvv, kna1, knb1) and so on…

So, which tables are involved in a function module and how can I find some useful info ?
Apart from reading the ‘static code’ from SE37 (but this might be applicable not always), there are some other option…


SQL TRACE (when you flirt with your DB…)

If you want to know from which tables the extraction program pulls out the data and if you have a friendly relation with your DB, you can try your fortune with a reading of the SQL trace, a database-oriented tool provided by SAP as a performance trace function for monitoring and analyzing SQL statements used in reports and transactions (as glossary says).

You can start from RSA3 transaction, that allows to check the extraction content of every active datasource in BW simply in simulation mode (by the way, do you know that in RSA3 you can also, but only from BW 4.0 release, enter the ID of an existing request in your BW to have all the information for that request transfered to the extractor checker via an RFC call from the specified BW system ? Cool and really useful !)…

To activate the SQL trace choose from the menu path System, Utilities, Performance Trace or go directly to ST05 transaction; then turn the SQL trace on.
As already suggested, go to RSA3 and execute an extraction with the datasource for which you need more info.
Now you can return to ST05, turn your trace off (otherwise your system will continue to record every breath you take on your R/3 !!!) and click on the button ‘List trace’ to view the report and analyze the trace the system has been generated during the extraction simulation.
If you look at the column ‘Object’ you can find a list of all the tables hit with the actual values used for the selection.

DEBUGGING (when the truth is at the end of a winding road)

Do you try with SQL trace ?
Are you amazed to see how many tables a system can potentially read during an extraction (and not everything is strictly related to BW) ?

Indeed, now you know what are the tables involved, but this doesn’t mean that you will be able to link a field to a table!
More: how could you understand that a BW-field content comes not simply from a specific R/3 table, but after several more or less complex calculation logics, alternative scenarios (if-clauses) and so on ?

This is paradoxical, but, in this case, last chance is the best chance: debug the extraction !
Generally speacking (from any transaction), it’s enough to enter /H in the transaction code field and press enter: this puts you into debugging mode (the same if you choose from the menu path System, Utilities, Debug).
But in our situation (we are in RSA3 transaction) SAP gives us the possibility to have easier life: just flag the checkbotton ‘Debug Mode’ (shown in the previous picture) and start your extraction.
On the first screen of your debug go to Breakpoints, Breakpoints at, Statement and enter the statement ‘SELECT’, then click on the ‘Continue’ button. In this way the system will stop the program at the first (and at every) ‘SELECT’ statement.

Clearly it’s necessary to know a little about ABAP, in order to understand where it’s useful to stop (by analyzing the processing blocks in the Overview screen) or to avoid unuseful and exhausting loops (in which a single select can be performed many many times!).
But, overall, only by following this way you can find how a specific field is calculated or why a certain record is not taken into account !
Debugging an extraction is surely not by any means an easy task, it is a job which requires a great deal of patience, but only through this method you will be able to see behind the matrix !!!


Sometimes the solution is just in the extract structure name of a datasource (you can see it via RSA5/RSA6 transactions with a double-click on a specific datasource): for example, 0ABCPROCESS_ATTR datasource has BIW_CBPR as extract structure and, in this case, CBPR table (CO-ABC: Activity Master Table) is the source of extracted records.

Besides, you can look at the function module RSA1_SINGLE_OLTPSOURCE_GET that, after having inputed the datasource in OLTPSOURCE parameter, returns all tables linked to that datasource(OLTPSOURCE).

In the end, it’s evident that finding what’s the source of a specific info can be sometimes very easy and requires titanic efforts in other cases. Other different requirements can arise (as happened ETDAT), but there is always a common denominator: the pursuit of the origins. And, in my opinion, what do you get out of having everything ready-made?

You must be Logged on to comment or reply to a post.
  • Hi Roberto!
      Thanks for presenting most useful information as far as BW is considered. Expecting a lot more blogs from you. Expecting a lot more blogs from you. Some how you stopped bloging after the series on LO Extraction.

    with regards

    • Really thanks, Ashwin !
      I would like to post more content, but to assure a certain standard of quality I have to go slowly (since I have to put together my wife, my work, my other interests, SDN forums and so on…)
      Then consider that I have been absent because of illness for a couple of months…
      Last, but not least….now I’m working on another software (even if I hope to come back BW home ASAP)!!!!


  • My Sincere thanks to Roberto and Ashwin as this log has answered many unanswered questions in Extraction.

    We are looking forward for some more logs which can  uncover the underlying tables in SAP BIW.

  • Hi Roberto,

    This is really good info all in one place and I am sure would be a great reference for ‘generations’ of BWers 🙂



  • Bravo, bravissiomo Roberto!

    I have to explain once more my appreciation to you and your great job.

    Thanks for the usefull advices and tips!



  • I don’t have anything to add (except that I would like to ban macros from ABAP – who invented this? It’s only to confuse us poor consultants – and there seems to be a trend in SAP development. It seems like every single function module used for BW extraction uses a macro ;-(
    It was great to read from you again and to read a weblog about BW again.

    BTW will somebody be at the BI Kongress in Duesseldorf in October? I’ll be there.

    Best regards

  • Really thanks for all your feedbacks…it’s encouraging know to have such a kind of beautiful SDN family…

    I don’t think I can come to Dusseldorf…but see you next time, Dirk!


  • Roberto ,
    As always …  amazing post …
    BTW is there anyway to find out if the extractor gives a straightforward extract from many tables or has some transformations and or calculations embedded into the function module? and continuing on Ashwin’s comment ..  wishing you a very happy married life and get well soon …  we will have to keep up the promise of SDN that the average time for response for a query is somewhere around 26 or 36 minutes  , unable to remember Craig’s comment during TechED Bangalore!!!


    • Hi Arun,
      about ‘many tables’ issue, try to take a look to RSA1_SINGLE_OLTPSOURCE_GET…anyway, the answer to your question is negative: there is not an immediate way, you have to debug the code to see for any transformation or, if you are lucky, in business content documentation you can find something useful about calculation logic (but this happens not so often…).
  • I was just getting ready to post this question when I saw this weblog.  I read the log and found the information needed instead of adding one more ‘how to do I find that table’ to forum list.


  • Hello Roberto

    After Seeing your web log i am also inspired to do some simliar type of work , its really amazing and very muuch helpful please coninue this type of work
    Sandeep Singh

  • Hey SDN Team,

    I would request SDN team to make this blog as Featured Weblog at BI Development area for forever. The best weblog i ever read at SDN.

    Great work Mr. Bond-Roberto!!!!!!!!!


  • During the migration process problems have been reported for this blog. The blog content may look corrupt due to not supported HTML code on this platform. Please adjust the blog content manually before moving it to an official community.
  • Hi, maybe you can help me in extractor for Service 2LIS_02_S174.

    We try extend fields the standar structure, but key values is not relevant for use in internal tables.
    Any idea for get HES value in extractor 2LIS_02_S174 ?

    Thank you.

  • I read the blog you recommended, and tried to look for table ROOSOURCE introduced in this blog, but still no luck to find the R3 table name of the datasource 0CUSTOMER_ATTR. I also tried search this datasource in, but still fail to find it’s R3 table name.

    Any idea?


    • Hi dear,
      I’m out from BW stuff from some years…but your question is really simple!
      Have you followed my steps?
      If you go in ROOSOURCE table, you see that the extraction method for this datasource is a function module (F1).
      Well, without need to look in its ABAP code, the name of the extract structure is BIW_KNA1_S, so, it should be really easy to understand (but maybe not) that the source table is KNA1.
      Good luck!
  • Hello Roberto,

    I just came across your blog from one of the posts in the forum, and I have to say it is indeed very very useful, despite it being more than 6 years since you wrote this.

  • Dear Roberto,

    Thank you for such a wonderful post. It stood well against the sands of time. Almost after 9 long years it still is an exceptional post on pursuit of origins as you may call it. Thank you again.

    Dear SCNers,

    It is a SIN not to like or rate such wonderful posts. We will not get people like Roberto writing posts like this often. So kindly show your gratitude.