Unified reporting using SAP Business Objects BI 4.1

[Former Member]

Summary:

   Creating semantic layer (Universe) and reporting over three different data source such as HANA, TERADATA and SAP BW using relational connection.

Author: Vinodh Kumar Gunasekar

Company: Capgemini Consulting India

Vinodh  is a HANA Consultant currently working with Capgemini Consulting, India. His area of expertise includes SAP  HANA,BW,BO

Unified reporting using SAP Business Objects BI 4.1

Creating a universe with three different data sources.Generally BW can only be connected through a OLAP connection and it cannot be used for a multisource connection.Now the BW can be connected using a relational connection and a BW cube can be used for a multisource connection in a universe.To connect the BW as relational connection we require a JCO connection to be established in the BO server a RFC connection has to be established between SAP BW system and the BO server.

Hana can be connected using a JDBC or ODBC driver and the same way Teradata can also be connected using a JDBC or ODBC driver.

Universe

A Business Objects Universe is the semantic layer that resides between an organization’s database and the end user but more importantly, it is a business representation of your data warehouse or transnational database.  It allows the user to interact with their data without having to know the complexities of their database or where the data is stored.  The universe is created using familiar business terminology to describe the business environment and allows the user to retrieve exactly the data that interests them.

• A connection parameter to a single and multiple data structure.
• SQL structures called objects that map to actual SQL structures in the database.  All objects are grouped into classes and sub classes.
• A schema of the tables and joins from the database.  The objects are built from the tables that are included in the schema.

Advantages of a Universe

• Only the universe designer needs to know how to write SQL and understand the structure of the underlying database.
• The interface allows the creation of the universe in an easy-to-use graphical interface.
• All data is secure.  The data is read-only so there is no danger of the data being edited or changed by the end user.
• The reports are created using a simple interface using drag and drop techniques.
• All users use consistent business terminology.
• Users can analyze data locally.

OLAP Universe:

A  OLAP universe is a Business Objects universe that has been generated from a OLAP cube or query. The universe is created automatically from a selected connection to a OLAP data source using an OLAP query flattening driver that is installed as an add in to Designer XIR2.Once the universe has been created it can be exported to the Central Management System (CMS) as any other universe, and is then available to Web Intelligence users to run queries and create reports .You create a OLAP universe by selecting a OLAP connection to a Query Cube or InfoCube. The universe creation process is automatic once you have selected the connection. OLAP structures are mapped directly to classes, measures, dimensions, and details. The universe structure appears in the Universe pane. There is no table schema in the Structure pane.Once you have created the OLAP universe, you can modify any of the universe components as for any other universe.

Relational Universe:

A relational database is a database that has a collection of tables of data items, all of which is formally described and organized according to the relational model. Data in a single table represents a relation, from which the name of the database type comes. In typical solutions, tables may have additionally defined relationships with each other.The universe created on the relational data base is called relational universe

The two types of relational connections include the Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC)

ODBC – Open DataBase Connectivity

Microsoft-driven specification for relational reporting

Database requests are made via SQL (Structured Query Language)

Heavily adopted in industry

No longer Microsoft-centric - Unix and Linux drivers exist for ODBC

JDBC – Java DataBase Connectivity

Relational reporting drivers specified by the Java community.  Popular on Unix platforms.

STEPS TO CONNECT THE DIFFERENT DATA SOURCE IN IDT.

Connecting Teradata.

1. Initially a project has to be created in IDT

Right click on the left upper pane and select NEW ->PROJECT and name the project

2. Create a Relational Connection for Teradata Right click on the left upper pane -> NEW->

Relational Connection

3.  Enter the name of the connection name and click NEXT

4. Select TERADATA and select the installed drivers and click NEXT

5.Enter the logon details for teradata and select the datasource .Once the details are entered click on TEST CONNECTION to make sure the connection is successful and click FINISH.

6. A .cnx file is created  and the connection has to be published to the repository

Right Click on the connection created (.cnx file) and select PUBLISH CONNECTION TO A REPOSITORY.

Once the Connection is published to the repository  a .cns file is created.

Creating Connection For HANA.

7.For Creating HANA Connection a relational connection is created as in STEP 2.

8.Select the HANA driver under SAP folder and follow the similar  STEPS 4 & 5.

Connecting BW As A Relational connection.

Generally to connect a BW cube in a universe we require a OLAP connection (BICS connection) Since the BW is connected with OLAP  joining a Relationally connected source with the cube is not possible.To over come this and to connect the BW cube using a relational connection is also possible .

To connect the BW Cube in a relational connection we require a JCO (JAVA CONNECTOR).

    JCO  has to be installed in the BO server and a RFC connection has to be established between SAP BW system and the BO server in SM 59 .The JCO leverages IDT to select the cube from the BW environment and use the cube in the data foundation layer where the different sources can be joined and a Business layer can be created and the Universe can be published.

9.To Create a BW relational connection Right click on the connection in the lower left pane -> INSERT RELATIONAL CONNECTION as shown in the below diagram.

10.Enter a name to the connection in the RESOURCE NAME space and click NEXT.

11.In the RELATIONAL CONNECTION screen expand the SAP folder and the SAP JCO has to selected under the SAP NETWEAVER BW connection folder as shown in the above diagram and click NEXT.

12.Enter all the required entries in the Connection screen and the info provider name has to be mentioned in the INFO PROVIDER space and test the connection

13.The Created Relational connection will be available in the connection folder in the left Below pane , RIGHT CLICK on the connection and create a short cut so that the connection is assigned to the project created as shown in the below diagram.

Creating the data Foundation.

All the connections are created and the data foundation layer has to be created  for three source.

14.Right click on the project created ->NEW->DATA FOUNDATION->Give a name to the data foundation in the POP-UP screen  and click NEXT as shown in the below diagram.

15.Select Multisource Enabled as shown in the below diagram and click NEXT. The multisource enabled selection will leverage the data foundation to have more than one source.

16.Once the multisource enabled is selected in the data foundation, the next screen shows the selection of the data source all the published connection under the project are shown in the selection screen as shown above. The required connection can be selected as per the requirement. For our scenario all the three connections have to be selected. Once the connections are selected click FINISH.

Creating Joins in the data foundation layer

In the above screen all the three connections are visible in different colour.

Teradata--- Blue

HANA---orange

BW---Green

We have selected the info provider while connecting the BW as shown in STEP 11.Therefor the cube with the extended star schema with the fact tables dimension tables will be shown once the data foundation is selected.

The tables from other two sources can be selected by expanding the connections (By expanding the + symbols) in this scenario I have used a sales cube from the BW and the MARA data from Teradata and the data from the extractor 2lsi_11_vahdr  from HANA.

In the right part of the screen the tables from HANA Teradata and the cube from the BW are joined with the respective fields as shown in the above diagram.

Creating Business Layer over the data foundation

A Business layer is created over the data foundation which is published into the repository and a universe is created with file extension .unx

The below screen shows the fields from all the three data sources the HANA_EVI has all  field from HANA table and similarly from teradata and BW. The field from all the three sources can be consolidated in a single folder or can be used as shown.

Once the business layer is created the business layer has to be published into the repository. Right click on the business layer ->PUBLISH TO THE REPOSITORY.

When the business layer is published a .unx file is got and which is universe .The universe can be consumed in all the SAP BO  reporting suite.

The below screen shows the particular universe created can be consumed in WEBI report

Below is the report created over the universe BL_TERA_HANA_BW. To differentiate the fields from the different data source the colour difference is shown with reference to the color in data-foundation.