Replication of calculation views from on-premise HANA Platform to HANA Cloud

[Taesuk]

This blog explains replication from SAP HANA Platform on-premises to SAP HANA Cloud only. If your interest is more about general replication with various SAP solutions and external data, please check out the Data Intelligence Cloud as part of our Data Manatement Portfolio

In my previous blog Replication Service to easily setup replication from SAP HANA on-premise to SAP HANA Cloud, I’ve shown how tables can be easily replicated from an SAP HANA Platform on-premises to SAP HANA Cloud using Replication Service.

Since then we now support

• HANA native replication using remote table replication.
  If the source SAP HANA Platform is 2.0 SPS05 or higher, native RTR is used for replicating data. The details of remote table replication is described in A new approach for replicating tables across different SAP HANA systems by my colleague Lee, Seungjoon and a step by step tutorial.


• Replicate calculation views with a few simple clicks.
  When a calculation view is selected for replication, the runtime metadata is extracted from the source system and parsed to get the definition of the calculation view and retrives all dependent objects. This can be other nested calculation views, hierarchy information and tables. For nested calculation views, the metadata is again extracted and parsed until all dependent objects have been identified as tables. The tables will be replicated in real-time or loaded once based on the schedule settings. The calculation view runtime objects will be built from bottom up until the final selected calculation view is built on the target side.

One point that needs to be emphasized is that only the runtime objects of the calculation view are being replicated and not the design time. The runtime is the column view that are accesible using SQL while design time is the artifact stored in the repository (XSC) or github (XSA). So, any design changes that is needed for the calculation view needs to be done at the source side which is on-premises and replicated again after the changes. For details of how to update an existing calculation view please check out the details in SAP Note 3133975.

If the replicated calculation view needs to be extended to create another calculation view in SAP HANA Cloud, the replicated calculation view can be referenced as column view consumed as a synonym inside a HDI container.

The benefits of replicating a calculation view runtime is

• Easy to extend an existing analytical workload running on-premises to the cloud with a few simple clicks


• Offload workload dynamically to any region running SAP HANA Cloud on demand


• Replicate to multiple regions extending global reach


• Expand existing workloads with latest features available with every SAP HANA Cloud enhancement releases

Replication Service

 

Now lets go over the steps on how we can replicate a calculation view.

 

Pre-requisites for technical user for source system

• A technical user for running replication setup. This user should not be the owner of the schema being replicated but a technical user without being an owner.


• For replicating calculation views, runtime metadata is extracted from the source system.
  
  
  
  •  SYSTEM user is mandatory for SAP HANA 2.0 SPS05 Rev59 or lower
  
  
  • Apply the workaround mentioned in SAP Note 3133975. to use a technical user other than SYSTEM for higher than SAP HANA 2.0 SPS05 Rev59
  
  
  
  


• CATALOG READ


• SELECT privilge
  
  
  
  • SCHEMA (XSC based tables and calculation views)
  
  
  • HDI Container Schema (XSA based tables and calculation views)
  
  
  • Objects to be replicated
  
  
  
  

Steps to replicate a calculation view

1. 
   
   
   
   1. Logon to SAP HANA Cockpit and goto Replication Service card found under Monitoring and select Create Replication
      
   
   
   2. For creating a New Connection, there are 2 options to select from SAP HANA 2.0 SPS05 or SPS04 or lower. The difference between the 2 is SPS05 or higher uses the native data replication called remote table replication while earlier version requires SDI to be used. I will use the SPS05 or higher
   
   
   3. Create a remote source using SAP with Cloud Connector selected. The Location ID is optional based on the cloud connector. This is normally set when multiple cloud connector is used and each defined with a different location id. Using the cloud connector allows SAP HANA Cloud connect to the SAP HANA Platform on-premises using SDA connection which is mandatory for remote table replication.
   
   
   4. Once the remote source is created, SAP HANA Cloud connects to the HANA on-premises through the cloud connector and gets the list of schemas or HDI containers that the technical user have been granted in the pre-requisites. I have created SLPADM for schema based calculation views and SALES for HDI based.
   
   
   5. First, lets step through how a schema (XSC) based calculation view can be replicated by selecting SLPADM. As the password is secured for SLPADM user and cannot be extracted, we need to initialize the password to be used in SAP HANA Cloud.
   
   
   6. Once the target schema is created in SAP HANA Cloud, it will retrieve all the objects of the selected schema. The following is a list of calculation views that can be replicated and will select the top most calculation view Sales/CALCVIEW_SALESORDERS as others are nested CVs which gets automatically replicated as it is a dependent object of the selected CV.
   
   
   7. It will now connect to SAP HANA Platform on-premises and retrieve the metadata of the calculation view by importing the BIMC data
   
   
   8. Then retrieves metadata of all dependent objects
   
   
   9. After the initial replication is done, the hierarchy of the dependent objects can be checked with the hierarchy icon
   
   
   10. The dependency objects are all listed which includes tables, hierarchy and other nested calculation views.
   
   
   11. Once the replication is running the status can be check from the main service card grouped for each remote source
   
   
   
   

