Technical Articles
SAP Ariba Analytical Reporting API: Part 2 – SAP Ariba Reporting API Structure
In this post, I will be covering the second part of the three part series on SAP Ariba Analytical Reporting API.
Understanding the basic data model of Analytical reporting database, Reporting API structures and its limitation will ensure a smooth project implementation of data extraction utilizing this API.
Below are the summary for each of the part:
- SAP Ariba Analytical Reporting Data Model
I will briefly cover the Reporting Dimension(s) and Fact(s) classes as Database object, Hierarchies, References, and ASM Data load. - SAP Ariba Reporting API Structure
I will cover the Synchronous and Asynchronous Method of data extraction, getting Metadata, using OOTB and custom ViewTemplates, Filtering data, using available Parameters, and reading the Rate limit. - Best practice: SAP Ariba Analytical Reporting API
I will discuss the typical use case of the API along with Q&A, FAQ, and Tips.
Part 2: SAP Ariba Reporting API Structure
While this post is focused on Analytical Reporting API, the general structure of SAP Ariba Reporting API is the same for all 3 branches of Reporting: Analytical Reporting API, Operational Reporting API for Procurement, and Operational Reporting API for Sourcing.
There are two methods of getting Data:
- Synchronous Method
Instant result, data is batched and paginated with PageToken, recommended for a very small subset of data. Client application submit a request and wait for the response - Asynchronous Method
Request for data is submitted via scheduling a reporting job. Result data output is in ZIP files, recommended for a larger subset of data.
Client application submit a job request to a queue and periodically polls the status of the job. Upon completion, client application downloads the results one file at a time.
Pagination of jobs are supported. I will discuss more on this at the Real life example section.
Before continuing with the Reporting API structure, I need to mention that there are limitations imposed on Reporting APIs:
- Time span Limit
The number of transactions posted within a certain time period may not exceed a certain maximum.
Timespan for data is 1 year on Analytical, Procurement, and Sourcing Reporting.For example:
Using filter TimeCreated >= 1 January 2021 and TimeCreated <= 1 February 2022 will not work as the timespan is more than 1 year. - Volume Limit
Total result of data retrieved shouldn’t exceed a certain size.Analytical Reporting API:- Asynchronous is 50,000 records or 10 files containing 5000 records per file.
- Synchronous 50,000 records and 50 records per page.
Procurement and Sourcing Reporting API:
-
- Asynchronous is 10,000 records or 10 files containing 1000 records per file.
- Synchronous is 10,000 records and 40 records per page.
- Rate Limit
The number of API calls made every second/minute/day/week/month may not exceed a certain limit.
Same rate for Analytical, Procurement, and Sourcing Reporting API:
-
- Synchronous end point: 1 per second, 3 per minute, 50 per hour, 300 per day.
- Asynchronous Job Post request: 1 per second, 2 per minute, 8 per hour, 40 per day.
- Asynchronous Job Status and download: 2 per second, 20 per minute, 200 per hour,1000 per day.
- viewTemplate management request: 1 per second, 10 per minute, 100 per hour, 500 per day. Note: Rate Limit information are returned with every call under the Response Header section. Also, please check the latest rate limit for each end point in Developer Portal for your data center as it could change when this blog is published.
Limitations on Reporting API are collectively exhaustive. If one of the limit is met, Reporting API will not return data.
Structure and Concept
Reporting API utilize the concept of view Template and Metadata.
Key facts about viewTemplate:
- Reporting viewTemplate specifies the types of records to report on (documentType), the reporting fields to be included as the response (selectFields), and the reporting filter to be used to select relevant records for inclusion or exclusion (filterFields).
- Out of the box (OOTB) viewTemplates are provided as System-generic viewTemplate. OOTB viewTemplates will only have standard fields.
- Custom viewTemplates can be created and patched for specific selectFields and filterFields. Any custom fields will not exist in OOTB views but they can be added as part of selectFields in a custom viewTemplate, as long as the custom field itself is a persistent, non-derived/computed field.
- Each viewTemplate have a 1:1 relationship with documentType. If you desire data from multiple documentType such as ContractWorkspaceFact and SupplierDim (for example: Contract Title and Company name), separate calls to the respective viewTemplate are needed. One to the view with documentType ContractWorkspaceFact and One to the view with documentType SupplierDim. Any join between the two viewTemplate needs to be completed on your end. More detail on viewTemplate structure can be found HERE
Metadata
Reporting API Metadata is used to gather information on specific documentType, selectFields, filterFields, PrimaryKeys, and ForeignKeys. Utilizing Metadata, Data definition of specific select or filter fields such as data type, size, precision, and scale become visible. This data definition is important when you plan to store the dataset in your own database as part of your datawarehousing project. Another purpose of Metadata is when we need to create a custom viewTemplate with specific selectFields and filterFields on specific documentType.
When invoking Metadata end point, several parameters are supported. Please review the documentation on Analytical Reporting Metadata for API HERE
⚡ Check this blog post by Antonio Maradiaga for creating ER diagram utilizing Analytical Reporting API Metadata.
Next, I will be sharing several video recording on how to invoke Metadata, getting viewTemplates, creating custom viewTemplate, getting data via synchronous and asynchronous call.
I assume that one reading this post would already know the process of creating application from SAP Ariba developer portal for Analytical Reporting API and have the required apiKey and secret to generate token.
⚡ Check another blog post by Antonio Maradiaga on how to create application and consume SAP Ariba APIs.
Video 1 – Invoking metadata:
Video 2 – Getting viewTemplates, Creating custom viewTemplate, and Getting data via the Synchronous method:
Video 3 – Getting data via the Asynchronous method:
Conclusion
With Metadata end point, we can retrieve all available select and filter fields available on a specific documentType in SAP Ariba Analytical Reporting API. Utilizing the Metadata, we can create a custom viewTemplate with the required select and filter fields. Once the viewTemplate is set, data can be retrieved via Synchronous and Asynchronous method, depending upon your project requirements.
On the part 3, I will discuss the best practice on SAP Ariba Analytical Reporting API along with some FAQ and Q&A section. Please feel free to post your comments on the section below and submit questions via this link
Hi Yos,
is it possible to obtain more information about the filter. I noticed you passed it as a parameter as per your needs (in this case a specific project id). I have a different need to only include certain event states for my API. I worked on this earlier today but wasn’t able to get it working.
I’d appreciate any information you can provide some information on how the filter parameter functions.
Would like to be able to filter specific EventStates below (RFXDocument is the documentType, I'd like to exclude ArchivedState). Would it be possible to do this?
{
"name": "EventState",
"type": "int",
"size": null,
"allowedValues": [
{
"key": "11",
"value": "FailedState"
},
{
"key": "12",
"value": "ArchivedState"
},
{
"key": "13",
"value": "AwardedState"
},
{
"key": "14",
"value": "SourcingRequest"
},
{
"key": "-1",
"value": "NotCreatedState"
},
{
"key": "0",
"value": "NewState"
},
{
"key": "1",
"value": "SubmittedState"
},
{
"key": "2",
"value": "ScheduledState"
},
{
"key": "3",
"value": "PreviewState"
},
{
"key": "4",
"value": "PrebidReviewState"
},
{
"key": "5",
"value": "OpenState"
},
{
"key": "6",
"value": "PendingSelectionState"
},
{
"key": "7",
"value": "ClosedState"
},
{
"key": "8",
"value": "CancelledState"
},
{
"key": "9",
"value": "PausedState"
},
{
"key": "999",
"value": "SuspendedState"
},
{
"key": "10",
"value": "ReviewState"
}
],
"isCustomField": false,
"precision": null,
"scale": null
}
Pierre Collette In your case, what you need to do is to utilize the operators in the filters.
So set up the filters like this:
I will discuss more about filtering as the default of filters are "and" between them, so if there's multiple filters, it can only be filter1 statement is true and filter 2.
Hi Yos,
Thanks for your answer. I'm able to create the template using POST with the added filterExpression you provided however, when I use the Get View Template template (specifying the Template I created), I receive the following error:
Pierre Collette
Could you try the operator "NOT IN"? If it is still giving you an error,
please submit a service request to SAP Ariba Support team with API selected as the queue.
Our team will take a look at this from the back end to see if there's anything wrong in the query.
Hi Yos,
Thanks for the response, same error with "NOT IN".
I will submit a SR.
Thanks,
Pierre
Just an update, issue was the quotes. It required an integer value without the quotes.
ahh yes. Thanks Pierre Collette . I will make sure to discuss this on the 3rd part of the blog.
Hello Yos,
Thank you for such a nice blog. It helped us a lot. Keep posting 🙂
Best Regards
Rajan
Hi! Would you specify, in a Rate Limit, do you count unsuccessfull queries?
For example, Synchronous api has restrictions: 1 per second, 3 per minute, 50 per hour, 300 per day.
Let's suppose I perform one query, got message "API rate limit exceeded" and then tried to execute it once again (without delay), and so 300 times in a row. Will I exhaust my day limit or not?
Leonid Borchuk Consider this scenario:
Say you run the call 4 times in 1 minute. The 4th call will return 429 error - API rate limit exceeded.
The rate limit of the hour and day will not be affected.
But if you run a call that returns error/failure(400,500,etc), such as missing realm, wrong documentType or invalid view, each call is counted to the existing limit.
Hello,
I am having this issue: approx. month ago I created a contract workspace and uploaded a document, then I was trying to find the one by creating a view with the filter by ProjectId and with the document type DocumentFact. Once the view was created, I was trying to find it with the GET request. The return list is empty. Today I created another view with the same body and I am able to find that contract workspace document.
I tested the case today as well: created new CW, uploaded some documents and I was trying to get them using an API, however, I just received an empty list.
It would be helpful if you have any hint or knowledge where the problem could be.
Thank you and kind regards,
Amna
Amna Mrkalic please review the first part of the blog.
Analytical Reporting API is NOT real time data. Scheduled DataLoad must be completed for newly added data to be reflected.
Hello Yos
I have a problem with a customview on a custom entity. This custom entity is linked to a project, but we are unable to add the projectid in the customview because it cannot be selected as a select. Is there a way to build a customview which would incorporate the (hidden) field containing the projectID (parentprojectid)?
The (foreign) key must be in the system otherwise it would be impossible to link the customentity in the application itself.
Thank you in advance.
Guy
Hi Guy,
If the metadata of the entity is not exposing the field, then it is not possible to add it to the custom view.
Is the value available from different documentType? that might be the way to go.
From the API perspective, unexposed field can be a limitation for reporting API.
-Yos-
Hi Yos
Thank you for the quick reply. We looked for references to the custom entity in other entities, but unfortunately it's not referenced anywhere else. We'll have to look for another way to get the projectID exposed in the entity.
Kind regards
Guy