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.

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”

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

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

TAXITRANSACTION – Calculation View created for the table “Taxi_Transaction”

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 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:

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:

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

CONNECTING TO CALCULATION VIEW:

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

CONNECTING TO CALCULATION VIEW:

CHOOSE A SINK DATASET AND STORE THE DATA IN AZURE:

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

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:

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