Connecting to Google BigQuery from Information Designer Tool (IDT)
Information Design Tool (IDT) is a Business Objects design tool that extracts the data from different data sources using an OLAP and Relational connection to create Universes.
To Create the Google BigQuery connection in IDT:
The Google Cloud allows instead of using a server name, user, and password, Google allows serialized connection information into a JSON key file. The client libraries can rely on the information contained in the file to connect to the Google cloud and the relevant services.
The drivers you need:
api-common-1.7.0.jar
auto-value-1.4.jar
commons-codec-1.12.jar
error_prone_annotations-2.1.3.jar
failureaccess-1.0.1.jar
gax-1.37.0.jar
gax-httpjson-0.54.0.jar
google-api-client-1.27.0.jar
google-api-services-bigquery-v2-rev20181104–1.27.0.jar
google-auth-library-credentials-0.12.0.jar
google-auth-library-oauth2-http-0.12.0.jar
google-cloud-bigquery-1.82.0.jar
google-cloud-core-1.61.0.jar
google-cloud-core-http-1.61.0.jar
google-http-client-1.27.0.jar
google-http-client-appengine-1.27.0.jar
google-http-client-jackson2–1.27.0.jar
google-oauth-client-1.27.0.jar
gson-2.7.jar
guava-26.0-android.jar
j2objc-annotations-1.1.jar
jackson-core-2.2.3.jar
jsr305–2.0.1.jar
jsr305–3.0.2.jar
protobuf-java-3.6.1.jar
protobuf-java-util-3.6.1.jar
proto-google-common-protos-1.12.0.jar
proto-google-iam-v1–0.12.0.jar
threetenbp-0.7.2.jar
1. Get the drivers listed from link: https://help.sap.com/viewer/aa4cb9ab429349e49678e146f05d7341/4.3/en-US/698763ed80ad4c588849764260d390ce.html
2. In order for the BigQuery driver to work, create a google folder in the <Install Dir>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\sharedLibraries folder, then drop the google jars in that folder you just created.
3. Restart the BOE servers.
4. Launch IDT and create the new Relational Connection selecting Google BigQuery ->BusinessObjects OpenConnectivity as the middleware.
5. Pass the JSON key in Filepath or Pattern, click on TestConnection.
Refer the link to create the JSON key: https://cloud.google.com/iam/docs/creating-managing-service-account-keys
6. On TestConnection success publish this connection to the Repository to make it available for use
7. The next step is to create a Data Foundation Layer on this secure connection. Select the .cns connection right click and create the Data foundation. It will show list of all available schemas in the database. You can add Tables and Joins from Schema to Data Foundation layer
8. Next is to create a Business Layer on the Data Foundation. Click the Save All icon at the top of the screen. Then, right-click on Data foundation .dfx → New Business Layer. Enter Resource Name → (Generating Business Layer for Data Foundation) Finish. It will add Business Layer .blx under the Local Project.
9. Once you define Business Layer, click Save All icon and publish it to repository
10. Using this universe WebI documents can be created