SAP BusinessObjects BI 4.3

16.15.2. SAP BusinessObjects BI 4.3

This section describes to setup database connections to various database systems for SAP BusinessObjects BI 4.3.

Note: The SAP BusinessObjects BI 4.3 install directory is referred to as <install directory>.

Microsoft SQL Server #

Note: SAP BusinessObjects BI 4.3 is using JRE 8, therefore, the JRE 8 version of the Microsoft JDBC Driver need to be used.

Download the Microsoft JDBC Driver from: Download Microsoft JDBC Driver for SQL Server.

Copy the Microsoft SQL Server JDBC Driver mssql-jdbc-7.0.0.jre8.jar into <install directory>/dataAccess/connectionServer/jdbc/drivers/mssql.
Open <install directory>/dataAccess/connectionServer/jdbc/sqlsrv.sbo in a text editor.
Configure the default classpath to the MS SQL Server database configurations by adding the following Path as a child element to the default ClassPath element:

<Path>$ROOT$/jdbc/drivers/mssql/mssql-jdbc-7.0.0.jre8.jar</Path>

Example:

<Defaults>
  <Class JARFile="dbd_mssql">com.sap.connectivity.cs.java.drivers.jdbc.mssql.MSSQLDriver</Class>
  <JDBCDriver>
    <ClassPath>
      <Path>$ROOT$/drivers/java/dbd_jdbcwrapper.jar</Path>
      <Path>$ROOT$/jdbc/drivers/mssql/mssql-jdbc-7.0.0.jre8.jar</Path>
    </ClassPath>
    <Parameter Name="JDBC Wrapper">com.sap.connectivity.cs.java.drivers.jdbc.wrapper.JDBCWrapper</Parameter>
  </JDBCDriver>
  <Parameter Name="Family">Microsoft</Parameter>
  <Parameter Name="SQL External File">sqlsrv</Parameter>
  <Parameter Name="SQL Parameter File">sqlsrv</Parameter>
  <Parameter Name="Description File">jdbc</Parameter>
  <Parameter Name="Strategies File">sqlsrv</Parameter>
  <Parameter Name="Driver Capabilities">Query,Procedures</Parameter>
  <Parameter Name="Extensions">sqlsrv,jdbc</Parameter>
  <Parameter Name="Connection Shareable">Yes</Parameter>
  <Parameter Name="Shared Connection">No</Parameter>
  <Parameter Name="Array Fetch Available">True</Parameter>
  <Parameter Name="Array Fetch Size">10</Parameter>
  <Parameter Name="Array Bind Available">False</Parameter>
  <Parameter Name="Max Rows Available">Yes</Parameter>
  <Parameter Name="Query TimeOut Available">Yes</Parameter>
  <Parameter Name="Quote Identifiers">False</Parameter>
  <Parameter Name="Optimize Execute">True</Parameter>
</Defaults>

OLEDB connection from Client to SQL Server (2016 and higher) #

Microsoft have changed their drivers for OLEDB connections, So need to make some adjustments in the default sqlsrv.sbo file to work with latest Mircrosoft OLEDB drivers placed at <install directory>/dataAccess/connectionServer/oledb/sqlsrv.sbo. In the default sqlsrv.sbo file need to change value of parameters Provider CLSID and Enumerator CLSID from default SQLNCLI11 to MSOLEDBSQL.

<Parameter Name="Provider CLSID">MSOLEDBSQL</Parameter>
<Parameter Name="Enumerator CLSID">MSOLEDBSQL Enumerator</Parameter>

Example customized sqlsrv.sbo file for MS SQL Server 2019:

