Configuring Data Export Service: Restricting to a ...

zili_zhou · ‎01-08-2024

Background:

There are 3 different ways to use SAC Data Export Service API in BW. I have written blogs for each. And the feedback from customers are: BW managed way (Blog 1) is preferred compared to create replication task in HANA Web IDE (Blog 3) or develop own ABAP coding (Blog 2). Currently 90% of customers are using the the BW managed way described in blog 1. The rest BW customers have chosen replication task or ABAP because of different reasons.

As the BW managed way are most used, thus we also got the most feedback from this area. Recently I got a request from colleagues, that after a customer setting up Restricted SAC Exports, it is not possible to set up the data export from SAC to BW. After exploring various approaches, I am eager to share with you in this blog the most convenient solution I've discovered to tackle this challenge.

A special thanks to my colleague Ning, Bin who help to correct the SQL syntax.

Issues to be solved:

First, We called below architecture described in these two blogs "classic configuration".

Leverage the SAP Analytics Cloud Data Export Service to extract your planning data to SAP HANA, SAP ...

Realtime (delta) Data Replication from SAP Analytics Cloud to BW/4 HANA

However based on feedback from different customers, currently there are three known issues for setting up BW source system for all SAC models:

Security Concerns from customers that all SAC models could seen in HANA(BW)

Not possible to create BW source system: Looping large SAC system metadata make the ABAP dialog process timeout (TIME_OUT in CL_SQL_RESULT_SET)

Not possible to create BW Datasource when any SAC model(s) set to Restricted Export

classic configuration

Thus, after doing some more test. Here is a variant of this architecture to solve above 3 issues. And compared to the "classic configuration", there is only one small change (adding one more line the SQL command while creating the HANA remote source). Other steps are very similar.

Experience shows that a SAC production has 200 to 2000 models but normally 2 to 8 planning models need to be exported, which make this solution is possible.

restrict to one SAC model

How to restrict HANA remote source to only one SAC model:

Find out the provider ID of the SAC model which you need to export like below: in my example CE31P3P3UB47A373KIHQKOBJLS

Create a HANA remote source restricting only to this SAC model. Compare to the classic configuration, we have add one more property called "root path" to restrict to a specific SAC model like below. Please replace this with your own one.
<PropertyEntry name="rootPath">/sac/CE31P3P3UB47A373KIHQKOBJLS</PropertyEntry>

CREATE REMOTE SOURCE "<my_remote_sourcename>" ADAPTER "CloudDataIntegrationAdapter" AT LOCATION AGENT "<MyAgentName>" CONFIGURATION

'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<ConnectionProperties displayName="Configurations" name="configurations">

<PropertyEntry name="host"><myprefixURL.sapanalytics.cloud/></PropertyEntry>

<PropertyEntry name="port"></PropertyEntry>

<PropertyEntry name="protocol">HTTPS</PropertyEntry>

<PropertyEntry name="servicePath">/api/v1/dataexport/administration</PropertyEntry>

<PropertyEntry name="rootPath"></sac/CE31P3P3UB47A373KIHQKOBJLS></PropertyEntry>

<PropertyEntry name="auth_mech">OAuth2</PropertyEntry>

<PropertyEntry name="oauth2_grant_type">client_credentials</PropertyEntry>

<PropertyEntry name="oauth2_token_request_content_type">url_encoded</PropertyEntry>

<PropertyEntry name="oauth2_token_endpoint"><your token></PropertyEntry>

<PropertyEntry name="require_csrf_header">true</PropertyEntry>

</ConnectionProperties>'

WITH CREDENTIAL TYPE 'PASSWORD' USING

'<CredentialEntry name="oauth2_client_credential">

<user><your Oauth Client ID></user>

<password><your Oauth Client secret></password>

</CredentialEntry>';

When this is successfully created, you will only see the fact and master data derived from this model in HANA (BW). There is no need for concern regarding the possibility of users with access to the HANA remote source viewing all SAC analytic and planning models along with their associated data.

3. Create Source System in BW. From this step, it is similar to the classic configuration. BW/4 HANA and BW on HANA have a slightly different UI. As BW /4 HANA has already detailed steps in my other blog. Here I will take BW on HANA as an example.

Create source system

Create Source System

Specific your BW source system name, choose "SAP HANA Smart Data Access" as connection type. Then the HANA source (SACDES_CPO_onemodel) your created just now in the SQL will appear in the dropdown list.

create BW source based on HANA Smart Data Access type

When the creation has been done. You can verify by running below SQL

select * from RSLOGSYSDB where "REMOTE_SOURCE" = 'SACDES_CPO_onemodel'

you will see the BW source system has been created.

table RSLOGSYSDB

Here is also an important tip many customers have asked, if you add "F" in column "DP_SETTINGS", it will be later possible to create a filter for BW data source.

4. Create BW data source

Below are the steps in details.

Choose Proposal from SAP HANA Table or View

For transaction data, type "FactData" and search. You will get only 1 result. This is different to the "classic" setting, it will search "FactData" for all SAC models, thus it will take a while till you get the result. Performance could be very bad sometimes.

Confirm the name and finish.

Here you could choose enable "realtime" only when it is "FactData". If it is other master data like "MasterData" or "VersionMaster", please do not check this . Currently SAC does not support delta for master data export.

After activate the data source, you could create the following steps in a process chain loading delta or full data.

What signs indicate problems related to issues 2 and 3?

At the beginning, we mentioned there might be 3 issues that could be solved by this blog. Issue 1 is obvious when we create the HANA remote source as it is restrict to only one SAC models.

Issue 2. Not possible to create source system: Looping large SAC system metadata make the ABAP dialog process timeout (TIME_OUT in CL_SQL_RESULT_SET)

When SAC models has exceed a certain value (it could different as columns are also different, the rough threshold is 200), the create of BW source system could end into timeout as it tries to loop all the metadata while creation and it will normally reach the ABAP dialog work process timeout uplimit.

It could be solved by temporarily increase the dialog work process timeout parameter. But when SAC models has above 1000, it will simplify take too much time.

create BW source based remote source to all SAC models

in transaction ST22, you will see below dumps.

Category ABAP programming error
Runtime Errors TIME_OUT
ABAP: Program CL_SQL_RESULT_SET=============CP
Application Component BC-DB-DBI
Date and Time 18.12.2023 11:53:48 (CET)
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
|Short Text |
| Time limit exceeded. |
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
|What happened? |
| The program "CL_SQL_RESULT_SET=============CP" has exceeded the maximum |
| permitted runtime without |
| interruption and has therefore been terminated. |
| |

3. Not possible to create BW Datasource when any SAC model(s) set to Restricted Export

when you set SAC model(s) set to Restricted Export like below

Currently there is a known issue that for other non restricted export SAC models, you can not search its master or transaction data when creating BW data source. Even the test for HANA connection are successful. Hopefully, this will be improved in the future.

Learn more

Blog posts on SAP Analytics Cloud Data Export Service

SAP Help