SAP Datasphere as enabler for a business data fabric architecture delivers seamless and scalable access to business data. While SAP Analytics Cloud and its Add-In for Microsoft Office are natively integrated and best-suited for analytics use-cases, SAP Datasphere aims to empower any consumer irrespective of the chosen analytical frontend solution.
The public OData API is a great proof point for this, as it now also provides access the Analytic Model. The latter is the cornerstone for business modeling in SAP Datasphere and provides harmonized, semantically-rich access to SAP data.
This blog provides an overview of the OData API and highlights the various endpoints with hands-on examples.
The SAP Datasphere OData API lets you consume views and Analytic Models from SAP Datasphere, and consists of two services:
The supported authentication method is a three-legged OAuth2.0 flow with type authorization_code. This also means that the consumer is authenticating with the standard SAP Datasphere user credentials and the respective data access authorizations are respected.
Moreover, the standard OData v4 query parameters $select, $filter, $orderby, $top and $skip can be used to refine the query.
All specifications are outlined in our SAP Help Documentation: Consume Data via the OData API.
The Catalog API lets you list all accessible spaces and assets that are exposed for consumption. For example, to list all exposed views and Analytic Models in a particular space, one can use the following request:
Applied to my SAP Datasphere demo tenant with specified space, this yields the following result:
As we can see, the selected space contains 44 assets that are exposed for consumption. In addition, every exposed asset provides a reference to its relational or analytical endpoint.
The Relational API lets you consumeviews of the data builder that are exposed for consumption. It consists of two requests:
The consumption is in relational manner, i.e. irrespective of the selected measures and dimensions, no data is being aggregated but is retrieved in a row-by-row fashion. The result is the same as in the data preview of the view editor of SAP Datasphere.
Applied to my demo tenant with specified space and asset, this yields the following result for the metadata request:
In addition to the technical name and datatype information shown in the screenshot above, the metadata request also provides the corresponding business names via OData annotations.
Let us now retrieve the data itself. In this example, we will simply request all data and not yet take advantage of the OData parameters to filter the response based on our business need:
The Analytical API lets you consume Analytic Models. In contrast to views, every Analytic Model is by default exposed for consumption and thus accessible through the API.
The consumption is in analytical manner, i.e. the data is aggregated along all dimensions that are not part of the selection. The result is the same as in the data preview of the Analytic Model editor.
Overall, the Analytical API has the following benefits:
Let us now have a look on an Analytic Model in my demo tenant. The Analytical API can not only retrieve all dimensions from the Fact View, but also all attributes from the three associated dimensions.
The metadata response is similar to the one of the relational API, but provides the additional information of whether an attribute is a dimension or measure (via OData annotations, not shown in screenshot).
Let us now formulate an analytical request as well. In this example, we want to inquire the Gross Amount for every Sales Order Id and Sales Order Item combination. Under the hood, SAP Datasphere will then calculate Gross Amount based on its defined calculation logic and aggregation behaviour.
A great example of the OData API in action is the OData Connector for Microsoft Power BI, which has been developed to offer a seamless experience for consuming SAP Datasphere data in Power BI.
All details can be found in the following blog: Blog: OData Connector for PowerBI. Since recently, this connector supports access to both views and Analytic Models in SAP Datasphere.
The OData API is going to be continuously enhanced with more capabilities and enriched metadata information.
Top of our priority list for 2024 is to make the OData API directly accessible from the Analytic Model and view editor, thus providing users a fast, reliable and business-user friendly access to it (here). In addition, we plan to support Analytic Models with variables (here and here) and an authentication method for technical users (timeline to be announced).
I hope you enjoyed reading this blog and that it provided you with a nice overview to get started with the OData API yourself. As mentioned above, please refer to our official SAP Help documentation for further details (here).
For any feedback and questions, feel free to reach out.
I wanted to highlight that the OData API is one way to consume data from SAP Datasphere, and leveraging the OpenSQL schema via ODBC / JDBC is an alternative option (more details here). To replicate (mass-) data from SAP Datasphere to external environments, we recommend using Replication Flows (more details here).
The public OData API is a great proof point for this, as it now also provides access the Analytic Model. The latter is the cornerstone for business modeling in SAP Datasphere and provides harmonized, semantically-rich access to SAP data.
This blog provides an overview of the OData API and highlights the various endpoints with hands-on examples.
Content
Overview
The SAP Datasphere OData API lets you consume views and Analytic Models from SAP Datasphere, and consists of two services:
- Catalog Service: Lets you list all accessible spaces and assets that are exposed for consumption.
- Consumption Service: Lets you consume the data from views and Analytic Models.
The supported authentication method is a three-legged OAuth2.0 flow with type authorization_code. This also means that the consumer is authenticating with the standard SAP Datasphere user credentials and the respective data access authorizations are respected.
Moreover, the standard OData v4 query parameters $select, $filter, $orderby, $top and $skip can be used to refine the query.
All specifications are outlined in our SAP Help Documentation: Consume Data via the OData API.
Catalog API
The Catalog API lets you list all accessible spaces and assets that are exposed for consumption. For example, to list all exposed views and Analytic Models in a particular space, one can use the following request:
https://<tenant_url>.cloud.sap/api/v1/dwc/catalog/spaces('<space_id>')/assetsApplied to my SAP Datasphere demo tenant with specified space, this yields the following result:
Catalog API lists all exposed assets from a space
As we can see, the selected space contains 44 assets that are exposed for consumption. In addition, every exposed asset provides a reference to its relational or analytical endpoint.
Relational API
The Relational API lets you consumeviews of the data builder that are exposed for consumption. It consists of two requests:
- The Metadata request provides metadata information that can be used by the consuming application to optimize the consumption experience, such as showing the attributes’ business names instead of its technical names.
https://<tenant_url>.cloud.sap/api/v1/dwc/consumption/relational/<space_id>/<asset_id>/$metadata
- The Data request lets you retrieve the data itself. In case the view requires an input parameter, the latter can be specified in the request as well.
https://<tenant_url>.cloud.sap/api/v1/dwc/consumption/relational/<space_id>/<asset_id>/<asset_id>[<params>]
The consumption is in relational manner, i.e. irrespective of the selected measures and dimensions, no data is being aggregated but is retrieved in a row-by-row fashion. The result is the same as in the data preview of the view editor of SAP Datasphere.
Applied to my demo tenant with specified space and asset, this yields the following result for the metadata request:
Relational API: Metadata response of a view
In addition to the technical name and datatype information shown in the screenshot above, the metadata request also provides the corresponding business names via OData annotations.
Let us now retrieve the data itself. In this example, we will simply request all data and not yet take advantage of the OData parameters to filter the response based on our business need:
Relational API: Accessing all data from a view
Analytical API
The Analytical API lets you consume Analytic Models. In contrast to views, every Analytic Model is by default exposed for consumption and thus accessible through the API.
- The Metadata request provides metadata information that can be used by the consuming application to optimize the consumption experience, such as showing the attributes’ business names instead of its technical names.
https://<tenant_url>.cloud.sap/api/v1/dwc/consumption/analytical/<space_id>/<asset_id>/$metadata
- The Data request lets you retrieve the data itself.
https://<tenant_url>.cloud.sap/api/v1/dwc/consumption/analytical/<space_id>/<asset_id>/<asset_id>
The consumption is in analytical manner, i.e. the data is aggregated along all dimensions that are not part of the selection. The result is the same as in the data preview of the Analytic Model editor.
Overall, the Analytical API has the following benefits:
- Access to the Analytic Model as central modeling entity in SAP Datasphere.
- Access to all dimensions and attributes that are part of the Analytic Model definition, which usually includes multiple dimension views.
- Analytical response (see above). This allows applications to consume data from SAP Datasphere without carrying out additional computations.
- Access to complex calculations such as exception aggregations and restricted measures that are only available via Analytic Models.
Let us now have a look on an Analytic Model in my demo tenant. The Analytical API can not only retrieve all dimensions from the Fact View, but also all attributes from the three associated dimensions.
Analytic Model with three associated dimensions
The metadata response is similar to the one of the relational API, but provides the additional information of whether an attribute is a dimension or measure (via OData annotations, not shown in screenshot).
Analytical API: Metadata response from an Analytic Model
Let us now formulate an analytical request as well. In this example, we want to inquire the Gross Amount for every Sales Order Id and Sales Order Item combination. Under the hood, SAP Datasphere will then calculate Gross Amount based on its defined calculation logic and aggregation behaviour.
Analytical API: Aggregating Gross Amount by Sales Order ID and Item
Use-Case: Integration with MS Power BI
A great example of the OData API in action is the OData Connector for Microsoft Power BI, which has been developed to offer a seamless experience for consuming SAP Datasphere data in Power BI.
All details can be found in the following blog: Blog: OData Connector for PowerBI. Since recently, this connector supports access to both views and Analytic Models in SAP Datasphere.
Outlook
The OData API is going to be continuously enhanced with more capabilities and enriched metadata information.
Top of our priority list for 2024 is to make the OData API directly accessible from the Analytic Model and view editor, thus providing users a fast, reliable and business-user friendly access to it (here). In addition, we plan to support Analytic Models with variables (here and here) and an authentication method for technical users (timeline to be announced).
I hope you enjoyed reading this blog and that it provided you with a nice overview to get started with the OData API yourself. As mentioned above, please refer to our official SAP Help documentation for further details (here).
For any feedback and questions, feel free to reach out.
Note
I wanted to highlight that the OData API is one way to consume data from SAP Datasphere, and leveraging the OpenSQL schema via ODBC / JDBC is an alternative option (more details here). To replicate (mass-) data from SAP Datasphere to external environments, we recommend using Replication Flows (more details here).
- SAP Managed Tags:
4 Comments
