Data Federation Between SAP Data Warehouse Cloud ( DWC ) and Google BigQuery
Explosion of data is enabling enterprises to get new insights into their business. But it also brings in challenges like data fragmentation. Cloud had enabled LoBs to decide on the data storage of choice which increases the data fragmentation. SAP Data Warehouse Cloud helps in bridging the silos of information across multiple cloud providers.
Start Data federation from Google BigQuery through SAP Data warehouse Cloud mission for free in SAP Discovery Center for step by step guide to setup with your account.
This blog post will focus on integrating Google Big Query with SAP Data warehouse cloud.
1. Loading Data to BigQuery
Google Big query is the big data service which is part of Google Cloud Platform. Get started by creating a new dataset “sample_dataset” in Google BigQuery. After successfully creating it, click create a table.
In the create table page enter table name, select the sample data csv file and click Create Table.
BigQuery will automatically generate the table columns based on the data type.
Step 2. Creating Service Accounts in GCP
SAP Data Warehouse cloud needs service account with necessary authorizations to access the data. In your Google Cloud Account, go to IAM -> Service Account -> Click Create Service Account
Enter Account name and click Create.
In the next step, add BigQuery User and BigQuery Data Viewer roles to service account. Click Done.
After the account is created, Add private key and download it.
Step 3. Installing Certificate
Open your browser and go to google.com. Open the certificate and drag the certificate to desktop.
Import the certificate to Keychain Access and export it “.pem” format.
Open the SAP Data Warehouse account.
Click Administration Menu , go to Security and click Add Certificate.
Upload the .pem certificate that was downloaded in the previous step and “Click Upload”
Step 4. Creating Google BigQuery Connection
Click Space Management Menu and select the space you want the BigQuery connection to be created. Scroll down to connection section and Click “Add Connection”.
Enter the GCP project and upload the Key created in step 2. Now click “Create Connection”
Step 5. Creating Remote table
Open Data Builder and click create new SQL View.
Click Sources and Open Connection.
Open Google BigQuery Connection , Project , DataSet and select the BigQuery table.
Drag the table to SQL editor.
Add the columns in SQL.
Save and Deploy the view.
Once the view is deployed, Data Warehouse Cloud automatically creates Relation Table with remote connection.
This is a virtual representation of the BigQuey table. SQL statements fired on this table will be delegated to BigQuery and results will be rendered in Data Warehouse Cloud. Data is not stored in Data Warehouse Cloud for this table.
Open Data Builder and create Analytical Dataset. Drag the remote table created in the above step and add measures and dimensions.
Save and deploy the Analytical Dataset.
Step 6 : Test the Analytical Dataset
Open SAP Analytics Cloud and create the story. Use Analytical dataset created in the above step as source and build charts. Slice and dice the data like you would with the regular table. Data Warehouse cloud delegates the query to BigQuery and display the result in the chart
Now enterprise can visualize data from Google BigQuery in SAP Analytics Cloud through SAP Data Warehouse Cloud through data federation.