Dealing with a customer who is hesitant to provide access to their HANA tables?

Well, request the necessary data in the HANA system as Views, because the data within a View would be a subset of the larger table and it would be straight up easier for any referencing tool to identify the attributes and measures needed, especially when trying to back it up on Cloud for upcoming analysis. Views also can have calculated measures which again reduce any extra effort to transform the data to desired format in the future. This standalone blog helps you to connect Azure to the views and bring in the data in the View seamlessly for Analysis/Backup.

We need to first understand that the connectivity to HANA DB is established through “Linked Services” available in Azure Data Factory, which acts as a bridge between Azure Cloud and SAP HANA. We will get back to “Linked Services” later in the article. For now, let us prepare the SAP end, to get the data in the right format for Azure pipeline to access it.

Below is a snapshot of the tables that we currently have in SAP HANA Database.

Connecting to VIEWS in SAP HANA from AZURE CLOUD
 
As mentioned above, we need the HANA views in place to prepare the data from the tables for our migration. SAP HANA has three different types of Views, namely “Attribute View”, “Analytic View” and “Calculation View”.

Different Type of Views in SAP HANA:

A brief introduction to these views are as follows,

Attribute View: All the columns from a table or joins of tables are represented as Attributes. This view can never include a “Measure” in it.

Analytic View: Comprises of both “Attributes” and “Measures”. A strong component if analysis of current Attributes and measure amongst the HANA tables is our priority.

Calculation View: Strongest and most versatile View if user plans on analyzing the data with newly defined aggregations and custom calculations. Supports even if all pieces are only of type “Attribute”

Limitations on Azure Connectivity:

  1. Attribute Views cannot be connected through Azure Linked Services
  2. Analytic Views can be connected to, but are supposed to be aggregated using MDX commands to obtain the data
  3. Calculation Views are the only type of view which can be connected and can be accessed at a direct 1:1 row mapping when pulling the data into Azure.

CREATION OF VIEWS in SAP HANA:

TAXIZONE – “Attribute View” with all the columns from the table “Taxi_ZoneLookup”

Connecting to VIEWS in SAP HANA from AZURE CLOUD

 

TAXI_ZONEDIMENSION – “Analytic View” with all columns from table “Taxi_Transaction”

Connecting to VIEWS in SAP HANA from AZURE CLOUD
 

DIMZONE – “Calculation View” created for the table “Taxi_ZoneLookup”

Connecting to VIEWS in SAP HANA from AZURE CLOUD

 

TAXITRANSACTION – Calculation View created for the table “Taxi_Transaction”

Connecting to VIEWS in SAP HANA from AZURE CLOUD

 

Our work on the View creation based on the SAP data is completed and below we can see all the available views under the respective schema.

Connecting to VIEWS in SAP HANA from AZURE CLOUD

 

CONNECTING TO SAP FROM AZURE:

Earlier we mentioned about “Linked Services” in Azure which helps connect to other outer world data sources. One other component which assists “Linked Services” in these connections is “Integration Runtime”, which acts as the Gateway Authorization of the source system and provides access to Azure.

ACCESSING THE HANA VIEWS FROM AZURE:

Connecting to VIEWS in SAP HANA from AZURE CLOUD

 

As expected none of our “Attribute Views” are seen from Azure. This brings us to a conclusion that only Analytical or Calculation Views must be accessed from Azure.

CONNECTING TO ANALYTIC VIEW:

Connecting to VIEWS in SAP HANA from AZURE CLOUD

 

The Query to connect to the above view is written automatically in our Query Editor box and looks something like the below:

Connecting to VIEWS in SAP HANA from AZURE CLOUD

 

CONNECTING TO CALCULATION VIEW:

Connecting to VIEWS in SAP HANA from AZURE CLOUDConnecting to VIEWS in SAP HANA from AZURE CLOUD

 

Selecting the Attributes auto-created the query and now we can preview the data right away as shown below:

Connecting to VIEWS in SAP HANA from AZURE CLOUD

 

CHOOSE A SINK DATASET AND STORE THE DATA IN AZURE:

Connecting to VIEWS in SAP HANA from AZURE CLOUD

 

In our example, we use the “Azure Data Lake Store” as “Sink Dataset” using the respective “Linked Services” and get the data stored in a “.txt”,“.csv/.tsv” or any other format listed.

Below is a snapshot of the data migrated to “Azure Data Lake Store” in a .tsv format

Connecting to VIEWS in SAP HANA from AZURE CLOUD
 

BENEFICIAL FOR:

Once connected to SAP HANA system, the Views (Analytic and Calculation Views) created within HANA are available in AZURE’s “Browse SAP” pane, which then can be expanded and selected at Attribute/Measure Level. For someone who requires aggregate of the measure for analysis, they could define Measures in Analytic/Calculation View, but for someone who needs an exact copy of the source table content inside can bring all columns as Attributes inside a Calculation view and import it without any aggregations in Azure.

 

Reference: Data used for building this tutorial comes from a public dataset available at the below site:

http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml

Subscribe to our Newsletter

5600 Tennyson Pkwy
Suite 120
Plano TX 75024.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com