Executing Analysis for Office InA/MDS Request in Active/Active (Read Enabled) En...

Summary

Active/Active (Read Enabled) enables SAP HANA system replication to support read access on the secondary system. It reduces the load on the primary and has been available with SAP HANA 2 SPS00 to route SQL requests to the secondary system.

Now with SAP HANA 2 SPS06, it is possible to route SAP HANA InformationAccess Service (or InA service) to the secondary system. To achieve this, you need SAP HANA Analytics Adapter for XSA.

SAP HANA Analytics Adapter (HAA) for XSA is the successor of the XSC-based InA service. It leverages the InA protocol under the hood to enable your analytics tool to connect with your SAP HANA Database.

1. Prerequisites

• Setup SAP HANA System Replication for HANA+XSA with the logreplay_readaccess operation mode. The SAP HANA 2.0 SPS should be SPS06 or higher. See Configuring SAP HANA System Replication for help on SAP HANA System Replication Configuration.
• Install the EPMMDS plugin.
• Download the on-premise version (e.g., xsahaa-onprem-2.1.4-XSACHAADP01_4.zip) of the SAP HANA Analytics Adapter from SAP Development Tools.

2. Setting up the user

See Maintaining Tenant Databases in XS Advanced for help on IdP.

2.1 Case I: SYSTEMDB is used as Identity Provider (IdP)

This procedure is designed for an on-premise setup with XSA when the SYSTEMDB of your HANA instance is used as Identity Provider (IdP).

2.1.1 Checking if a JWT provider is available and create one if not

Run the SQL command in both your tenant DB and SYSTEMDB associated with it:

SELECT JWT_PROVIDER_NAME FROM JWT_PROVIDERS;

This returns the name of the JWT Provider if one exists. It should be the same on both the DBS.

If no name is returned, you need to re-establish the trust in your database. The best way to do this is to use the “renew-db-trust” option of the XSA tool (you need to be logged on the Linux server with the user corresponding to the SID of your HANA instance) as explained in the SAP Note 2654543 – Re-establish certificate trust between XS advanced and SAP HANA database. If it does not work, please try to other older method mentioned in SAP Note 2470084 – XSUAA metadata for XS_APPLICATIONUSER trust creation.

Once done, please verify that you have a JWT Provider on both DBs with the above SELECT command.

2.1.2 Creating the users

You need to create and configure the same user first in the tenant DB and then in the SYSTEMDB.

SQL commands to run in the tenant DB:

CREATE USER <your_user_name> PASSWORD <your password> NO FORCE_FIRST_PASSWORD_CHANGE; 
GRANT EXECUTE ON SYS.EXECUTE_MDS TO <your_user_name>; 
ALTER USER <your_user_name> ENABLE JWT; 
ALTER USER <your_user_name> ADD IDENTITY '<your_user_name>' FOR JWT PROVIDER <JWT_PROVIDER_NAME>;

SQL commands to run then on the SYSTEMDB:

CREATE USER <your_user_name> PASSWORD <your password> NO FORCE_FIRST_PASSWORD_CHANGE; 
GRANT EXECUTE ON SYS.EXECUTE_MDS_DEV TO <your_user_name>;

2.2 Case II: Tenant DB is used as Identity Provider (IdP)

This procedure is designed for an on-premise setup with XSA when the tenant DB of your HANA instance is used as Identity Provider (IdP).

2.2.1 Checking if a JWT provider is available and create one if not

Run the SQL command in your tenant DB:

SELECT JWT_PROVIDER_NAME FROM JWT_PROVIDERS;

This returns the name of the JWT Provider if one exists.

If no name is returned, you need to re-establish the trust in your database. The best way to do this is to use the “renew-db-trust” option of the XSA tool (you need to be logged on the Linux server with the user corresponding to the SID of your HANA instance) as explained in the SAP Note 2654543 – Re-establish certificate trust between XS advanced and SAP HANA database. If it does not work, please try to other older method mentioned in SAP Note 2470084 – XSUAA metadata for XS_APPLICATIONUSER trust creation.

Once done, please verify that you have a JWT Provider on your tenant DB with the above SELECT command.

2.2.2 Creating the users

You need to create and configure the user in the tenant DB

SQL commands to run in the tenant DB:

CREATE USER <your_user_name> PASSWORD <your password> NO FORCE_FIRST_PASSWORD_CHANGE; 
GRANT EXECUTE ON SYS.EXECUTE_MDS TO <your_user_name>; 
ALTER USER <your_user_name> ENABLE JWT; 
ALTER USER <your_user_name> ADD IDENTITY '<your_user_name>' FOR JWT PROVIDER <JWT_PROVIDER_NAME>; 
GRANT EXECUTE ON SYS.EXECUTE_MDS_DEV TO <your_user_name>;

3. Deploying SAP HANA Analytics Adapter

