Connecting SAP Analytics to Google BigQuery

What is Google BigQuery (GBQ)?

GBQ is a low-cost, highly-scalable cloud warehousing and analytics suite built on the company’s cloud platform, designed to harness cloud power to process data orders of magnitude larger than on-premise boxes can manage – we’re talking petabytes.

Whilst GBQ offers processing superpowers and analytical capabilities, it is not yet ready to replace the traditional vendors’ analytics and BI offerings. Indeed, Google is yet to be included in Gartner’s Magic Quadrant for BI. SAP, however, is a visionary and its core BI systems – SAP Analytics Cloud (SAC) and SAP BusinessObjects (BI) – are still a world-class choice for enterprise BI. In this blog, I will go through the tasks of connecting your GBQ system to both of SAP’s, displaying both the ease with which it can be done and providing food for thought on how the capabilities of both systems can be combined into a great warehousing and analytics landscape.

A Brief Technical Introduction to GBQ

In order to analyse your data in SAP’s BI tools, you need some data in a GBQ system. If you do not have one in your enterprise, you can get a year’s free trial from Google here. We’re not going to go into too much detail on GBQ itself, but Google’s full documentation is here.

The first thing you’ll need to do is to create a Project. Projects in GBQ are singular, standalone developments and contain the data, queries, APIs etc. for the task at hand. Multiple Projects can be created for different warehousing and reporting purposes. The general hierarchy looks like this:

Within a project, you can import one of Google’s public datasets or upload your own into custom tables, which can then be queried using the Query UI and several SQL dialects.

Once your tables are set up, they can be brought into SAP’s tools. HANA and its brethren can also connect to GBQ, but here we will focus on analytics.

SAP Analytics Cloud (SAC)

Let’s start with the easier one, SAC. Being a modern, cloud-based analytics suite, SAC comes with a built-in connector to BigQuery, to acquire data into the SAC tenant. It does not yet offer live connectivity, so data will be imported into SAC. Before you embark upon creating a connection, ensure that popups are enabled in your Google Chrome browser, which is recommended for both SAC and GBQ. In the Connections screen, select a new connection and find Google BigQuery in the popup:

This will bring up the classic Google login screen, where you can select your credentials and use Google’s built-in login API to connect. No configuration is required.

And once you have chosen your credentials, you will be able to name your connection and select a specific Project to which to connect. One connection connects to one GBQ project.

From here you can either create a Model to further enhance and analyse your data or insert your GBQ data directly into a story. Select your connection and you will be shown the query screens, where you can either select objects using the GUI or write a freehand SQL query.

BusinessObjects

Connecting to BusinessObjects has more moving parts, but it still relatively straightforward. As with all non-SAP datasources, you will be required to build a Universe in Information Design Tool, so make sure that you have the relevant Client Tools for your BusinessObjects setup installed.

Before we dive into the technical detail, a note on versions. The current mainstream version of BusinessObjects is BI4.2 and although you can connect the two – we will go through it shortly – it is officially unsupported by SAP. It can be used with this caveat in mind, but SAP will not provide technical support. The SAP note on this can be found here. The brand-new version of BusinessObjects – BI4.3 – does support a connection to GBQ and has a native connector built into Information Design Tool.

If connecting to BI4.2, you will need to download and install the Magnitude Simba ODBC driver on your server, which is found here. Once installed, configure the driver in the Windows System DNS window, where you will need to sign in to Google and select your project. You may need a system administrator for this.

The final piece of server-side tweaking involves changing parameters in two data access configuration files, both named odbc.prm but in different locations. Remember to back both up before editing!

• In the folder BI40 Installation Folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc change the following line:
  • NO
    • NO should be YES

• In the folder BI40 Installation Folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc\extensions\legacy add the following files before the line:
  • <Parameter Name=”IDENTIFIER_DELIMITER”>&quot;</Parameter>
  • <Parameter Name=”DELIMIT_LOWERCASE”>Y</Parameter>
  • <Parameter Name=”DELIMIT_IDENTIFIERS”>Y</Parameter>

Once these changes are made, you can build a UNX universe on top of your GBQ data in same way as usual, using a Relational connection with a Generic ODCB driver. This can then be ingested by any of the BI tools. For more information on connecting to BI4.2, consult this SAP whitepaper.

Conclusion

It took me under half a day to set up a GBQ project, upload some dummy data, model it, connect it to both systems and show it off to colleagues, so it would take no time at all to run a quick proof-of-concept in your business. With SAC’s ease of use and the new native connectivity in BI4.3, Google to SAP Analytics could become a new major analytics partnership. We can help you to harness the power of both.

Author

Chris Bradshaw

Senior consultant in SAP BI

Chris Bradshaw is a senior consultant in SAP BI in the UK I&D team. A graduate of the University of Oxford, he has 10 years’ experience providing analytic insight to clients in a range of industries.