BI - Informatica Integration Architectural Overview

(1)

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

BI - Informatica Integration Architectural Overview

Applies to:

SAP Net Weaver Business Warehouse (Formerly BI) Business Intelligence homepage. For more information, visit the Business Intelligence homepage.

Summary

This Article will explain you about “BI - Informatica Integration Architectural” Overview.

Author: Maruthi Chowdary Movva Company: HP

Created on: 19 July 2010

Author Bio

Maruthi Chowdary Movva is a SAP BI consultant having around 5 years of

Experience in SAP BI, Working with HP. He has extensively worked in SAP BW/BI Development, Production Support, BI Technical Upgrade and Functional upgrade projects.

(2)

Introduction

This article will help the people who are working in SAP BI-Informatica Integration project. This article will give Brief over view on Open Hub destination modeling, Performance tips while extracting the data from source to External tables, Power Connect details and Work Flow calling/configuration from BW.

Live Scenario

I am putting my pen to make this article based on my experience, we have different reporting system other than BI system, we got the requirement such that Data in BW and Data in Informatica should consolidate and based on consolidated data in informatica we need to generate report.

BI- Informatica Integration Architectural Overview:

As shown the Architecture diagram, we see from 3 sources, we are extracting the data from Open hub destination to External tables. I.e. 3 open hub extractors are extracting data from 3 sources

(Cube/DSO/Master Data) into their respective Target External Tables.

Work Flows are used to trigger the data from External tables into Informatica Data staging layer (This is as like we have EDW layer in BW). After that data can be transferred into ODS or CUBE for reporting purpose.

Note: We have different tools for Informatica reporting i.e. Poly vista, Crystal reports…etc.

(4)

Open Hub Destination Modeling

Definition

The open hub destination is the object that allows you to distribute data from a BI system to non-SAP data marts, analytical applications, and other applications. It ensures controlled distribution across multiple systems.

Open Hub Sources: BI objects such as Info Cubes, Data Store objects, Info Objects (attributes or texts), and Info Sets can function as open hub data sources. Note that Data Sources may not be used as the source.

Data transformation process: We can use the data transfer process to update data to the open hub

destination. This involves transforming the data. Not all rule types are available in the transformation for an open hub destination: Reading master data, time conversion, currency translation, and unit conversion are not available.

Open Hub Table Output: 1) Database Table.

2) Flat file.

3) Third-Party Tools As Destinations

But in this article we are mainly dealing with “Open Hub external table” and best practice for extracting data into External tables.

Data Update Modes in Open Hub Destination

Before discussing about the Delta process, it is good idea to know about Open Hub destination Technical key and Schematic Key.

Technical Key: Technical key will maintain unique Key with technical fields OHREQUID (open hub request SID), DATAPAKID (data package ID), and RECORD (sequential number of a data record to be added to the table within a data package). This will be used whenever we are having Open hub output as DB tables i.e.

If we have same data record already exist in Data base and the same records is updating will cause Short dump.

Schematic Key: All the fields in field list will be selected as schematic keys. With the schematic key selection they might be chance of having duplicate data records.

In this paper I am mainly going to explain about performance optimization of Open destination with DB tables as output.

DSO Data Update process:

Full Update: Full update data can be updated from Active data table into Open hub external tables.

Delta update: Delta data can be updated from Change log table into Open hub external tables.

Delta Initialization: Delta initialization can be done from Active data table or from Change log table.

Cube:

Full and Delta update: Full and Delta update can carried based on Dim & Fact table.

Master Data:

Text: Only full update can be done based on Master data Text object. From Text table open hub destination will retrieve the data.

Attribute: Only full update can be done based on Master data Attribute object. From P-table open hub destination will retrieve the data.

(5)

Performance Tips While Implementation Open hub Destination

Scenario: I Have Sales Open hub, such that, I have to populate sales data from 2 DSO. i.e. One open hub is getting data from 2 Data store objects.

For Increasing the Data load performance, we can take below listed tasks.

1) Index creation on the select statement fields used in routine.

2) Routine declaration with Hash table.

For Increasing the Data Base performance, we can take below listed tasks.

1) While extracting data from Source to External tables level, It is recommended to delete the data from external tables level for each delta update.

