Today’s post is about the ETL process, which forms the basis of data flow in the SAP BI system.I will go into detail on what the E,T and L stand for and how they are tied together to move data into BI and up into the the point where it ready for reports to be made on that data.
The ETL process brings data from external sources into the BI system which will be used later for analysis. There are three stages in this process, which is derived from the abbreviation ETL
• Extraction
• Transformation
• Loading
Extraction
Extraction is the process of bringing in raw data from a range of supported source systems into the persistent storage area (PSA). The two objects that are active in this process are the source systems and the PSA, which is sometimes referred to as the datasource. The PSA stores the data temporarily before the transformation process begins
There are lots of supported source systems which can be used to bring data into the PSA.
- XML, you can post data from web services that support the SOAP protocol to the BI system
- Data can also be extracted from custom databases. For example Oracle or SQL server databases. These use the DBConnect/UDConnect adapters
- Flat files like excel can serve the BI system with data using file interfaces
- Data can also be retrieved from SAP systems like HR, MM, SD, CRM. These connect using the BI service API
ETL PSA Soure system
Transformation
After the data has been extracted from the source system, it has to be cleansed and transformed into something meaningful for the BI system. After the transformation has been done, it can be stored in the InfoObjects.
InfoSources are used for transformation. It serves like an adapter between the source systems and the InfoCubes.
All the fields in the source system are mapped to the corresponding fields (InfoObjects) in the InfoCube. For example customer name will correspond to client name InfoObject.
The InfoSource serves to make the InfoCube independent of the source system. When a source system is swapped for another one, only the InfoSource gets to change. Transformation rules are be applied to the InfoSource. This can be either direct correlations, formulas or combinations. I will discuss more on transformation rules in a later lesson
Loading
The next step in the process is to load the data into the end target. The end target referred to in this case is the InfoProvider of which there are many types. The load process will be different depending on which type of InfoProvider is being used. You can either have records being added and records being updated with some change management going on.
Now that the data is loaded, the stage is set for reports to be made. This is the reason why BI exists, to display reports for intelligent business decisions can be made.
There you have it, the ETL process being a major part of getting data ready for reports. If you have any questions, I would love to answer them, so leave a comment.
How to increase your chances of starting a successful SAP career
You might be asking yourself,
Is SAP right for me?
What are my chances of getting a good SAP job?
The good news is you are not alone. A lot of people have the same questions as well. What is even better news? I have written a series of articles that answer these questions and many more on SAP Unveiled.
{ 2 comments… read them below or add one }
you know i have read all. And now I am thinking, is it working like if you want to transfre ready files to SAP system, like I am using in excell macro , punct input, and then it goes to Agresso, which i am using here in Norway, for exaple, and then all transfred in system? or what I am not getting it, really.
Sir thank you so much for sharing such notes with us. Can you share something related to different types of Extractors used for SAP BI or give us some ppt. for it.