How to set up SAP BusinessObjects with SNOWFLAKE using JDBC

[abhinav_shrivastava1]

This blog is to share my experience setting up the connection of Snowflake from SAP BO

Software/Applications:

Environment Information: SAP BO 4.2 SP03
BO Server Operating System: RHEL 6.x
Database: Snowflake
Keystore Manager: (used portecle-1.11 from windows for BO Client tool, eg: IDT)
Connectivity Type: JDBC
JAR Version: snowflake-jdbc-3.6.0.jar (download Snowflake JDBC)

Audience: SAP BO Admin, SAP BO Developer, SAP BO Consultants

Snowflake?
Snowflake is a Cloud Data Warehouse, which runs completely on public cloud infrastructure. It uses virtual compute instances for its compute needs and a storage service for persistent storage of data. Snowflake cannot be run on private cloud infrastructures (on-premises or hosted)

Connectivity Pre-requisites:

1. Domain in which BO server is hosted, should have proper handshake with the Snowflake regional server.
   Refer Snowflake connectivity document:
   https://docs.snowflake.net/manuals/user-guide/ecosystem-diagnose-connectivity-issues.html

In our use case, we are using US East region. Check below for more information:
https://docs.snowflake.net/manuals/user-guide/intro-regions.html

2. Import the snowflake cert in the “cacert” file located on BO server and client using KeyTool and portecle-1.11 respectively.
   cacert location on Linux server:
   <INSTALL_DIR>\enterprise_xi40\lin_x64\sapjvm\jre\lib\security\cacert location for windows client:
   <INSTALL_DIR>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\jre\lib\security\


3. After this, create a folder with name “snowflake” under below location and place “snowflake-jdbc-3.6.0.jar” into it:BO server:
   <INSTALL_DIR>\enterprise_xi40\dataAccess\connectionServer\jdbc\drivers\<snowflake> Client Location:
   <INSTALL_DIR>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\snowflake


4. Update the JDBC.sbo present on server and client using below tag:

BO Server:

<DataBase Active="Yes" Name="Snowflake JDBC Datasource">



<JDBCDriver>



<!--  Uncomment and edit the following lines to define java classes required by JDBC driver



&lt;ClassPath&gt;&lt;Path&gt;your jar or class files directory&lt;/Path&gt;  &lt;/ClassPath&gt;



            -->



<ClassPath>



<Path><INSTALL_DIR>\enterprise_xi40\dataAccess\connectionServer\jdbc\drivers\snowflake\snowflake-jdbc-3.6.0.jar </Path>



</ClassPath>



<Parameter Name="JDBC Class">$JDBCCLASS$</Parameter>



<Parameter Name="URL Format">$DATASOURCE$</Parameter>



</JDBCDriver>



<Parameter Name="Array Fetch Size">10</Parameter>



</DataBase>

Client Machine:

<DataBase Active="Yes" Name="Snowflake JDBC Datasource">



<JDBCDriver>



<!--  Uncomment and edit the following lines



to define java classes required by JDBC driver



&lt;ClassPath&gt;



&lt;Path&gt;your jar or class files directory&lt;/Path&gt;



&lt;/ClassPath&gt;



-->



<ClassPath>



<Path><INSTALL_PATH>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\snowflake\snowflake-jdbc-3.6.0.jar </Path>



</ClassPath>



<Parameter Name="JDBC Class">$JDBCCLASS$</Parameter>



<Parameter Name="URL Format">$DATASOURCE$</Parameter>



</JDBCDriver>



<Parameter Name="Array Fetch Size">10</Parameter>



</DataBase>

 

Steps to create Connection:

1. 
   
   
   
   1. Login into IDT.
   
   
   2. Under Repository Resource, right click on Connection folder and select “Insert Relational Connection”
   
   
   3. After giving the Connection Name, click on Next.
   
   
   4. Expand Generic and you should see a Driver “Snowflake JDBC datasource”
      
   
   
   5. Give the service account and password of Snowflake followed by JDBC URL and JDBC Class and test the connection:
      
   
   
   
   

   User Name : <user_name>

   Password : <password>

   JDBC URL: jdbc:snowflake://<company>.<snowflake_region>.snowflakecomputing.com:443

   JDBC Class: net.snowflake.client.jdbc.SnowflakeDriver

Additional observation:
- Before executing any SQL on Snowflake interface, we have to define the Virtual Warehouse against which the query execution should take place. Hence used BEGIN_SQL parameter in the Business layer (while universe creation from IDT) to define the Virtual Wareshouse from business objects.

 

Please share your experience. I will keep updating/correcting this blog as per my new observations.

 

Thanks,
Abhinav Shrivastava