Virtualized EDW on HANA 1 SP12 – A few stumbling blocks…

[DotEiserman]

Note: This blog is the recreation of an old blog on an old profile as part of the process of pulling all my content together on a consolidated profile.

In this blog I thought I would take a few moments to describe some of the stumbling blocks we have encountered in getting a virtual EDW up and running. Our landscape involves a virtual EDW (HANA Enterprise) connecting to various sources including S/4 HANA and SAP CRM (on HANA DB), basically a federated HANA environment.

The cornerstone of our solution is the virtual table object which we are using to expose data from the underlying systems into our HANA EDW. A virtual table is an object in HANA which points to objects in remote systems for e.g. tables and views. In our scenario we have used SDA (Smart Data Access) remote source connectors to access the underlying systems.

We encountered the following four stumbling blocks and I will discuss each one separately:
1. It is not possible to create a virtual table pointing to a CDS View with parameters
2. It is not possible to create a virtual table pointing to a calculation view with parameters
3. It is not simple to promote and track changes on a virtual table (on HANA 1.0 SP12)
4. It is not possible to refresh the definition of a virtual table if the underlying table structure has been modified (on HANA 1.0 SP12)

Virtual Tables and CDS Views

On the latest S/4 HANA release, HANA Live content (delivered as Calculation Views in HANA) has been replaced with new S/4 HANA Embedded Analytics by means of ABAP Managed CDS View content.

Here is a nice blog on the topic:

https://blogs.sap.com/2016/09/26/core-data-services-cds-in-sap-s4-hana/

We started off by connecting our HANA Virtual EDW to S/4 HANA and creating virtual tables to connect to the SAP standard delivered CDS views. Our initial thoughts were that this would be an easy task as each DDL source has a SQL view defined in its definition and we could simple connect the virtual table to this DDL SQL view. It turned out after a lot of investigation and a call logged with SAP that this is not the case and that our assumption was flawed as not all CDS view generate a simple DDL SQL view that can be consumed.

In summary:

1. A CDS view without parameters will generate a view type object which can be consumed by a virtual table


2. A CDS view with parameters will generate a table function type object which cannot be consumed by a virtual table

This was a real pity for us as the main Trial Balance CDS view had parameters and could not be consumed.

Virtual Tables and Calculation Views

On connecting to SAP CRM (on HANA DB) our journey took a different path as the content is still HANA Live (delivered as calculation views) and has not yet been transformed into ABAP managed CDS Views. We thought this path would be much simpler and started creating virtual tables pointing the CRM Live calculation views that were relevant for our scenario.

We yet again hit a stumbling block with parameters and after a lot investigation and yet another call logged with SAP they confirmed that the feature is not currently available and that it will be delivered in the future but with no concrete delivery date.

In summary:

1. A calculation view without parameters can be a source for a virtual table and can be consumed for e.g. BusinessPartner from HANA Live content


2. A calculation view with parameters cannot be a source for a virtual table for e.g. ServiceRequest from HANA Live content

Promotion Management and Change Tracking on Virtual Tables

As virtual tables are the cornerstone of our solution we wanted them to form part of the HALM (HANA Application Lifecycle Management) process and would also have like to track changes on them. We investigated and could not find an easy way to incorporate them into a change list with the rest of the repository objects and logged an incident with SAP for guidance. SAP responded very quickly to say that we could use .hdbvirtual table and .hdbvirtualtableconfig objects which form part of HANA XS. They also provided the following link:

https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/1.0.12/en-US/08191144d5804c86af9395b110...

We have not yet converted to using these new object types and it will be our mission for the next week to try them out and see if they integrate seamlessly into HALM.

Refreshing the definition of a Virtual Table

As virtual tables point to objects on a remote system it is likely that the definition of the object on the remote system could change, for e.g. the addition of a column. This is exactly what happened on our project and when we went back to the virtual table and tried to view the contents it gave errors. We searched for a way to refresh virtual table and came across the following command:

ALTER VIRTUAL TABLE myvirtualtestable REFRESH DEFINITION

The SQL console did not recognise it and we logged an incident with SAP who confirmed that there is no ALTER VIRTUAL TABLE command in HANA 1.0.

They provided us with the following workaround:

Solution/Workaround:

1. Add the column you need to the source table


2. Create a new virtual table based on this source table


3. Follow the Steps “Replace a Data Source in Calculation Views” to modify your Calculation View. https://help.sap.com/http.svc/rc/fb8f7a9f7860468b84a07eab0a7d0a98/2.0.01/en-US/SAP_HANA_Modeling_Gui...

I hope this blog will save other developers some time when hitting the same stumbling blocks, I would recommend starting off with a HANA 2.0 installation as SAP are adding new features continuously.