Pros and Cons – “Deletion of Data before external table” Option in Open Hub Destination.

Pros: some time due to source system issues power connect may not trigger the data from BW system to informatica system, When we have all the historical data in external tables level , based on selection condition work flows can easily trigger the data from External tables to Informatica staging layer.

Cons: If we don’t have data in external table’s level, we have to follow extract the data from source target to open hub external tables level and also need to trigger the Work flows manually to pull the data from External tables to Informatica Data staging layer.

Preferable opinion on “Data deletion before data loads” while Extracting data from Open Hub extractor into External DB tables:

Since we have redundant data available in External tables, it is advisable to Delete the data before each time of data execution .

High level “Power Connect” Information

As we discussed in the Architecture diagram, we should have “Power connect “connected in between SAP BW system and Informatica system. Power Connect will act as source system connectivity, how we will connect SAP BW system to R/3 system.

In this article I am just explaining high level information on Connectivity.

2) We are having the following connection types available in SAP system to Connect to SAP/Non SAP systems.

 Type B is recommended for connecting to an SAP system (using Load Balancing).

 Type A is only to be used if you want to connect to a specific application server.

 Type 2 is for connecting to an R/2 System only.

 Type E is for RFC client programs working with another external program as RFC server program.

 Type T is start connecting external system Using TCP/IP

 Type 3 is to Connection to ABAP System.

 Type 2 is to Connection to R/2 System.

3) As we are connecting from SAP system to Non SAP system. We have to use Connection type as “T”

(TCP/IP Connection) in BW system.

4) In Informatica system for establishing connectivity, we can use Connection type as “3” (Connection to ABAP System).

By using Type “3”, system will treat to connect to 3 type system and it will connect to R/3 system - specific application server.

5) Work flows created in Informatica system are mapped to Power connector once Power connect programs are successfully

6) Installed in Informatica system.

(6)

7) In below fig we can able to see the list of Work flows mapping to Power connect.

8) Automating / Calling of Work flows in BW side.

For Automating the Work flows from BW side, we need to call the Work flows via Process chains by Workflow (Remote Also) Process type.

Below are some Mandatory things needed to do in BW side before implementing Automation process.

I. Install Power Center in BW System.

II. Register the Power Exchange for SAP Net Weaver client.

III. Install the SAP RFC SDK libraries.

After you install Power Exchange for SAP Net Weaver, complete the configuration tasks.

(7)

Workflow Configure in SAP BW/BI

To configure Informatica work flows in BI/BW, We should enable the Power connect Program in BW i.e.

Work Flows can be called in BW side by the program “ZPMSENDSTATUS”.

ZPMSENDSTATUS will not available by default in BW, it should be enabled by installing Power Center/Power connect software in BW system.

Below is the process to enabling ZPMSENDSTATUS in BW side.

1) From the SAP BW window, enter the SE38 transaction.

2) Enter ZPMSENDSTATUS for the program name and select create.

3) Enter a Title.

4) Select Executable Program as the Type.

5) Select Basis as the Application.

6) Select Start Using Variant.

7) Click Save.

8) Click Local Object.

9) The ABAP Editor window appears.

10) Click Utilities > More Utilities > Upload/Download > Upload.

11) Navigate to the zpmsendstatus.ab4 file located in the sap trans/BW directory on the Power Center installation DVD.

12) Save the program.

13) Activate the program.

Process to Trigger Informatica Work Flow in BW System

Go to SE38 transaction by provide program name as “ZPMSENDSTATUS” and give the respective variant by Clicking “With Variant” Tab as shown in the below Figure.

Note: We will call the connection as Power Connect if it is below 8.6 version, we will call as Power center if it is 8.6 Version.

For BW 3.5 We will use Power connect and For 7.0 We can use Power center.

(8)

Disclaimer and Liability Notice

This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.

SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk.

SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.

BI - Informatica Integration Architectural Overview

BI - Informatica Integration Architectural Overview

Applies to:

Summary

Author Bio

Table of Contents

Introduction

Live Scenario

BI- Informatica Integration Architectural Overview:

Open Hub Destination Modeling

High level “Power Connect” Information

Workflow Configure in SAP BW/BI

Related Content

Disclaimer and Liability Notice