You have downloaded the on-premise version (e.g., xsahaa-onprem-2.1.4-XSACHAADP01_4.zip) of SAP HANA Analytics Adapter from SAP Development Tools.

Note: It is recommended to use xshaa-onprem version 2.1.4 or higher. The newer versions consume less memory.

3.1 Edit MTA Deployment Descriptor (mtad.yaml)

Steps:

1. Extract the on-premise version.

2. Locate MTA Archive (MTAR) with the name like xsahana-onprem-<version>.mtar in the extracted directory.

3. Open the MTAR file using any archive utility (e.g., WinRAR).

4. Locate mtad.yaml in the MTAR and open it using any text editor.

5. Locate and set the value of parameter SECONDARY_ROUTING_PERCENT between 0 to 100 in mtad.yaml. This parameter controls what percent of queries should be routed to the secondary active/active read enabled system. If set to 0 then routing to the secondary system will be disabled; if set to 100 then all the requests will be routed to the secondary system.

   E.g., Set SECONDARY_ROUTING_PERCENT to 100 if you want to route all the requests to the secondary as below:

   modules:
     - name: xsahaa-java
       properties:
         SECONDARY_ROUTING_PERCENT: 100

6. Save the mtad.yaml file back into the MTAR file.

See the below documents for more details on MTAR:

The MTA Deployment Descriptor

Maintaining the XS Advanced Application Development & Deployment Descriptors

3.2 Deploy modified MTA Archive (.mtar)

Prerequisites:

• You must deploy the analytics adapter in a space (or spaces) other than the default space (SAP), for example, in the space DEV.
• The target run-time space must already be mapped to a tenant database before you deploy the analytics adapter.
• Your SAP HANA version must be at least SAP HANA 2.0 SPS 06 Revision 60.00 or higher.
• You must have the SAP HANA EPM-MDS component installed in your SAP HANA database.

See Maintain the Analytics Adapter in XS Advanced for more details.

Follow the steps below to deploy the MTAR file.

Steps:

1. Login to xsa admin.

   Command:

   xs login

2. Switch to desired ORG and SPACE.

   Command:

   xs target -o <org> -s <space>

   See Maintaining Organizations and Spaces in XS Advanced for more details.

3. Deploy the HAA application using the below command. You must use thexsahana-onprem-<version>.mtar file which was modified in the previous step.

   Command:

   xs deploy /<directory>/xsahaa-onprem-<version>.mtar

   See Maintaining Multi-Target Applications in XS Advanced for more details.

4. Check the host and port of the entry point under “URLs”:

   Command:

   xs app xsahaa-entry

   Output:

   Showing status and information about "xsahaa-entry"
name: xsahaa-entry
requested state: STARTED
instances: 1
memory: 1.00 GB
disk: <unlimited>
buildpack: sap_nodejs_buildpack
urls: https://<your_host>:<your_port>

   You will see below welcome page after opening https://<your_host>:<your_port>

   

   SAP HANA Analytics Adapter welcome page

5. Open the heartbeat URL in your browser to verify you are logged in with the named user.
   
   https://<host>:<port>/sap/bc/ina/service/v2/HeartBeat

   If everything is fine, then you should get the output as follows:

   {"userName":"<your_user_name>","applicationName":"sap.bc.ina.service.v2","applicationUser":"<your_user_name>","URL":"https://<your_host>:<your_port>/sap/bc/ina/service/v2/HeartBeat"}

   You can also open the below URL. If everything is fine then you should get a big json output.

   https://<host>:<port>/sap/bc/ina/service/v2/GetServerInfo

   You have successfully installed SAP HANA Analytics Adapter.

4. Make connection SSL Secure

Make the connection to HAA SSL secure. Then only the Analysis for Office will be able to connect to HAA using HTTPS.

See below SAP Notes for help on making connection SSL secure:

SAP Note 2325467 – Exception when using Analysis for Office to connect to HANA with SSL/TLS

SAP Note 2243019 – Providing SSL certificates for domains defined in SAP HANA extended application services, advanced model

5. Set SAP HANA Parameter

Make sure that the below parameter is set to true (default: true).

indexserver.ini -> [mds] enable_execution_on_secondary = true

When set to true, it enables InA query execution on the secondary system of Active/Active (Read Enabled).

See SAP Note 2983761 – Executing InA/MDS Request in Active/Active (Read Enabled) Environment for more details.

6. Connecting from SAP Analysis for Microsoft Office

From SAP Analysis for Microsoft Office, use the HAA host, port, and HTTPS connection type along with the user credentials to connect to the SAP HANA database using HAA.

Now you should be able to route requests SAP Analysis for Microsoft Office InA/MDS requests to the secondary Active/Active (Read Enabled) environment.

Conclusion

Please share your feedback or thoughts in the comments section. You may follow me for similar content on SAP HANA.

You can also check Database and Data Management for more awesome content on SAP HANA, post questions and answers, and read related posts on the topic.