<?xml version="1.0" encoding="UTF-8"?>
<DriverConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../sbo.xsd">
  <Defaults>
    <Parameter Name="Family">Microsoft</Parameter>
    <Parameter Name="SQL External File">sqlsrv</Parameter>
    <Parameter Name="SQL Parameter File">sqlsrv</Parameter>
    <Parameter Name="Description File">sqloledb</Parameter>
    <Parameter Name="Driver Capabilities">Query,Procedures</Parameter>
    <Parameter Name="Extensions">sqlsrv,oledb</Parameter>
    <Parameter Name="Array Fetch Available">True</Parameter>
    <Parameter Name="Array Bind Available">True</Parameter>
    <Parameter Name="Query TimeOut Available">True</Parameter>
    <Parameter Name="Max Rows Available">True</Parameter>
    <Parameter Name="SSO Available" Platform="MSWindows">True</Parameter>
  </Defaults>
  <DataBases>
    <DataBase Active="Yes" Name="Generic OLEDB Provider">
      <Parameter Name="Provider CLSID">MSOLEDBSQL</Parameter>
      <Parameter Name="Enumerator CLSID">MMSOLEDBSQL Enumerator</Parameter>
      <Parameter Name="SSO Available">False</Parameter>
    </DataBase>
    <DataBase Active="Yes" Name="MS SQL Server 2019">
      <Library>dbd_wsqloledb</Library>
      <Parameter Name="Query TimeOut Available">No</Parameter>
      <Parameter Name="Family">Microsoft</Parameter>
      <Parameter Name="Version">mssqlserver_oledb.setup</Parameter>
      <Parameter Name="Driver Capabilities">Query,Procedures,Cancel</Parameter>
      <Parameter Name="SQL External File">sqlsrv</Parameter>
      <Parameter Name="SQL Parameter File">sqlsrv</Parameter>
      <Parameter Name="Description File">sqloledb</Parameter>
      <Parameter Name="Provider CLSID">MSOLEDBSQL</Parameter>
      <Parameter Name="Enumerator CLSID">MSOLEDBSQL Enumerator</Parameter>
      <Parameter Name="Extensions">sqlsrv2019,sqlsrv,oledb</Parameter>
      <Parameter Name="SSO Available" Platform="MSWindows">True</Parameter>
    </DataBase>
  </DataBases>
</DriverConfiguration>

SQL Server Settings #

SQL Server database needs these following settings as well in order to work better so both SICS and reports will run a bit faster with these settings. The following will allow SQL Server to use multiple threads when running the SQL.

In SQL Server Database, under Database Properties

Database Scoped Configurations	Settings
Legacy Cardinality Estimation	ON
Max DOP	4
Max DOP For Secondary	4
Query Optimizer Fixes	ON
Is Read Committed Snapshot On	True

Oracle #

Download the Oracle JDBC Driver from: Oracle Database 12.1.0.1 JDBC Driver & UCP Downloads.

Copy the Oracle JDBC Driver ojdbc8.jar into <install directory>/dataAccess/connectionServer/jdbc/drivers/oracle.
Open <install directory>/dataAccess/connectionServer/jdbc/oracle.sbo in a text editor.
Configure the default classpath to the Oracle database configurations by adding the following Path as a child element to the default ClassPath element:

<Path>$ROOT$/jdbc/drivers/oracle/ojdbc6.jar</Path>

Example:

<Defaults>
  <Class JARFile="dbd_oracle">com.sap.connectivity.cs.java.drivers.jdbc.oracle.OracleDriver</Class>
  <JDBCDriver>
    <ClassPath>
      <Path>$ROOT$/drivers/java/dbd_jdbcwrapper.jar</Path>
      <Path>$ROOT$/jdbc/drivers/oracle/ojdbc6.jar</Path>
    </ClassPath>
    <Parameter Name="JDBC Wrapper">com.sap.connectivity.cs.java.drivers.jdbc.wrapper.JDBCWrapper</Parameter>
  </JDBCDriver>
  <Parameter Name="Family">Oracle</Parameter>
  <Parameter Name="SQL External File">oracle</Parameter>
  <Parameter Name="SQL Parameter File">oracle</Parameter>
  <Parameter Name="Description File">oracle</Parameter>
  <Parameter Name="Strategies File">oracle</Parameter>
  <Parameter Name="Driver Capabilities">Query,Procedures</Parameter>
  <Parameter Name="Extensions">oracle,jdbc</Parameter>
  <Parameter Name="Connection Shareable">Yes</Parameter>
  <Parameter Name="Shared Connection">No</Parameter>
  <Parameter Name="Array Fetch Available">True</Parameter>
  <Parameter Name="Array Fetch Size">250</Parameter>
  <Parameter Name="Array Bind Available">False</Parameter>
  <Parameter Name="Max Rows Available">Yes</Parameter>
  <Parameter Name="Query TimeOut Available">Yes</Parameter>
  <Parameter Name="Optimize Execute">True</Parameter>
  <Parameter Name="Include Synonyms">False</Parameter>
</Defaults>

PostgreSQL #

