SAP Analytics Cloud Export Data API Sample Scripts

[Matthew_Shaw]

I've written a bunch of Sample Scripts, in Postman, that demonstrate how the 'export data' API retrieves data from models stored inside SAP Analytics Cloud. These samples are now available.

This blog first takes a step back and provides an overview of other means and ways to extract data before then introducing the sample scripts themselves. If you are thinking of using the export data API, then these samples and the associated User Guide can remove some barriers to adopting the API since much of the code and logic can easily be re-used and they provide a useful insight into how the API works and behaves.

A number of, currently undocumented but supported features, also form part of these samples. It means the samples and this associated User Guide provide a practical starting point to ensure your coding project gets off to the best start possible.

Overview

SAP Analytics Cloud is often chosen as an enterprise planning solution. The planning data, held in SAP Analytics Cloud, occasionally needs to be further integrated with other systems to complete the business planning process in some way or another. This new ‘export data’ API provides another option to the many already existing options that are described now.

There are many means and ways to export data from SAP Analytics Cloud to other systems and these include using the:

• postMessage API call available within Analytic Applications:
  
  
  
  • Official documentation: User Guide and API reference
  
  
  • Related blogs: Analytics designer communicating between embedded applications and host web pages using the post mes... and Control SAP Analytics Cloud, Analytics Designer App Remotely with Mobile Phone
  
  
  
  


• OData service available within Analytic Applications:
  
  
  
  • Official documentation: Use an OData Service in Analytic Applications
  
  
  • Related blog: How to use an S4/Hana OData service in Analytics Designer
  
  
  
  


• Custom widgets available within Analytic Applications:
  
  
  
  • Official documentation: Using Custom Widgets
  
  
  • Related blog: Quickly integrate SAP Analytics Cloud with other systems using custom widgets
  
  
  
  


• File export to a shared folder
  
  
  
  • Official documentation: Exporting a Model to a File
  
  
  
  


• Page or widget (table/chart) options to export from within a Story or Application to csv and other formats

A new method is available with SAP Analytics Cloud Q2 2022 Quarterly Release called the ‘export data’ API. This is an extension to the OData standard and allows the retrieval of all the data from acquired models (both analytic and planning models) on a ‘pull’ basis, unlike the above methods which operate on a ‘push’ basis. The official documentation describes it as

… a PULL based API data extraction API that helps with data integration from SAP Analytics Cloud to SAP Data Warehouse Cloud, SAP BW, SAP HANA, and other third-party tools. The API doesn't have a dedicated user-interface, and simply extracts data and information about providers.

Sample Scripts

These sample scripts, written in Postman, demonstrate each of the API endpoints. The samples are provided on an ‘as is’ basis and they only retrieve data from SAP Analytics Cloud models, they don’t do anything with the data once it’s been retrieved. Though some of the samples write the data to the console, that’s it. It means these samples are just to demonstrate the API endpoints along with many aspects such as how:

• sessions and errors can be managed


• ‘pages’ of data can be retrieved when multiple pages are available (the samples automatically fetch all the pages of data)


• optional request parameters can be used

If you’re using an SAP connector then there’s no code to develop, it’s just plug-and-play:

Target system that will pull data from SAC:	Related article:
Data Warehouse Cloud and Data Intelligence	blog
SAP HANA, SAP BW on HANA, SAP BW/4HANA	blog

However, if you are using your own custom application then you will need to write code.

Postman, which is freely available, is a commonly used tool for implementing API workflows. Postman provides a good basis to demonstrate how requests are made to the API, and optionally export code snippets in various languages, such as Java, Python, C, PHP, Node.js and many others. This means development time can be reduced, but nevertheless, you will need to adapt the scripts for your own use, either to process the data or upload it to a target data source for further processing.

The list of the samples I've provided are:

• Export Data 761-Test-Environment Setup


• Export Data 762-Metadata


• Export Data 763-Namespaces (sets Environment ExportData_NamespaceID)


• Export Data 764-Namespaces ID


• Export Data 765-Namespaces ID Provider


• Export Data 766-Fcj-Provider Metadata


• Export Data 767-Fcj-Service Document


• Export Data 768-Fcj-Fact Data


• Export Data 769-Fcj-Master Data


• Export Data 770-Fcj-Audit Data


• Export Data 771-Fcj-Dimension Master Data

Resources

SAP Analytics Cloud Export Data API Sample Scripts User Guide	Version 0.7.1 - August 2022 .pdf Download .pdf Preview
Samples (the code)	Version 0.7.1 – August 2022 GitHub (zip download) Change log

Frequently Asked Questions

Q1: When retrieving a dimension the columns 'read' and 'write' (that are used by SAP Analytics Cloud for Data Access Controls) are not returned, why is this?

A1: Data Access Controls and Data Locks are not part of the master data but an extension to the dimension metadata for use only by SAP Analytics Cloud, they have a dependency on them. They are not available for data retrieval and they don't form part of the API. There are no plans to change this.

 

Q2: When retrieving a dimension the column 'parent' (that is used by SAP Analytics Cloud for hierarchical data structures) is not returned, why is this?

A2: The hierarchy is part of an internal hierarchy extension and doesn't form part of the data retrieval and they don't form part of the API. There are no plans to change this.

 

Q3: Calculated measures are not retrieved, why is this?

A3: The API is for data retrieval only and a calculated measure is out of scope for this reason (the API is only for data retrieval not for any form of 'query' aspect). They are not available for data retrieval and they don't form part of the API. There are no plans to change this. As a workaround, these measures could be materialised via a data action making them then retrievable.

 

