If you would like to get data in and out of SAP analytics cloud the most straightforward way to do it is to use import or export jobs from within the “Data Management” section of the model editor.
Import jobs will load data from external systems – SAP BW or HANA amongst other – into SAC, while export jobs will send the data from your SAC model into other systems, for example SAP BW/BPC.
While these load jobs are easy to configure, their purpose is to provide a scheduled data load – or they can be executed on-demand by the administrator or SAC power user. If you need to access data in your SAC models on-the-fly, direct access via the Data Export Service API is the way to go. This is a programmatic access that can be used to fetch current fact and master data records from SAC into external systems with a ‘pull’ paradigm.
Here is an example: You have created an SAC Planning app for your top management to enter most important target budget figures on a high level, e.g. EBITDA per company. In your SAP BPC system you have a planning application used by the controllers, where they are planning the budget on highly detailed level. When the controllers save their data, you would like to display the difference between their “detail EBITDA” and the “top management target EBITDA” based on the most up to date values available. As top management could have changed these figures recently in SAC, you need to access the current data on the fly from the ABAP coding (planning function) of your BPC application.
What is Data Export Service API?
The Data Export Service – in short DES – is a REST API that provides on demand access to model data in SAC. To be precise, it is an OData service.
Configuring Authentication
In order to be able to access the DES API you need to configure a new client on SAC side – in the App Integration tab of your System Administration settings. This can be thought of as a ‘technical user’ that can be used to access the API. You need to add a new OAuth Client, with the purpose ‘Interactive Usage and API Access’ and in the access field select ‘Data Export Service’. You will be presented a cryptical Client ID and a Client Secret, and an Access token URL that can be used to acquire tokens from the authentication service.
If you would like to call the DES service from ABAP, you will need to acquire a tokent before firing your actual API calls. As you do not want to hardcode your client secret into your source code, here is the way to go:
- Create an new OAuth 2.0 Client Profile of type DEFAULT. You can do this for example in transaction SE80 in the appropriate context menu. You don’t need to pass any scope.
- In transaction OA2C_CONFIG create a new OAuth 2.0 client for the profile you just created. Enter here the authentication URL, client ID and client secret.
If you want more details on this process, see this blog post. Of course your general SSL configuration in transaction STRUST should also be correct for the HTTPS calls to succeed.
Calling the service
Unless you have the option to generate a client proxy, you can call the API by:
Getting an HTTP client instance:
...
cl_http_client=>create_by_url(
EXPORTING
url = iv_url
ssl_id = 'ANONYM'
IMPORTING
client = lo_http_client
).
lo_http_client->request->set_method( 'GET' ).
...Configuring OAuth authentication on the client
...
cl_oauth2_client=>create(
EXPORTING
i_profile = 'MY_CLIENT_PROFILE_NAME'
RECEIVING
ro_oauth2_client = DATA(lo_oauth2_client)
).
...
lo_oauth2_client->execute_cc_flow( ).
...
lo_oauth2_client->set_token(
EXPORTING
io_http_client = co_http_client
i_param_kind = 'H'
).
...Firing the HTTP call, and getting the response:
...
lo_http_client->send( ).
lo_http_client->receive( ).
lv_json = lo_http_client->response->get_cdata( ).
lo_http_client->close( )
...
"Parse the JSON into an ABAP deep structure:
/ui2/cl_json=>deserialize(
EXPORTING
json = lv_json
pretty_name = /ui2/cl_json=>pretty_mode-camel_case
CHANGING
data = rr_data
).
...In order to get JSON (instead of XML) results from the service, make sure to add the OData parameter $format=JSON to the request URL.
Which calls do you need?
- First you need to fetch the provider list in order to look up the model ID from the model name – unless you want to hardcode the model ID in your calls.
/api/v1/dataexport/administration/Namespaces(NamespaceID=’sac’)/Providers
Hint: use a filter on model name - Get the provider metadata to know which dimensions and measures you have in your model and the appropriate data types (for dynamic processing on ABAP side)
/api/v1/dataexport/providers/sac/[model id]/$metadata?$format=JSON - Download
- Fact data (without dimension properties)
/api/v1/dataexport/providers/sac/[model id]/FactData - Fact data (with dimension properties, a.k.a. “navigation attributes”)
/api/v1/dataexport/providers/sac/[model id]/MasterData - Master data of a dimension
/api/v1/dataexport/providers/sac/[model id]/[dimension_name]Master
- Fact data (without dimension properties)
Find more information on the API endpoints on the SAP help pages. A good way to test you calls and develop a feeling for the API structure is to first call some test URLs from PostMan. (For this you need to get a token first from the authentication URL, and provide that bearer token along with your test requests.)
Note: all code above are just snippets to illustrate the process, and not complete, e.g. it does not contain any error handling.
In a hurry? Take a shortcut…
If you need to access SAC data from the DES API and have no internal knowledge or resources to develop the client, take benefit of our know-how. We can implement extraction and configure your system for a working connection with a few days of development effort.