Download the PostgreSQL JDBC Driver from: PostgreSQL Database 14.1 JDBC Driver & UCP Downloads.

Copy the PostgreSQL JDBC Driver postgresql-42.3.7.jar into /dataAccess/connectionServer/jdbc/drivers/oracle.
Open /dataAccess/connectionServer/jdbc/PostgreSQL.sbo in a text editor.
Configure the default classpath to the PostgreSQL database configurations by adding the following Path as a child element to the default ClassPath element:

<Path>$Roots$/jdbc/drivers/ postgresql-42.3.7.jar</path>

Example:

<Defaults>
  <Class JARFile="dbd_postgresql"> com.sap.connectivity.cs.java.drivers.jdbc.postgresql.PostgreSQLDriver</Class>
  <JDBCDriver>
    <ClassPath>
      <Path>$ROOT$/drivers/java/dbd_jdbcwrapper.jar</Path>
    </ClassPath>
    <Parameter Name="JDBC Wrapper">com.sap.connectivity.cs.java. drivers.jdbc.wrapper.JDBCWrapper</Parameter>
  </JDBCDriver>
  <Parameter Name="Family">Generic</Parameter>
  <Parameter Name="Description File">jdbc</Parameter>
  <Parameter Name="SQL External File">postgresql</Parameter>
  <Parameter Name="SQL Parameter File">postgresql</Parameter>
  <Parameter Name="Driver Capabilities">Query</Parameter>
  <Parameter Name="Extensions">jdbc</Parameter>
  <Parameter Name="Connection Shareable">Yes</Parameter>
  <Parameter Name="Shared Connection">No</Parameter>
  <Parameter Name="Array Fetch Available">True</Parameter>
  <!--  define &quot;magic&quot; value 0 for Array Fetch Size to use default JDBC Fetch Size  -->
  <Parameter Name="Array Fetch Size">0</Parameter>
  <Parameter Name="Array Bind Available">False</Parameter>
  <Parameter Name="Max Rows Available">Yes</Parameter>
  <Parameter Name="Query TimeOut Available">No</Parameter>
  <Parameter Name="Force Execute">Always</Parameter>
  <Parameter Name="Optimize Execute">True</Parameter>
</Defaults>

Configuration Changes : #

PRM File Changes: #

Changes to the oracle and PostgreSQL PRM files to resolve outer join issues. So, the universes are compatible with SQLServer, Oracle and PostgreSQL databases
PRM File Path:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc

Modification of Oracle PRM File: #

Users need to modify the below highlighted area in the Oracle PRM file.

<Parameter Name="LEFT_OUTER"></Parameter>
<Parameter Name="RIGHT_OUTER"></Parameter>

Modification of PostgreSQL PRM File: #

<Parameter Name="LEFT_OUTER"></Parameter>
<Parameter Name="RIGHT_OUTER"></Parameter>

Setting the Prefix_Object :- #

Prefix_Object is used by all other objects in the universe where currency conversions are carried out. If this object works then can be tested by Parsing the “Test Prefix Object by Parsing”, this object is located immediately above the Prefix_Object. This test object will invoke the Prefix_Object and the Stored procedure.

Example:

Users need to change the definition of the object named Prefix Object according to their data base schema name. At the universe level, prefix_object should define this object as “Schema Name.“If the object is not defined properly, GET_EXCH_RATE_AND_EURO function-related objects will not work.

Known issues and their resolutions #

Java Heap Memory issue:

The following database error occurred: (CS)
"Java Exception : java.lang.RuntimeException: com.sap.connectivity.cs.core.CSError: GC overhead limit exceeded" .
For Information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal.
(IES 10901) (Error: INF WIS 10901)

Solution- #

Changed the Xmx value from 2GB to 4GB as suggested by the SAP and ran the report for single and multiple objects.

Now the report can be run for dimensions without any error, when it comes to adding few measure objects to the dimensions then we are getting an error.

Joins with Postgresql database

Joins in the webi reports pointed to PostgreSQL connection happened reversely when compared with Oracle and SQL connections. This means in SQL and Oracle query LEFT JOIN is converted as RIGHT join in PostgreSQL.

Solution - #

By enabling the ANSI92 parameter at the universe level we can see the joins as expected with the PostgreSQL connection.

With ANSI92 parameters we have added 2 more universe parameters “SELFJOIN_IN_WHERE” and “FILTER_IN_FROM”.