We will now try to add a new calculation view replication based on HDI container schema by on selecting SALES in step 4 above.

1. The selected HDI container schema SALES will be created as a normal schema in SAP HANA Cloud and not a HDI container. So, the password needs to be initialized as well.


2. Once the target schema is created, the list of objects in the HDI container is extracted. The object name is prefixed “Sales::” whereas it was “Sales/” for normal schema based calculation views


3. The calculation view dependent objects in HDI container is replicated. (Currently, we have found an issue when the dependent object is a synonym which will be resolved soon)

 

We have gone through how we can replicate a calculation view from SAP HANA Platform on-premises to SAP HANA Cloud with just a few simple clicks. The calculation view replication works for both XSC and HDI based without any migration efforts. For 2022, we are planning to close the gaps by supporting replication of Analytic Privilege and how to support calculation views referencing table functions.

We will also publish a detailed easy to follow step by step tutorial extending the existing table replication tutorial.

The official documentation for Replication Service can be found here.

There are some limitations which we currently have as mentioned in SAP Note 3133975 . We will update this SAP Note as we resolve the limitations.

[Cocquerel]

taesuk.son

I understand that there is a prerequisite that is not explicitly mentioned to create SLPADM and SALES users on HANA Cloud side. Is it correct ? What privileged do they require ? Why only those 2 users are appearing in step iv ?
Regards,

Michael

[Taesuk]

Hi mickael.cocquerel,

Correct, the user is selected during the replication process and not pre-created. The users are meant to be schemas and not general users which may require roles and privilege to be copied as well. The general users are in our roadmap.

I've used our DEV landscape and given SELECT grant on SLPADM schema and SALE HDI container to the technical user and is why those are shown only.

Best regards,

Tae-Suk

[Cocquerel]

Hi taesuk.son ,
Thanks, it clarifies.
One additional question, in step vi , how the wizard know it has to show those _SYS_BIC views ? Is it automatically determined based on SLPADM user authorizations ?
Regards,
Michael

[anishgupta]

Hello Tae Suk,

Many thanks for this wonderful blog. I would have one question on the following statement of yours:-

You mentioned about the following

"Once the remote source is created, SAP HANA Cloud connects to the HANA on-premises through the cloud connector and gets the list of schemas or HDI containers that the technical user have been granted in the pre-requisites."

If I understand it correctly when you refer to HDI containers  in the above statement you mean the runtime for the HDI container which are their schemas right?

Regards,

Anish

[Taesuk]

Hi anish.gupta2

Yes, you are correct. The HDI Container runtime is a secured schema. You would need to give privileges to a DB User to get access to the schema within the HDI container.

 

Let me modify the blog to be more explicit. Thank you for your comments.

 

Best Regards,

Tae-Suk.

[Taesuk]

Hi mickael.cocquerel

Yes, the initial list of calc views will be based on _SYS_BIC but may not be replicated correctly if the any of the dependent object is not authorized or doesn't have privileges for access.

Best Regards,

Tae-Suk

[former_member221748]

When you said : First, lets step through how a schema (XSC) based calculation view can be replicated by selecting SLPADM.   Are you really replicating old SYS_REPO based CVs (created in Studio or WorkBench)?

