SAP Cloud Integration for data services – Google BigQuery Integration with SAP IBP
SAP Cloud Integration for data services now supports Google BigQuery as source to integrate data with SAP Integrated business planning (SAP IBP) planning areas. In this blog, I will show how to create Google Big Query connection in SAP Cloud integration for data services and discuss some common usecases for this integration .
For information about Google Big Query processes and policies, account and fee information, and documentation including Quick Start guides, go to the Google BigQuery website
If you plan to use a Google BigQuery datastore as a source, the target must be an SAP Integrated Business Planning (IBP) WebSocketRFC datastore. To take advantage of Google BigQuery support in SAP Cloud Integration for data services, we strongly recommend that you migrate your SAP IBP connection type to WebSocket RFC if you have not done so already.
For more information on switching from JDBC to WebSocket RFC connection, refer to SAP IBP help documentation: Switching from JDBC to WebSocket RFC Connection
Google BigQuery Datastore
To work with Google BigQuery in SAP Cloud Integration for data services, you must have a Google account and create a project with datasets.
You will need to upgrade to SAP Cloud Integration for data services Agent version 1.0.11 patch 2306 or later to use Google BigQuery Datastore.
Download and Configure ODBC driver
To connect to Google BigQuery account , you will need to download and install Magnitude Simba driver for Big Query. The driver contains mechanisms that enable you to use an OAuth 2.0 authentication with either a Google user account or a Google service account. The Google BigQuery ODBC datastore supports both DSN and DSN-less connections .
For more information, see Download and install the Simba ODBC driver.
Configuring DSN on Windows
- Click the windows start icon, search and open the ODBC Data source Administrator (64 bit)
- Open either the User DSN or System DSN tab as applicable and click Add.
- Select Simba ODBC Driver for Google BigQuery and click on Finish
- Enter a unique name in Data Source Name and optionally enter text for Description.
- Set Encrypt Sensitive data for All Users
- Select Service Authentication from the OAuth Mechanism dropdown list.
Service Authentication or User Authentication are supported authentication mechanism.The type of OAuth mechanism you select determines the authentication options to complete.
- Obtain service account email id from your Google account and enter for Email.
- Generate a Google service account private key from your Google account and save the file locally as either a JSON or P12 file type.You can obtain this account email id and key information from Google Cloud console → IAM & Admin → Service Accounts
- Enter the path and filename of the saved key file
- Select the version number from the Minimum TLS Version dropdown list. Google BigQuery requires TLS. SAP Cloud Integration for data services supports only TLS version 1.2.
- Specify the Trust Store CA certificate file to use.
- To use the Windows Trust Store for the CA Certificates, select Use System Trust Store.
- To use the .pemfile that is installed with the Simba ODBC driver for Google BigQuery, accept the default address in Trusted Certificates.
- To use your system Trust Store, enter the full path to the trusted certificates .pemfile on your system.
- Select the applicable Google BigQuery project name from the Catalog (Project)dropdown list.
- Select the data set from the Dataset dropdown list.
- Click Test to test the connectivity
- Click OK after the DSN tests successfully.
Create a GBQ datastore using DSN or DSN-less
- Login into SAP Cloud Integration into data services UI
- Add datastore by click on +
- Enter Datastore name and description.
- Select type as Google Big Query
- Select the Agent installed and configured.
- Database type is defaulted to Google Big Query ODBC
- Select Yes to use Data Source (ODBC) if you prefer to use DSN
- Select NO to use Data Source (ODBC) if you prefer to use DSN-less connection.
- Choose Service Authentication for OAuth Mechanism.
- For Email , enter your service account email
- For Key File Path, Choose the downloaded Key file for Google service account.
- For Catalog, Enter the Google Big Query Catalog
- Save and test the connection.
These are some common uses cases for Google Big Query integration with SAP IBP with SAP Cloud Integration for data services.
- Extract Master data from Google Big Query for transformation and integration of Master data existing outside of SAP IBP.
- Extraction of Master and Transactional data for 3rd party Enterprise Execution solutions existing and hosted on Google Cloud Platform in Google Big Query.
- Integration of Sales Opportunities, sales orders, quotations, Sales demand from Google CRM using Google Big Query into SAP IBP.
For demo on Google Big Query integration with SAP IBP using SAP Cloud Integration for data services, view below webcast recording available at IBP Engagement Hub
For more information on Google BigQuery Datastore options in SAP Cloud Integration for data services, refer to help page at Google BigQuery