This is part of a series on exploring the SAP Analytics Cloud (SAC) data export API. When the series is complete, it will also be available as a single tutorial mission.
We’ve configured our SAP Analytics Cloud (SAC) tenant to accommodate an application. Now we’ll assume the mantle of that app and explore the export API. I’ll assume that you have an API tester and know how to use it. You can use any that you wish, but I’ll be using Postman for the examples. The process of getting data out of a model involves – at a minimum - the following steps:
All of the URLs in the SAC data export API follow a similar pattern of baseURL + service endpoint. The baseURL is built up as:
So if you know your tenant name and which data center your tenant is on, it is easy to construct. For individual services, you’ll append the URL "service endpoint" fragment for the individual service.
All service URL endpoints use GET, not POST. In practice, this means that you’ll be building up any parametrization in the URL string itself, instead of a json body.
There are four administration service endpoints.
Neither of the first two endpoints returns much of any importance and are there for OData compliance. There is a single namespace (‘sac’) in the export API, so both return the same thing; the name and description of this one namespace, in json format.
returns
In Postman, this looks like:
/administration/$metadata - this endpoint returns the schema definition of the API itself, in XML format; aka the API's OData EDMX. If you want to view it in an OpenAPI tool, such as Swagger, you can convert it. We are not going to use it, but it is available as a machine-readable API definition.
There are two service URL endpoints, which return our model metadata
/providers/sac/<ProviderID>/ returns the service document. The service document is a listing of the available service endpoints for this provider. These endpoints are:
E.g. the URL for the Best Run Juice’s OData service document looks like:
And the response json is:
Generally, these provider specific endpoints all return their full (audit, dimension master or fact) dataset by default. So the fact table endpoint would return the entire fact table, dimension master data endpoints would return all master data values, etc. These endpoints all support the typical OData parameters, such as $select, $filter, $orderby, $pagesize and $skip, allowing you to shape the returned data json to your needs.
/providers/sac/<ProviderID>/AuditData – This endpoint returns a (maybe) small json, which the number of entries in the audit log, and the audit log itself. If auditing has not been enabled for the model, the endpoint still works; it just returns an empty audit log.
e.g. this model has not had auditing enabled:
It still returns a valid json response, but you can see that the audit log (value) is empty:
/providers/sac/<ProviderID>/$metadata – This is an interesting endpoint, because it returns the full OData EDMX API definition for that particular model. Sifting through this juicy document, you can figure out what your measures are, account dimensions, date dimensions, generic dimensions, etc. You might be inclined to use the service document as a 10,000ft view of your model, because it is easy to read, but the EDMX document is much more informative.
providers/sac/<ProviderID>/FactData – This is the model fact data, returned in json format. As mentioned before, unless you filter it or page the returned results, it will be the entire fact table* (at least until delta is available with QRC 2023.Q1).
If you wanted the entire fact table from the Best Run Juice sample model, you’d craft your http call as such:
But if you wanted only the Quantity Sold account and only fact data related to product ID PD1, you can append OData filters:
/providers/sac/<model>/<dimensionID>Master – This endpoint returns a json with the master data of the specified dimension. E.g. for a full enumeration of the X dimension, in the Best Run Juice model, you’d craft the following URL:
This concludes our tour of SAC’s data export API, as of January 2023. We have active plans for expanding this API, so more endpoints and features will be added. Next time, we’ll start building a Python wrapper.
We’ve configured our SAP Analytics Cloud (SAC) tenant to accommodate an application. Now we’ll assume the mantle of that app and explore the export API. I’ll assume that you have an API tester and know how to use it. You can use any that you wish, but I’ll be using Postman for the examples. The process of getting data out of a model involves – at a minimum - the following steps:
- Get the authorization token for the session
- List the “providers” (a.k.a. models)
- Fetch the metadata for s specific model
- Fetch the audit, master, and fact data for that model, as desired
Authorization Token
- The token URL from the App Integration overview
- The Client ID and Secret from the individual OAuth client
- The token URL from the App Integration overview
The export API URL pattern
All of the URLs in the SAC data export API follow a similar pattern of baseURL + service endpoint. The baseURL is built up as:
https://<tenantName>.<dataCenter>.sapanalytics.cloud/api/v1/dataexportSo if you know your tenant name and which data center your tenant is on, it is easy to construct. For individual services, you’ll append the URL "service endpoint" fragment for the individual service.
All service URL endpoints use GET, not POST. In practice, this means that you’ll be building up any parametrization in the URL string itself, instead of a json body.
Administration Service URL Endpoints
There are four administration service endpoints.
- /administration/Namespaces
- /administration/Namespaces(NamespaceID='sac')
- /administration/$metadata
- /administration/Namespaces(NamespaceID='sac')/Providers
Neither of the first two endpoints returns much of any importance and are there for OData compliance. There is a single namespace (‘sac’) in the export API, so both return the same thing; the name and description of this one namespace, in json format.
https://<tenant>.<datacenter>.sapanalytics.cloud/api/v1/dataexport/administration/Namespacesreturns
{
"@odata.context": "https://<tenant>.<datacenter>.sapanalytics.cloud/api/v1/dataexport/administration/$metadata#Namespaces",
"value": [
{
"NamespaceID": "sac",
"Description": "SAC Namespace"
}
]
}In Postman, this looks like:
/administration/$metadata - this endpoint returns the schema definition of the API itself, in XML format; aka the API's OData EDMX. If you want to view it in an OpenAPI tool, such as Swagger, you can convert it. We are not going to use it, but it is available as a machine-readable API definition.
https://<tenant>.datacenter>.sapanalytics.cloud/api/v1/dataexport/administration/Namespaces(NamespaceID='sac')/ProvidersFetching the model metadata
There are two service URL endpoints, which return our model metadata
- /providers/sac/<ProviderID>/
- /providers/sac/<ProviderID>/$metadata
/providers/sac/<ProviderID>/ returns the service document. The service document is a listing of the available service endpoints for this provider. These endpoints are:
- The audit data
- The model master data
- The fact data
- Each dimension’s master data
E.g. the URL for the Best Run Juice’s OData service document looks like:
https://<tenant>.datacenter>.sapanalytics.cloud/api/v1/dataexport/providers/sac/sap.epm:BestRunJuice...And the response json is:
{
"@odata.context": "$metadata",
"value": [
{
"name": "FactData",
"url": "FactData"
},
{
"name": "MasterData",
"url": "MasterData"
},
{
"name": "AuditData",
"url": "AuditData"
},
{
"name": "Account_BestRunJ_soldMaster",
"url": "Account_BestRunJ_soldMaster"
},
{
"name": "Store_3z2g5g06m4Master",
"url": "Store_3z2g5g06m4Master"
},
{
"name": "Location_4nm2e04531Master",
"url": "Location_4nm2e04531Master"
},
{
"name": "Product_3e315003anMaster",
"url": "Product_3e315003anMaster"
},
{
"name": "Sales_Manager__5w3m5d06b5Master",
"url": "Sales_Manager__5w3m5d06b5Master"
},
{
"name": "Date_703i1904sdMaster",
"url": "Date_703i1904sdMaster"
},
{
"name": "Version_BestRunJsold_VMaster",
"url": "Version_BestRunJsold_VMaster"
}
]
}Generally, these provider specific endpoints all return their full (audit, dimension master or fact) dataset by default. So the fact table endpoint would return the entire fact table, dimension master data endpoints would return all master data values, etc. These endpoints all support the typical OData parameters, such as $select, $filter, $orderby, $pagesize and $skip, allowing you to shape the returned data json to your needs.
/providers/sac/<ProviderID>/AuditData – This endpoint returns a (maybe) small json, which the number of entries in the audit log, and the audit log itself. If auditing has not been enabled for the model, the endpoint still works; it just returns an empty audit log.
e.g. this model has not had auditing enabled:
https://<tenant>.datacenter>sapanalytics.cloud/api/v1/dataexport/providers/sac/Cvjk1n1h4a6dr12dm10c0...It still returns a valid json response, but you can see that the audit log (value) is empty:
{
"@odata.context": "https://<tenant>.<datacenter>.sapanalytics.cloud/api/v1/dataexport/providers/sac/Cvjk1n1h4a6dr12dm10c07rr46o/$metadata#AuditData",
"value": [],
"@des.entitiesInPage": "0",
"@des.processingTimeInMilliseconds": "1040"
}/providers/sac/<ProviderID>/$metadata – This is an interesting endpoint, because it returns the full OData EDMX API definition for that particular model. Sifting through this juicy document, you can figure out what your measures are, account dimensions, date dimensions, generic dimensions, etc. You might be inclined to use the service document as a 10,000ft view of your model, because it is easy to read, but the EDMX document is much more informative.
Fact Data
providers/sac/<ProviderID>/FactData – This is the model fact data, returned in json format. As mentioned before, unless you filter it or page the returned results, it will be the entire fact table* (at least until delta is available with QRC 2023.Q1).
If you wanted the entire fact table from the Best Run Juice sample model, you’d craft your http call as such:
https://<tenant>.datacenter>.sapanalytics.cloud/api/v1/dataexport/providers/sac/sap.epm:BestRunJuice...But if you wanted only the Quantity Sold account and only fact data related to product ID PD1, you can append OData filters:
https://<tenant>.datacenter>.sapanalytics.cloud/api/v1/dataexport/providers/sac/sap.epm:BestRunJuice... eq 'Quantity_sold' and Product_3e315003an eq 'PD1'Dimension Master Data
/providers/sac/<model>/<dimensionID>Master – This endpoint returns a json with the master data of the specified dimension. E.g. for a full enumeration of the X dimension, in the Best Run Juice model, you’d craft the following URL:
https://<tenant>.datacenter>.sapanalytics.cloud/api/v1/dataexport/providers/sac/sap.epm:BestRunJuice...This concludes our tour of SAC’s data export API, as of January 2023. We have active plans for expanding this API, so more endpoints and features will be added. Next time, we’ll start building a Python wrapper.
- SAP Managed Tags:
2 Comments