[Taesuk]

Hi Douglas,

Sorry for the belated reply. Yes, the source can be both the SYS_REPO based CVs (created in Studio or Workbench) and HDI Container based CVs (created in Web IDE).

[Cocquerel]

I have launched the replication of one single view. It is in status loading since more than one hour. How to check the progress and that there is no issue ?

[Taesuk]

Hi Michael,

 

As it would be a bit difficult to help using just a screenshot. Do you mind opening a incident for this so that we can support you.

[Cocquerel]

Hello, here is the incident number 290326 / 2022

[Cocquerel]

The status was still "loading" after several days.

I have tried to delete and recreate everything from scratch but, at the step to select the objects to be replicated, I do not see anymore views from _SYS_BIC schema.

I have check the privilege of my replication user and it has SELECT privilege on _SYS_BIC.
So, I don't understand what's wrong.
I have notice there is a schema SYS_BIC on HANA Cloud that owner is DBAMIN. I have suppose it was created during my first replication attempt.
Also, I have tried to drop  it with user DBAMIN but it says "insuffisant privilege". That's a bit strange as it is the owner of the schema.

[Cocquerel]

The note 3133975 is in status "being updated" since more than one year. Is there a workaround to avoid using SYSTEM user ?

[alan_berkey_wa]

It is my understanding from this that the result of the replication from HANA calculation view is a column view on HANA Cloud. Is there a way to replicate HANA calulation views or the resultant column view to a CDS view?

[Cocquerel]

is it supported to directly replicate calculation views in the HANA Cloud instance of Datasphere or should we setup first a stand-alone HANA Cloud Instance, replicate the views there and then, create virtual tables in Datasphere on top of the remote hana cloud replicated views ?

[Taesuk]

Hi Michael,

 

Sorry for the late replay. Although SAP Data Sphere allows SAP HANA Cloud Central access for the underlying SAP HANA Cloud instance, it is only allowed for monitoring the instance for Data Integration Monitoring. So, the replication is only supported with a stand-alone HANA Cloud Instance at this point.

[Taesuk]

Hi Al,

 

Sorry for the late reply. Currently, we do not support replicating to a CDS view.

Just for clarification, do you mean ABAP CDS view or CAP based calculation view. Regarding HANA CDS views, it is no longer supported with SAP HANA Cloud and should use CAP Model (Cloud Application Programming Model)

For ABAP CDS Views, as it is just a SQL view in runtime, it should not be much different from column views. For CAP based calculation views, we are supporting replication of runtime objects and not design time objects. The CAP based calculation views are design time objects and thus not supported.

[Taesuk]

Sorry for the status of the note.


You can use the following workaround

 

****** Workaround to use a user other than SYSTEM for the Technical user *****

1. Create a SCHEMA named UTILS_SAP_HYBRID

    CREATE SCHEMA UTILS_SAP_HYBRID;

2. Create a View name UTILS_SAP_HYBRID.M_DEV_CS_HIERARCHY_DEFINITIONS AS SELECT * FROM SYS.M_DEV_CS_HIERARCHY_DEFINITIONS.

    CREATE VIEW UTILS_SAP_HYBRID.M_DEV_CS_HIERARCHY_DEFINITIONS AS SELECT * FROM SYS.M_DEV_CS_HIERARCHY_DEFINITIONS;

3. Grant SELECT on SCHEMA UTILS_SAP_HYBRID to the technical user connecting from SAP HANA Cloud.

GRANT SELECT ON SCHEMA UTILS_SAP_HYBRID to <technical_user>

4. Grant SELECT on SCHEMA _SYS_BI for time dimension data and other reference data in _SYS_BI schema

    GRANT SELECT ON SCHEMA _SYS_BI to <technical_user>

[Cocquerel]

Thanks for your confirmation. I have submitted the Following improvement request for Datasphere https://influence.sap.com/sap/ino/#/idea/303591
@all, your votes are welcome

[Cocquerel]

How to identify that some replicated views have been updated in source system and are no more in-line with target system ?
How to reload those views without reloading the underlying tables ?

[ganadha203]

Hello,

