In SAP BW we have different methods to extracting data from other data sources than SAP.The business scenario is as explained below.Your company wants to add data from other sources to the data in its own SAP source systems. These external sources of data include non-SAP operative systems and flat files containing data from subsidiaries of your company.Before you proceed, you want to find out about the technology that SAP provides to enable you to connect these sources of data to your BW system.
Third-party tools allow you to extract master data and transaction data from non-SAP operative systems. Third-party tools convert data from a non-SAP source system into a format that the BW system is able to understand. This kind of data is subject to the same data staging infrastructure as data from SAP source systems. External data is homogenized in the transfer rules and stored in the InfoSources together with other data ready to be updated.There are several third-party tools that you can use to prepare data from non-SAP systems for extraction into the SAP Business Information Warehouse. These tools usually consist of an administration component for managing metadata and a transformation engine (an ETL component) for converting transaction data into the structure that is required by the BW.
This required structure is stored on the BW server as the transfer structure. The administrator has to maintain this transfer structure for each InfoSource. A special BAPI interface tells the component of the third-party tool that manages metadata detailed information about the structure.All third-party tools are certified by SAP for this interface in its Complimentary Software Program.The use of this inteface or the calling of BAPIs individually is not reserved exclusively for certified partners. SAP customers are of course also able to use the interface and BAPIs in their own development programs.
Role of the Staging BAPIs
Open interface to the staging engine
for extraction tools from third-party providers and for customers (for own extraction programs)
Exchanging metadata
Creating InfoObjects, InfoCubes, InfoSources, InfoPackages
Activating available metadata
Detailed information about available metadata
Importing transaction data
Importing master data (attributes, texts, hierarchies)
Scheduling data transfers from third-party tools
Status information about data requests
The individual BAPIs of the BW STA contain methods for updating and staging metadata as well as for sending data extracts to the SAP Business Information Warehouse.Customers and providers of third-party tools use the individual modules of this interface to connect their metadata repositories and extraction engines to the SAP Business Information Warehouse.
Tools for Extracting, Transferring and Loading Data (ETL Tools)
As the name suggests, ETL tools:
Ÿ
Ÿ
Extract data from a source system
Transform and transfer this data into the format required by the respective target system Ÿ Load the transformed data into the target system
Transform and transfer this data into the format required by the respective target system Ÿ Load the transformed data into the target system
The transformation engines of the third-party tools from the BW external data extraction infrastructure are types of ETL tool. Once the incoming structure fields have been mapped to the outgoing structures, transformation rules adjust the internal structure of the external data so that it meets the requirements of the BW.
The final step is to load the transformed data into the BW system. The loading process is controlled in the BW. You have to create an InfoPackage and schedule it in the scheduler. The procedure for uploading external data is therefore the same as the procedure you use to extract data from a SAP source system. The individual function modules of the staging BAPI make this possible.
Third-Party Tools: Step-by-Step Process
Use staging BAPIs to create BW source system (non-SAP type, transferring data and metadata)
Define BW RFC connection for the non-SAP source system
Assign BW non-SAP system to appropriate InfoSources
Maintain BW InfoSources, transfer rules, and transfer structures
Import third-party tool InfoSource definition
Map data and define transformation rules for third-party tool
Define BW InfoPackage - additional tab page: 3rd Party Selection
Schedule BW InfoPackage
Monitor data loading process for BW and third-party tool
Define BW RFC connection for the non-SAP source system
Assign BW non-SAP system to appropriate InfoSources
Maintain BW InfoSources, transfer rules, and transfer structures
Import third-party tool InfoSource definition
Map data and define transformation rules for third-party tool
Define BW InfoPackage - additional tab page: 3rd Party Selection
Schedule BW InfoPackage
Monitor data loading process for BW and third-party tool
BW File Interface
The file interface provides you with a practical way of transferring master data and transaction data from non-SAP systems into the SAP Business Information Warehouse.Using the file transfer protocol (FTP) the data is staged on an R/3 application server or on a workstation. This enables the SAP Business Information Warehouse to access the data. The extraction process is controlled in the scheduler and its progress observed in the monitor in accordance with the BW standard procedure. If you want to use the file interface to load data, one prerequisite is that you maintain the required metadata manually on the BW server involved in the transfer of the master data or the transaction data.
File Interface
Note the following regarding Excel CSV files:
Ÿ
Ÿ
Excel files uses separators to separate fields (a semicolon ";" for Europe and a comma "," for the USA). Other separators can also be used. You have to specify in the scheduler the type of separator that you are going to use.
Character fields that are not filled in a Excel CSV file are filled with a blank space, and numerical fields that are not filled in a Excel CSV file are filled with 0.If an incorrect separator is included in the file, the system interprets this character as data information and may generate errors in this data record.
Note the following regarding Excel CSV files and ASCII files:
Ÿ
If your file includes headers that you do not want the system to read, you have to specify on the External Data Parameters tabstrip the number of headers that you want the system to ignore during the data upload. This gives you the option of retaining the column headers for your file. Ÿ Whether or not you specify leading zeros depends on the conversion-exit. Ÿ You generally use the YYYYMMDD date format without internal separators. The conversionexit that is used determines which other formats are availale for you to use. Ÿ Note the limit of 1,000 bytes for the length of data records if you are using IDocs to upload data. This limit does not apply if you are loading data from the PSA.
Extraction Process for Master Data /Texts / Hierarchies
Before you are able to use the file interface to load data into a BW, you have to create a corresponding source system in the Administrator Workbench. You usually use the context menu (right mouse-click) of the root node in the source system tree to create new source systems. For flat files, choose File System -> Metadata (Manually) -> Data using File Interface. In the InfoSource tree, you use the context menu (right mouse-click) of the InfoSource to assign a DataSource to an InfoSource. When you trigger the Assign DataSource function, you have to make sure that you assign the right source system to your InfoSource.
With source sytems for flat files, the system generates three different DataSources automatically (one for attributes (master data), one for texts, and one for hierarchies) and takes you to the screens for maintaining InfoSources. You have to specify manually the structure in which you want the data in the flat file to be stored (Transfer Structure tabstrip).
When you upload hierarchies, there is the additional step of maintaing the hierarchy.Once you have activated the InfoSource you are able to load master data from a flat file. The extraction process is controlled in the scheduler and its progress observed in the monitor in accordance with the BW standard procedure.
Tips for Loading Master Data
The sequence of master data and text fields in transfer structur has to correspond to the sequence of the fields in the flat file.Keys for the compounding of characteristics MUST exist in both the attribute files and the text files (if applicable). Time-dependent master data: The DATETO and DATEFROM fields MUST exist in the attribute files and the text files or have to be filled in the transfer rules.
Maintaining Hierarchies in the Transfer Structure
Define only top-down hierarchies (ID of the higher-level node < ID of the lower-level node) in your flat files.The structure of your flat file is NOT identical to the structure of the hierarchy table /BIC/H.
You can arrange flat files with hierarchy-data however you like. You have to set the appropriate switch and specify a CHILDID and a NEXTID for each data record. Hierarchy versions and general validity periods (time-dependent hierarchy names) do not require entries in the flat file. In the detail maintenance screen for characteristics you use the Hierarchy tabstrip to define more complex forms of hierarchy such as these.
Structure of a Flat Hierarchy File
The below table shows the structure that a flat file must have if you want to load hierarchies from it.
ŸThe rows displayed in green are added only if the hierarchy is a sorted hierarchy.
ŸThe rows displayed in blue are added if you are working with time-dependent hierarchies.
ŸThe rows displayed in red are added if you want the hierarchy to contain intervals.
ŸThe rows displayed in green are added only if the hierarchy is a sorted hierarchy.
ŸThe rows displayed in blue are added if you are working with time-dependent hierarchies.
ŸThe rows displayed in red are added if you want the hierarchy to contain intervals.
Make sure that the sequence of the objects in the hierarchy (InfoSource) maintenance dialog corresponds to the sequence of the fields in the flat file.The system transfers the existing interval settings and the settings that determine time-dependency automatically from the InfoObject maintenance. The system adds extra fields to the table depending on the settings that you made in the InfoObject maintenance.Ÿ Example: The valid-from date and valid -to date fields are added if you set the time-dependency switch in the hierarchy maintenance. If you chose the time-dependent hierarchy nodes option in the InfoObject maintenance, this switch is activated already.
Extraction Process for Transaction Data
Before you are able to use the file interface to load data into BW, you have to create a corresponding source system in the Administrator Workbench. You usually use the context menu (right mouseclick) of the root node in the source system tree to create new source systems. For flat files, choose File System -> Metadata (Manually) -> Data using File Interface.
In the InfoSource tree, you use the context menu (right mouse-click) of the InfoSource to assign a DataSource to an InfoSource. When you trigger the Assign DataSource function, you have to make sure that you assign the right source system to your InfoSource. With source systems for flat files, the system generates a DataSource for transaction data automatically and takes you to the screens for maintiaing InfoSources.
You have to specify manually the structure in which you want the data in the flat file to be stored (Transfer Structure tabstrip). On this tab page you also have to specify the update mode that you want the DataSource to support. With uploads of transaction data there is an additional step in which you have to use the update rules to join the InfoSource that you have defined with a data target. The extraction process is controlled in the scheduler and its progress observed in the monitor in accordance with the BW standard procedure.
Tips for Loading Transaction Data
In the maintenance screens for the transfer structure, specify an InfoObject for each field in your flat file. Make sure that the sequence of these InfoObjects corresponds to the sequence of the columns in your flat file.Check that the formatting is valid for an EXCEL CSV record as defined in the scheduler (correct use of separators).If, when you are loading transaction data, there are two data records with identical characteristic values and dimensions, the key figures are summarizied into a single data record.
The sequence of the data in a flat file MUST correspond to the sequence of InfoObjects specified in the transfer structure. If the sequences are not identical the transfer structure is not filled correctly.
Related Posts
Info objects in sap bw
No comments :
Post a Comment