Q4: Is Public Dimension Master Data accessible using this Export Data API?

A4: No. Dimensions in the model are accessible, however, public dimension Master Data is not available. Please see Influence Request 264969

 

Q5: Are custom properties of a dimension accessible using this Export Data API?

A5: Whilst hierarchical data is not available, generic (also known as custom) properties are accessible via the API

 

Q6: A planning model contains group currency values, does the Export Data API enable these values to be exported?

A6: No. The group currency values are not materialized or persisted and they are not available via the Export Data API. The data export API provides only data from base measures but not from calculated measures or conversion measures. However, if you really want to export converted currency values then you can create a second model that contains all necessary (calculated or conversion) measures as base measures. Then, create a cross-model copy between the original model and the new model. You need to map the calculated measures and the conversion measures to the corresponding base measures in the target model. Once the cross-model copy is executed, the calculations are performed and stored (materialized) in the target model. You can now export via Data Export Service from this second model. To be sure the calculations are up to date, you would have to run the copy any time before you export data from the second model.

 

Feedback

I’ve invested a great deal of time and effort into these materials so your feedback is very welcome and will help judge if I should continue to create these kinds of resources

Please do:

• Comment if you use these resources in any way (or if you’re shy, just hit the like button!)


• Share which sample scripts you’ve used. Other customers would love to hear if you’ve used the scripts. It will give them a sense of how reliable they are! 😉


• Share your experience


• Share how much time you saved because of these resources, would you have been as successful without them?

Before posting any questions please:

• Do read the contents of the User Guide. I appreciate you may not have the time to read it all. If you’re looking for a quick answer and don’t have the time, feel free to post a question to the community rather than here, it will help keep the number of questions here reduced and it will help others find answers easier (than searching this blogs’ Q and A). You can always ‘@tag’ me in your post so I get a notification, and you can always post a link to your question from a comment to this blog if you think that might help others.


• If you’ve got a question about the API in general, then post a question to the community rather than here

Feel free to follow this blog post for updates. I’ll update the version numbers in this blog post when there’s one to update.

Many thanks

Matthew Shaw @MattShaw_on_BI

matthew.shaw/#content:blogposts

 

Other related articles

https://blogs.sap.com/tag/sap-analytics-cloud-data-export-service/

Data Export API: A tour of the API by david.stocker provides an overview of each endpoint and how to integrate this with Python.

 

[fabianrunge1]

Hi Matthew,

the new API looks super promising and our first test worked very well but unfortunately we didn't find a way to export calculated measures from SAC models via this method.

 

Are there any ways to access these measures?

 

If not are there plans on offering this functionality in the future?

 

Best

Fabian

[Matthew_Shaw]

Hello Fabian

I've just added an FAQ section to this blog post which captures your question and the answer.

All the best, Matthew

[Matthew_Shaw]

Just updated the documentation to reflect the support for 3-legged OAuth as it is now supported 🙂

[Matthew_Shaw]

I've just added a duplicate set of Samples that support 3-legged OAuth. The 3-legged OAuth sample scripts start with an '8' rather than a '7' for example the 3-legged OAuth version of sample '765' is '865' etc. Whilst the user guide will be updated later, a few short instructions to get these 3-legged OAuth samples to work:

• You need to create a new SAC OAuth client as this new OAuth client must have the 'Redirect URI' to be 'https://oauth.pstmn.io/v1/callback'.


• Your web browser must allow pop-ups and re-directs for 'oauth.pstmn.io'


• and finally the user you use to login to SAP Analytics Cloud needs to be a non-admin user, assuming you want that user to see a restricted set of models/data.

And don't forget you need to press the 'Get New Access Token' in the 'Authorization' tab of the request.

Hope you find them helpful. Matthew

[matteo_s]

Hi Matthew,

thanks for the blog post. It is indeed a very promising feature especially to avoid exporting SAC model's data to flatfiles.

I have a question: do you think it is possible to use this API as a OData source for SAP BusinessObjects unx? And if yes, what should the service root uri look like? I'm currently testing on a 4.3 SP1 release but it doesn't seem to work properly:

Many thanks for any suggestion you would like to share.

Best Regards.

Test Api with generic odata driver

[Matthew_Shaw]

Hi Matteo

Good thinking, I like it, Sadly though this API doesn't conform to the right requirements for BusinessObjects to consume it 😞

All the best, Matthew

[matteo_s]

Hi Matthew,

ok. This is indeed quite unfortunate.

Thanks, by the way, for your quick reply.

Best Regards.

[dharma_ndt]

Hello Matthew,

Thanks for the post.Can we export data from SAC live models using API/ODATA? For some reason I am not seeing live model ids in sac namespace.

Thanks,

Dharma

[Matthew_Shaw]

Hello Dharma,

The API is for exporting data of acquired data models only. This export API won't operate as a proxy to remote (also known as live) data sources. For remote (or live connections) you'll need to go to that remote database and export the data from there.

It is expected that you do not see live models using this API.

Hope this helps

Regards, Matthew

[michael_tomaino]

To Fabian and any others who have been struggling with this, here's something that might work for you...

Since there aren't any plans to add this functionality, I had to find a workaround.

What works for me is to create an explicit calculated column in the SAC data model. Once this is done, the API response will include that column. Not sure if this was a feature back in May 2022, but it works now.

[Marco_Males]

Hello Matthew,

are you planning to provide a blog for SCIM API v2, too?

BR

Marco