Hybrid SAP Analytics – Connected to “Azure DB”

Author – Asad Mahmood, Principal Solution Architect, NTT DATA Business Solutions UK

I have been speaking to a number of customers over recent weeks who would like to connect to datasets hosted in Microsoft Azure using their existing investments in SAP Analytics. In a majority of cases, these customers have invested in the duo of SAP BusinessObjects and SAP Analytics Cloud; a configuration commonly referred to as the Hybrid SAP Analytics architecture. This is being significantly strengthened with the release of SAP BusinessObjects 4.3 later this year.

Prompted by these questions, I have managed to carve some time out of my diary to investigate this further. In the words of one of my favourite US late night talk show host “let’s take a closer look”…

Firstly, let’s cover off some key terminology:

“Hybrid SAP Analytics” is the combination of SAP Analytics Cloud and SAP BusinessObjects to deliver a rich and comprehensive analytical experience. This is the focus of SAP’s future direction and delivers a number of business benefits. I recommend the following links for additional context.

Read more about SAP Analytics Cloud & BOBJ ; Delivering Hybrid Analytics

Read more about the SAP Analytics Business Intelligence Statement of Direction

Whilst the rest of this blog focuses on this architecture, there will be side notes on associated architectures.

“Azure DB” in itself isn’t a real thing. This can manifest in a number of technical forms in my experience. Typically it refers to:
- Azure SQL Database
- Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse
- And in some extreme cases, it refers to SQL Server being hosted on an Azure Virtual Machines

It’s important to identify which of the above is being considered, as the ensuing steps will differ accordingly.

The datasets hosted in Azure are in some cases combined with SAP datasets and in other instances, analysed independently. The ensuing sections cover both of these scenarios. It’s also worth stressing that certain situations may lend themselves to the inclusion of a Data Warehousing system and this may then become the conduit for combining these disparate datasets and making them available to the reporting clients. This would need to be assessed and determined in each individual situation.

Secondly, I also wanted to take this opportunity to step through the activities I undertook in Azure for the purposes of this investigation as this may help diagnose issues or help with starting any new SAP analytics projects with datasets hosted in Azure. Please excuse some of the shaded fields. This is disguising my personal email address and IP addresses. For anyone relishing the challenge to hack my Azure tenant, I will disappoint you by sharing that the tenant has now been suspended…

Once I had access to my Azure Portal, I was ready to provision the requisite services. The creation of an Azure SQL DB was relatively straightforward. I opted for the default options in most instances.

The Azure Synapse Analytics DB was also created with relative ease. This led to brief frenzy of trigger-happiness, which culminated in the creation of several databases and a simultaneous failure of imagination when it came to the naming of my databases!

With some of these databases, I opted to include sample datasets and with others, I relied on my recently discovered creative flair to create new tables and datasets!

There is an online editor available for running DDL and DML commands using Transact SQL. An elaborate example of one such query is provided below. Every developers favourite: “SELECT *”…

This editor can be used for all such queries and procedures but for familiarity, I opted for SQL Server Management Studio SSMS. The download for this fat client application is available from the Azure Portal but I should add that it was several hours before I was ready to query and program my database applications as seen below. I dispensed with the “SELECT *” for this example but continued with my proliferation of “Test” tables…

Returning to my hours of frustration, the server name and username for Azure were expected in a certain form and after much digging, I stumbled across the database connection strings in the Azure Portal.

This provided me with the fully qualified server, username and Catalog name. This was the format expected by SSMS and subsequently the ODBC Connections. More on the latter shortly.

The next setback arrived in the form of firewall errors. This required firewall exceptions to permit connections from my remote IP’s. Note that this was the external IP for my office along with my home network. I added these exceptions into the Azure Portal. This would be the SAP BusinessObjects system in most cases.

azure

Once this was in place, I was operational with SSMS.

I then proceeded with a 64-Bit ODBC Connection, which I subsequently consumed in the new 64 Bit Information Design Tool for SAP BusinessObjects 4.3 (Beta). At first, I was experiencing peculiar behaviour when selecting the default “SQL Server” (shipped with Windows OS) driver as it would only provide access to the Master DB but this was resolved by using the “SQL Server Native Client 11” (a more recent version would be advisable for production implementations). This provided me with access to the various forms of “Test DB’s” I created in Azure, as seen in the earlier screenshot.

Importantly, I was unable to create an ODBC connection to the Azure Synapse Analytics DB and it was apparent from IDT, that this required a JDBC driver. As seen from the image inserted below, Azure SQL Data Warehouse (official name is Azure Synapse Analytics DB) requires a JDBC connection whereas Azure SQL DB’s (featuring under MS SQL Server 2017) can use JDBC or ODBC.

The Java Driver (as well as the latest ODBC) can be downloaded from the Azure Portal. Once this has been deployed into SAP BusinessObjects, a connection to Azure Synapse Analytics DB can be established. I recommend this post authored by my colleague Mike Quinn for details on the driver deployment process.

You are then ready to create the Business Layer and use this within Webi or SAP Analytics Cloud.

SAP Analytics Cloud supports Universes as a Live connection. This retains the data at source and returns resultsets to the user’s browser in an encrypted form. There is also the option to create an SAP Analytics Cloud connection to a SQL Server system via JDBC. Whilst I have not tested this, I would expect this to provide a further option for connecting to Azure SQL DB’s but this would entail a data import.

In summary, Hybrid SAP Analytics supports datasets hosted in Azure. It’s important to identify how these datasets are provisioned in Azure as there are multiple options to establish connectivity. Depending on the specific format, you can create JDBC/ODBC connections, which can subsequently be consumed in the SAP Information Design Tool for the creation of Universes. Finally, the Universes can be leveraged by Webi and SAP Analytics Cloud.

Please feel free to contact us if there are any questions.