I have couple of clarifications.

1. After replication of runtime calculation views from XSC or XSA, the underlying tables data needs to be replicated to Cloud or the queries will run on the on premise systems?


2. If we need to completely discontinue on premise HANA systems then what will be migration approach to HANA cloud.

Thanks

Gana

[Taesuk]

Hi Michael,

From the monitoring view, you can pause the replication then apply the changes and finally resume it. We compare the dates of replication and replication to detect changed objects. Apply the changed objects such as CV or SQL Views and then finally resume replication without reloading the entire table.

For Tables, RTR will automatically apply the changes during realtime replication.

Best regards,

Tae-Suk

 

[Taesuk]

Hi Gana,

Please find my answers below.

1. The underlying tables will be setup to be replicated or a one-time load based on the configuration done during setup. The Calculation view query will run in SAP HANA Cloud


2. This requires a migration of existing design time objects
   
   
   
   1. Convert XSC to XSA design time objects
   
   
   2. Migrate from XSA to SAP HANA Cloud design time objects
   
   
   3. Deploy to SAP HANA Cloud HDI containers
   
   
   
   

 

Best regards,

Tae-Suk

[ganadha203]

Thanks for the quick response Tae-Suk!

[Cocquerel]

Hi taesuk.son
Thanks for your quick answer. I have just checked and it is working fine.
Would there be a way to automatize a pause/resume of the replication once a day ?
I have noticed that time data are reloaded when resuming the replication and that takes a bit of time (10 minutes). I was wondering why table replication is not used for those data like other tables. I mean that would avoid reloading those data each time the replication is resumed.

[Taesuk]

Thank you for confirming.

There was some needed enhancement on our side for handling large data and I will check the time data, if we have missed to enhance it.

Will you be still interested in participating in an EAC to check and remove any further limitations such as time data and possible get feedback for further enhancements.

I've sent an email to Mariano to check offline.

 

Best regards,

Tae-Suk

[Cocquerel]

When trying to switch a replicated table from "In-Memory" to "Disk", I'm getting the following error:

SAP DBTech JDBC: [403]: internal error: Could not execute a query through the remote source (detail: [SAP AG][LIBODBCHDB SO][HDBODBC] General error;328 invalid name of function or procedure: no procedure with name REMOTE_TABLE_REPLICA_GENERATE_BLOCK_LOG_DEV found: line 1 col 10 (at pos 9))

is it a known issue ?

[Taesuk]

Can you share the version of SAP HANA Cloud being used? We have provided a fix for QRC04-2022 for it but please let us know if the issue still remains.

[Cocquerel]

Yes, we have a new project starting on the subject and I confirm we are still interested in participating in an EAC.

[Cocquerel]

Current version is 2022.42.21 (QRC 4/2022).
We are planning an upgrade soon.

[Taesuk]

Sorry, it was fixed in QRC01-2023. So, an upgrade is required.

[ganadha203]

Hello Tae-Suk,

I am not sure if you can answer this query.

If we migrate the on-premise Native HANA XSC system to hyper scaler (Azure / AWS / GCP) then what are the prerequisites ? does hyper scalers support Native HANA classic system (1.0) or do we need to migrate to HANA 2.0 XSA system?. Please share if any documents available on this.

 

Thanks

Gana

[Taesuk]

Hi Gana,

 

XSC runs within HANA system and is inactive in HANA 2.0 and needs manual activation. So, there is no dependency as part of Hyperscalers. Keep in mind that HANA 1.0 is end of support and XSC running in HANA 2.0 may not be supported in future as it has been deprecated for a while. So, it is highly recommended to upgrade to XSA if possible.

For XSA, a separate VM is needed to deploy and run XSA runtime and applications.

For SAP HANA Cloud, XSA runtime is not needed as it will be supported in CF.

Best regards,

Tae-Suk

[ganadha203]

Thanks for your reply Tae-Suk! customer currently on HANA 1.0 SP102 and SAP Database OS -SUSE Linux Enterprise Server 11.3 so they can deploy this in Hyper scalar without HANA DB upgrade right? They will upgrade the HANA DB later point intime.

 

Thanks

Gana