Using OData APIs for SAP Business ByDesign Analytics you can query and extract SAP Business ByDesign reports and data sources for analytical use cases.
In general SAP Business ByDesign (ByD) offers two REST APIs to extract analytical data:
- OData for Reports can be used to access ByD reports. The OData API for reports is tailored for remote access to pre-processed analytical data: A remote system extracts formatted analytical data pre-processed by ByD. The remote system may extract a ByD analytical model as a cube incl. semantics like key figures and language-dependent texts, ready to be processed by 3rd-party clients, or the remote system extracts analytics result sets incl. aggregations and key figures, ready to be displayed for users.
- OData for Data Sources is designed to extract analytical raw data. A remote system extracts ByD analytical raw data in flat tables, free of redundancies from ByD. The analytical raw data is then processed, combined and formatted in the remote system. Typically such remote systems are central business analytics application such as SAP Analytics Cloud.
You find documentations for both OData APIs in the ByD Help Center by searching with the key words "Retrieving Analytics Data Using OData".
Additionally ByD offers Soap services and OData APIs to access ByD business objects, but these APIs return deeply structured data and therefore requires some pre-processing and mapping before the data can be used in data warehouse solutions.
In the following this blog post focuses on
OData for Reports.
OData for Reports
Using
OData for Reports you can query and retrieve characteristics and key figures exposed in ByD analytical reports. This includes
- standard data sources and reports,
- data sources and reports created via SAP Cloud Applications Studio,
- custom reports based on standard or custom data sources, created as key user, and
- extension fields added by key user tools or SAP Cloud Applications Studio.
As result, ByD OData for Analytics provides you a highly flexible framework to query, read and analyze ByD business data, well-suited to serve your integration scenario.
The ByD OData API enables you to
- discover available ByD OData services for your business user,
- get metadata incl. technical field names, labels, data types and annotations,
- retrieve the data of ByD analytical reports.
You can control the response of your OData request by expanding the OData URI.
Below I will consider ByD OData rather from a business analytics point of view than from a REST point of view. Further information about REST, the OData-protocol in general and OData specifications can be found at
http://www.odata.org or
http://docs.oasis-open.org/odata.
For a quick start you can use the OData query builder: Open the
SAP Business ByDesign work center view
Business Analytics - Design Reports, mark the report of interest and click on "
Build OData Queries".
Discover OData Services and get Metadata
The goal is to get analytical reports that I can access and all meta data (characteristics, key figures, data types, ...) for those reports.
Get
OData containers for the logon user, or from a business user point of view: get all available work center for the logon user.
For containers with the following convention analytics data is available:
<shortened name of work center>_analytics.svc.
URL pattern:
<your system hostname>/sap/byd/odata/
Example:
https://my123456.sapbydesign.com/sap/byd/odata/
Get
OData entities for the logon user, or get all available analytical reports assigned to the logon user and visible in your Home work center.
URL pattern:
<your system hostname>/sap/byd/odata/cc_home_analytics.svc
Example:
https://my123456.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc
To retrieve the
entity metadata you can expand your OData URI by
/$metadata.
Get OData entities for a container, or get all available analytical reports per work center incl. all report meta data like properties (key figures, characteristics), data types, etc..
URL pattern:
<your system hostname>/sap/byd/odata/<shortened name of work center>_analytics.svc/$metadata
Example:
https://my123456.sapbydesign.com/sap/byd/odata/bpm_businesspartnerdata_analytics.svc/$metadata
Get
metadata for a specific entity, or get all metadata for a specific analytical report.
URL pattern:
<your system hostname>/sap/byd/odata/<shortened name of work center>_analytics.svc/$metadata?entityset=RP<report ID>QueryResults
Example:
https://my123456.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/$metadata?entityset=RPCRMSLOIB_...
Get Data of Analytical Reports
In all further examples I will skip the system host name. Let me list some simple examples before I go through the OData options step by step.
You can
fetch entity data by adding the ByD analytics report ID to your OData URL.
URL pattern:
<your system hostname>/sap/byd/odata/<shortened name of work center>_analytics.svc/RP<report ID>QueryResults
Example:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults
As result the system returns the first 50 entries of report "Sales Order Volume" (50 entries is the default page size).
Some more examples for ByD standard business partner reports:
Account collaboration data:
/sap/byd/odata/bpm_businesspartnerdata_analytics.svc/RPBPCSCARB_Q0001QueryResults
Account contacts data:
/sap/byd/odata/bpm_businesspartnerdata_analytics.svc/RPBPCSCONTB_Q0001QueryResults
... or little more sophisticated:
Assume you would like to analyze the consumption of customer contracts. For this scenario you create a joined data source with ByD service confirmation items and customer contract items and a ByD report to list and analyze service confirmation items aggregated by customer contract information.
Assume further the custom report has the report ID
ZABCDEF12345678ABCDEF.
Get service confirmations for contract CC1001:
/sap/byd/odata/cc_home_analytics.svc/RPZABCDEF12345678ABCDEFQueryResults?
$inlinecount=allpages&$select=FCISL_ACT_QTY_B,FCACCO_IAV_INV_QTY_BU,FCACCO_ITV_REQU_QTY_BU,FCZ0COUNT,CIBR_CUCO_ID,TIBR_CUCO_ID,CIBR_CUCO_I_ID,TIBR_CUCO_I_ID,CCCCO_DPY_MAINBUYERPTY,TCCCO_DPY_MAINBUYERPTY,CACCO_ITM_CREATIONDATE,CACCO_ITM_LASTCHANGEDATE&$filter=CIBR_CUCO_ID eq 'CC1001'
Get service confirmations joined with contract information, filtered by contract ID and contract item last changed date greater than 19th Feb. 2014:
/sap/byd/odata/cc_home_analytics.svc/RPZABCDEF12345678ABCDEFQueryResults?$inlinecount=allpages&$select=FCISL_ACT_QTY_B,FCACCO_IAV_INV_QTY_BU,FCACCO_ITV_REQU_QTY_BU,FCZ0COUNT,CIBR_CUCO_ID,TIBR_CUCO_ID,CIBR_CUCO_I_ID,TIBR_CUCO_I_ID,CCCCO_DPY_MAINBUYERPTY,TCCCO_DPY_MAINBUYERPTY,CACCO_ITM_CREATIONDATE,CACCO_ITM_LASTCHANGEDATE&$filter=CIBR_CUCO_ID eq 'CC1001' and CACCO_ITM_LASTCHANGEDATE ge datetime'2014-02-19T00:00:00'
Get service confirmations joined with contract information, filtered by contract ID and some extension field of type datetime:
/sap/byd/odata/cc_home_analytics.svc/RPZABCDEF12345678ABCDEFQueryResults?$inlinecount=allpages&$select=FCISL_ACT_QTY_B,FCZ0COUNT,CITM_POST_DT,CITM_CREATED_DT,CITM_CHANGED_DT,ZY86ABC123_ABC8D1B123&$filter=CIBR_CUCO_ID eq 'CC1001' and ZY86ABC123_ABC1234567 ge datetime'2014-02-19T00:00:00'
OData Parameter
The ByD OData URL consists of 4 components:
- your system host name,
- a shortened name of the ByD work center,
- a report ID, and
- query options.
URL pattern:
https://<your system host name>.com/sap/byd/odata/<shortened name of work center>_analytics.svc/RP<report ID>QueryResults?
<query options>
ByD OData supports the following query options:
- $select
Expansion: $select=<one or more specified characteristics separated by commas>
- $orderby (ascending)
Expansion: $orderby=<one or more specified characteristics followed by asc, separated by commas>
- $orderby (descending)
Expansion: $orderby=<one or more specified characteristics followed by desc, separated by commas>
- $totals
Expansion: $totals=<one or more specified characteristics separated by commas>
- $filter
Expansion: $filter=<specified characteristic> eq '<filter value>'
- $top
Expansion: $top=<specified number>
- $skip
Expansion: $skip=<specified number>
- $count
- $inlinecount
- $format
Select Option
The select option can be used to choose properties (key figures and characteristics) that shall be returned. Properties are specified using its technical names as provided in the entity meta data (e.g. CACCDOCTYPE for Journal Entry Type ID).
If no select options are specified in the URL, then the system returns all key figures and characteristics of the entity definition (report definition).
Example:
Select multiple characteristics:
&select=CCUSTOMER,TCUSTOMER,CCUCOREF,TCUCOIUUID,CPOSTDATE,CACCDCHUID,CSETOFBKS,CSRCNTHREF,CITM_ID
Please note that key figures are aggregated according the selected characteristics.
Example:
You query sales order items and select the key figures Number of Sales Order Items and Net Value, and the characteristics Product Category:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPR_CATCP_N,TIPR_CATCP_N
As result the system returns one entry per product category with aggregated sales order net values and the number of sales order items per product category:
The OData interface returns at maximum 50 properties (corresponding to a report with 50 columns).
If an entity has more properties defined, then specifying select options in the OData URL is mandatory. However, in this case I would recommend to copy the report and reduce the number of properties to 50.
Filter Option
Filter functions like "
startswith" are supported for characteristics that are configured as "Any Selection" in the report. In particular properties that are annotated with
"sap:filter-restriction="multi-value"" in the OData metadata do not support functions.
Examples:
- Filter by customer ID and set of books ID:
$filter=CCUSTOMER eq '1029754' and CSETOFBKS eq '4010'
- Filter by posting date (type date time):
$filter=CPOSTDATE ge datetime'2014-01-14T00:00:00'
Filter by initial values
With ByD 1611 the OData API supports filtering by initial/null values: not assigned/null values has to be passed as filter condition in the format
‘” “’ (double quotes separated by space – passed within single quotes), for example:
CCUST_ABC eq ‘” “’.
Examples:
…/sap/byd/odata/cc_home_analytics.svc/RP…QueryResults?$select=CDPY_PROS_CITY&$filter=CDPY_PROS_CITY%20eq%20'"%20"'
…/sap/byd/odata/cc_home_analytics.svc/RP…QueryResults?$select=CDPY_PROS_CITY&$filter=CDPY_PROS_CITY%20ne%20%27%22%20%22%27
AND/OR Operators and Intervals
The AND operator is allowed for "between" use cases only like
<characteristic> ge <value 1> and
<characteristic> le <value 2>.
You can use the OR operator to include two values, for example:
$filter=CACCDOCTYPE eq '1000' or CACCDOCTYPE eq '4000'
Excluding two values using the OR operator (for example:
$filter=CACCDOCTYPE ne '1000' or CACCDOCTYPE ne '4000') is not possible.
Combining two different characteristics using the OR operator is not supported.
You can combine multiple intervals by using brackets, for example:
$filter=PARA_COMPANY eq '1000' and PARA_SETOFBKS eq '7000'
and ( PARA_FISCYEARPER ge 52016 and PARA_FISCYEARPER le 72016 )
and ( PARA_POSTDAT ge datetime'2016-06-25T00:00:00' and PARA_POSTDAT le datetime'2016-07-04T00:00:00' )
Boolean values
Example:
$filter=DebtorDoubtfulIndicator eq false
$filter=DebtorDoubtfulIndicator eq true
IDs and codes
Example: Filter by token (customer ID and set of books ID):
$filter=CCUSTOMER eq '1029754' and CSETOFBKS eq '4010'
Date and Time
Filter by date (OData type
Edm.DateTime😞
$filter=CPOSTDATE ge datetime'2014-01-14T00:00:00'
Filter by time:
$filter=CLOGON_TIME ge time'PT00H00M32S'
Note:
You can filter by date-characteristics and time-characteristics only. Filtering by timestamps is not yet supported.
Date characteristics are exposed as OData type
Edm.DateTime and require the literal form
datetime'yyyy-mm-ddT00:00:00' in filter expressions.
Some date characteristics that correspond to a timestamps in the business object model are converted for usage in ByD analytics: The timestamp is converted to UTC and time is truncated afterwards.
For example:
- I create an invoice via UI as an Australian employee in time zone UTC+9.30, which means I see on the UI, that the invoice has been created at 2017-01-31 01:05 (my Australian time zone).
- I read the invoice via web service or SAP Cloud Application Studio: The web service returns the CreationDateTime in UTC: 30.01.2017 14:35:46.0000000Z.
- I check the invoice via UI using ByD reports: Created On is 2017-01-30 which is the creation date time in UTC with truncated time.
- I read the invoice using OData for Analytics: Created On ID (CDOC_CREATED_DT) is 2017-01-30T00:00:00.
Any filtering using OData has to take into account the UTC conversion and time truncation:
- The OData with filter $filter=CDOC_CREATED_DT ge datetime'2017-01-30T00:00:00' returns the invoice above.
- The OData with filter $filter=CDOC_CREATED_DT ge datetime'2017-01-31T00:00:00' does not return the invoice, because the filter date is greater than the invoice date in UTC.
- The OData with filter $filter=CDOC_CREATED_DT ge datetime'2017-01-30T18:45:00' does return the invoice, because the time of the filter parameter is not taken into account.
Report Characteristics and Report Selection Parameter
When retrieving OData metadata you may observe that some report characteristics are mentioned twice in the entity metadata:
- as property referring to a report characteristic (e.g. Property Name="CIP_SALES_UNIT" sap:label="Sales Unit ID") and
- as property referring to a report selection parameter (e.g. Property Name="PAR_SEL_IP_SALES_UNIT" sap:label="Sales Unit").
The properties referring to report characteristics correspond to the characteristics as defined in the
Report Wizard in step "
3 - Select Characteristics".
The properties referring to the selection parameters are indicated as
sap:selection="true" together with the selection type (for example
sap:selection-type="multi-value") in the property attributes. The properties referring to selection parameters correspond to the report characteristics enabled for value selection in the
Report Wizard in step "
4 - Characteristic Properties".
You can use both type of properties for filtering, but filtering using properties referring to the selection parameters is recommended for the purpose of runtime and memory consumption optimization, because the filter parameter values are applied on the database level always.
Filtering using properties referring to report characteristics might be applied on the selection result only, depending on the overall report setup.
Report Initial Selection Default Values
Initial Selection Default Values are default values for selection parameter defined in the
Report Wizard step "
5 - Define Variables", button
Maintain Default Values.
Report Initial Selection Default Values can be used to tailor ByD reports for OData consumption to simplify filtering in OData requests and to optimize runtime and memory consumption.
Report Initial Selection Default Values can be overwritten or refined using OData filter parameter. If no corresponding filter parameter are provided in the OData request, then the system applies
Initial Selection Default Values as provided in the report wizard.
Report Fixed Value Selections
Using the
Report Wizard step "
4 - Characteristic Properties" you can
Set Fixed Value Selections for some characteristics.
Note: Report
Fixed Value Selections and
Initial View Default Selections are not the same. The
Initial View Default Selection can be overrules using OData filter parameter whereas
Fixed Value Selections cannot be changed in OData requests.
Therefore report
Fixed Value Selections are effective possibilities to restrict access to ByD data using OData for Analytics.
Report Default Selection Variants
The Report
Default Selection Variant is a report selection variant configured as default by a key user or business user in the report UI.
Report
Default Selection Variants as well as all non-default report selection variants are not taken into account by the OData API.
Personalized characteristics and selection parameter
Personalized characteristics and Personalized selection parameter are characteristics added by a key user or business user in the report UI using button "
Added field".
Personalized characteristics are exposed via OData as filterable characteristic, but not as property referring to the selection parameter (hierarchical parameters and relative selects are not supported as well).
Personalized characteristics that are added as key user using work center
Business Analytics are available to all users.
Personalized characteristics that are added as business user (not using work center
Business Analytics) can be retrieved by the same user only.
Furthermore these business user characteristics are not exposed if you are using entity set
ana_businessanalytics_analytics.svc as well.
Filter in financial reports
Most financial reports and data sources have mandatory selection parameters like company and set of book.
In particular all reports based on financial balance data sources (for example report Trial Balance based on data source FINGLAU01) require to set the mandatory default selection values or filter parameters: company (COMPANY), set of books (SETOFBKS), fiscal year (FISCYEAR) and fiscal period (FISCPER). The report returns no data if those selection/filter values are not provided.
For further details and examples regarding filter and selection parameter please check my blog post
Using Filter and Selection Parameter in ByD OData for Analytics.
Order By Option
The options
$orderby=<characteristics name> asc or
$orderby=<characteristics name> desc enable you to sort your query result ascending or descending with respect to a specific characteristics. You can sort by characteristics, but not by key figures.
Example:
Select sales order net values per product category sorted by the product category ID and the product category name (ok, the example sorting doesn't make much sense, but it shows how it works):
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPR_CATCP_N,TIPR_CATCP_N&$orderby=CIPR_CATCP_N%20desc,TIPR_CATCP_N%20desc
Top, Skip Option (Paging)
The
$top option specifies that only the first n records will be returned.
The
$skip option specifies that the result shall not include the first n entities.
Both query options together can be used to process a paging through the server side hit list.
If no paging options are specified, ByD returns the first 50 entries due to default page size = 50.
Example:
Select the 3rd page of 10 entries (skip 20 and return 10), sorted by characteristic CIPY_BUY_CNTCD_N:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=CIPY_BUY_CNTCD_N,FCITEM_COUNT,FCITV_NET_AMT_RC,
CIPY_BUYER_PTY,TIPY_BUYER_PTY,CIPR_CATCP_N,TIPR_CATCP_N
&$orderby=CIPY_BUY_CNTCD_N%20asc&$skip=20&$top=10
Totals Option
Using the totals option you can add entries with totals to the OData result.
Example:
The URL
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=CIPY_BUY_CNTCD_N,FCITEM_COUNT,FCITV_NET_AMT_RC,
CIPY_BUYER_PTY,TIPY_BUYER_PTY,CIPR_CATCP_N,TIPR_CATCP_N,
TotaledProperties&totals=CIPY_BUY_CNTCD_N,CIPY_BUYER_PTY,CIPY_BUY_CNTCD_N
&$orderby=CIPY_BUY_CNTCD_N%20asc
results in the additional totals entries (marked orange):
Other Options
Count
Returns the total number of entries.
Example:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults/$count
Inline Count
Returns the number of entries included in the query result.
Example:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPY_BUYER_PTY,
TIPY_BUYER_PTY,CIPR_CATCP_N,TIPR_CATCP_N,CIPY_BUY_CNTCD_N
Format
Allows you to specify the result format:
Example:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPY_BUYER_PTY,
TIPY_BUYER_PTY,CIPR_CATCP_N,TIPR_CATCP_N,CIPY_BUY_CNTCD_N&$format=json
ID Access
Access an entry with a specified ID. IDs are provided in query results.
Example:
/sap/byd/odata/pmm_productdata_analytics.svc/RPSERVICE_Q0002QueryResults
('%7CCCHG_DATE%3D03/06/2015%7CCSERV_INT_ID%3DMCD-ICSP-1000%7C')
URL encoding for special characters and spaces
Some applications or browsers may require URLs without spaces, unsafe characters and special characters outside the ASCII character-set. In this case replace those characters by the correctsopnding %-syntax. You find more details on
HTML URL Encoding Reference.
Example:
Readable version:
$filter=CIBR_CUCO_ID eq '1001'
with %-Syntax:
$filter=CIBR_CUCO_ID%20eq%20%271001%27
How to set the language of the data returned?
Using parameter sap-language you can specify in which language descriptions and texts shall be returned.
Parameter:
Example:
myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPBUPCSD_Q0001QueryResults?$select=TMAIN_ADDRESS,CPRJTN_ROLE_CODE,TPRJTN_ROLE_CODE,CSTATUS,TSTATUS,CBP_UUID,CLEGAL_FORM,TLEGAL_FORM&sap-language=fr
Authorization for OData Access
Similar to the ByD UI every user has access to analytical reports that are assigned to work center views, which are covered by the business user access rights.
Technical users (for example communication users) cannot be used for OData access.
Steps to create a user with OData for Analytics authorization:
- Create a ByD analytics report and assign the report to a ByD work center view.
- Create a ByD employee or service agent (for service agents additionally request a ByD user).
- Assign the work center and work center view which contains the analytical report to the access rights of the employee or service agent.
The business user might get a specific security policy assigned that prevents password aging and hence necessity to change the password periodically.
If SAML SSO is configured on the tenant, and OData shall be accessed using basic authentication, SAML needs to be explicitly disabled for an OData call.
Example:
/sap/byd/odata/srm_supplierbase_analytics.svc/RPBUPSPP_Q0001QueryResults?saml2=disabled
High Volume Data Sources
Using ByD OData interfaces means to extract and transfer data via internet and hence every integration architecture should be carefully planned with regards to data volume and message sizes.
Any approach based on a "select-all" will not be a sustainable integration setup and reach memory or timeout boundaries at some point in time. You should make sure that the total amount of data loaded from the ByD database and the size of OData responses (xml or json message sizes) are reasonable sized and remain stable over years, even if the total number of database records may grow fast.
In the following I consider some possibilities to control the data fetched from the database and message sizes.
Extract delta data or limit the data extracted to a specific time frame
It is always recommended to fetched data using filters resulting in stable max. volumes. That means instead of reloading all data on every replication event, load missing data by filtering data created or relevant in a specific time period (for example filter by creation date, change date, posting date, fiscal period, or similar).
You can get information about filterable properties from the OData entity metadata, which contains all relevant information to assemble the ByD OData URL incl. property names, labels and filterable properties referring to characteristics and report selection parameter.
Note: Filtering using properties referring to the selection parameters is recommended for the purpose of runtime and memory consumption optimization, because the filter parameter values are applied on the database level always. Filtering using properties referring to report characteristics might be applied on the selection result only, depending on the overall report setup.
Examples:
- Filter by selection parameter invoice date:
$filter=PAR_SEL_DOC_INV_DATE ge datetime'2016-12-01T00:00:00'
- Filter by selection parameter item creation date:
$filter=PAR_SEL_ITM_CREATED_DT ge datetime'2017-01-04T00:00:00'
Note:
Combined and joined data sources may not contain all business documents due to its join conditions.
Example: When you cancel a time recording, the corresponding record will no longer be returned by the combined data source
Employee Times (HCMTLMU01).
Pre-filtering using ByD report Fixed Value Selections and Initial Selection Default Values
When creating a custom report using the ByD Report Wizard, you can define
Fixed Value Selections and
Initial Selection Default Values.
Both filter settings are applied on the database and therefore can be used to optimize runtime and consumption of system resources efficiently. Furthermore
Fixed Value Selections and
Initial Selection Default Values support
relative selects, for example "
Current financial period" and "
Current Month to Date".
Both settings are well-suited to tailor your ByD report for OData consumption in particular in case of high volume data sources.
Extract aggregated data with constant numbers of returned aggregated records
By choosing fields to be selected in the OData request, you implicitly define the level of aggregation in ByD before the data is transferred to the OData consumer. Using this aggregation and choosing the selected fields carefully, you can very efficiently reduce the number of records returned, or even reach constant numbers of records returned for growing data sources.
Basically requesting aggregated data is a possibility to optimize runtime, memory consumption and the size of transferred data volume. In general aggregations are done on database level reducing the impact of growing data source volumes.
However, aggregation still requires to select all entries and hence runtime and memory allocation increases over time.
Please note that restrictions in key figures are applied on OLAP level and hence aggregation might not be possible database level, but lead to an additional aggregation on OLAP level. Therefore using aggregation for the purpose to solve a data volume issue requires a very detailed analysis of the report setup. I would rather recommend to use OData filter and report selection parameter to optimize runtime and memory consumption.
Check calculated and restricted key figures: beyond characteristics listed in the select statement, all characteristics used for calculations and restrictions are loaded as well and significantly reduce the performance of aggregation.
Example:
You query sales order items and select the key figures
Number of Sales Order Items and
Net Value, and the characteristic
Product Category:
/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_MQ0001QueryResults?$inlinecount=allpages&$select=FCITEM_COUNT,FCITV_NET_AMT_RC,CIPR_CATCP_N,TIPR_CATCP_N
As result the system returns one entry per product category with aggregated sales order net values and the number of sales order items per product category
Select required fields (select-parameter)
It is always better to fetch only required characteristics and key figures from the data source. For this purpose you can create custom reports for OData usage with minimum needed fields and characteristics or you can use the OData $select-parameter to specify the fields retrieved from the data source.
It does not make a difference if you apply the $select option on a big standard report with 50+ characteristics and key figures, on a small custom report built on a big standard data source, or on a small custom report build on a small custom data source. The system selects the specified fields from the database only. But: If key figures use additional characteristics, these are selected as well implicitly!
If no OData $select option is provided, then the system returns all key figures and characteristics of the entity definition (report definition); at max. 50 characteristics and key figures.
Example:
Select multiple characteristics: &select=CCUSTOMER,TCUSTOMER,CCUCOREF,TCUCOIUUID,CPOSTDATE,CACCDCHUID,CSETOFBKS,CSRCNTHREF,CITM_ID
Paging (top/skip parameters)
“Top” gives that many specific number of records. But this has nothing much to do with performance as this is calculated at runtime after fetching all data and taking into account report calculations, aggregations, etc.
This means paging using top/skip mainly has an effect on the transferred data, but not on the allocated memory and server-side runtime.
Performance impact of filter and selection parameter
In general you can change and enhance ByD reports from multiple point of views:
- Using the Report Wizard in work center Business Analytics you can create or changing the basic report setup. The characteristics and key figures selected in the Report Wizard are called Initial View. The selection parameters and selection parameter values that are configured in the Report Wizard are called Initial Selection and Initial Selection Default Values. Furthermore you can define Fixed Value Selections.
- If you open a report as key user or business user you have the possibility to add characteristics and key figures, and you can assemble a personalized report view and personalized selection variant. Those added report characteristics and report selection parameter are called Personalized Characteristics and Personalized Selection Parameter.
- Using the option Edit With Web Browser in work center Business Analytics you have the possibility to add characteristics and key figures, and assemble a report view. Furthermore you can enter selection parameter values and filter values and save them as selection variant. The report view as well as the selection variant can be saved for later reuse by all users. Additionally you can mark report views and selection variants as Report Default View and Report Default Selection Variant.
Please check the following possibilities to setup filter and selection parameter for OData for Analytics requests and take into account its impact to runtime and consumption of system resources:
- Fixed Value Selection:
- Fixed Value Selections are applied on database level.
- Relative selects are supported.
- These selection parameter values are applied for all OData requests. Using OData filter parameter you cannot overwrite Fixed Value Selections.
- Initial Selection Default Values:
- Selection parameter values are applied on database level
- Relative selects are supported
- These selection parameter values are applied for all OData requests. Using OData filter parameter you can overwrite initial selection default values
- Report Selection Variants and Report Default Selection Variants:
- Selection parameter:
- Selection parameter values are applied on the DB
- Relative selects are supported
- Selection parameter values overwrite initial selection default values (2) if you open the report via UI
- Default selection variants are not taken into account for OData requests
- User-specific or non-default selection variants are not taken into account for OData requests
- Filter parameter:
- Filter parameter are applied on the selection result (Selection parameter values are first applied on database level. After that, OLAP applies the filter parameter values on the database selection result)
- Filter parameter values are not taken into account for OData requests
- OData Parameter $filter using properties referring to selection parameters (property attribute sap:selection="true"😞
- Example: Filter by creation date: $filter=PAR_SEL_ITM_CREATED_DT ge datetime'2017-01-04T00:00:00'
- Filter parameter values referring to selection parameters are applied on the database level
- These OData filter parameter values for selection parameter can be used to overrule initial selection default values (2). Selection parameters that are not provided as filter parameter in the OData request are still applied using the initial selection default values.
- Relative selects are not supported
- OData Parameter $filter using properties referring to filterable characteristics:
- Example: Filter by creation date: $filter=CCREATIONDATE eq datetime'2015-06-25T00:00:00'
- Selection parameter values of (2) and (4) cannot be overruled. Initial selection default values (2) and OData filter referring to selection parameter (4) are applied first on database level. OData filter parameters referring to filterable characteristics (5) might be applied on the database selection result or directly on database level depending on the overall report setup. For runtime and memory consumption optimization it is better to use initial selection default values (2) and OData filter referring to selection parameter (4).
- Relative selects are not supported
- OData Parameter $select for characteristics and key figures:
- Example: $select=FCDEBIT_CURRCOMP,FCCREDIT_CURRCOMP,FCBALANCE_CURRCOMP
- The OData select parameter does not reduce the number of records but the number of fields fetched from database
- $select parameter values are applied on the DB directly. In general fields not listed are not fetched from DB and hence you can use this parameter to reduce memory consumption. However, characteristics used by key figures are selected additionally as well, even if not listed in the OData $select parameter.
For further details and examples regarding filter and selection parameter please check my blog post
Using Filter and Selection Parameter in ByD OData for Analytics.
Some Trouble Shooting...
Error message: “Program error in class CL_RSBOLAP_QV_RESULT_SET method : TOO_MANY_DRILL_DOWN_OBJECTS”
=> You report has too many properties; the report must not have more than 50 properties. Reduce the number of requested properties using parameter
$select.
Error message: "Ressource für das Segment 'RP<report ID>QueryResults' nicht gefunden"
=> Logon user does not have authorization to access the report.
Error code: http 404
=> IE Version (better use Firefox) or user/password.
Meta data for report not shown, even if report can be used.
=> (Re-)assign report to work center.
Why does the oData service return 50 entries only?
=> 50 entries is the default if the parameter $top is not specified in the oData URL.Use $top, $skip and $inlinecount=allpages to
specify the number of entries to be returned and for paging.
Why does the oData service return less entries than expected, even if I didn't set any filter?
=> Report default selections cannot be overwritten with oData-based retrieval => Copy report w/o default selections.
MS Internet Explorer renders the OData result as feed:
You can turn off the RSS feed reading view on the internet explorer by the following steps:
- Open Internet explorer tools menu > select internet options > go to the content tab
- Click on Settings of the "Feeds and Web Slices"-section
- Uncheck the check box "Turn on feed reading view"
- Click on ok-button and restart the Internet Explorer
Error message 401 Unauthorized using SVC-documents in Microsoft Visual Studio or Excel-PowerPivot:
The OData metadata level service URL call requires “/” at the end, e.g.
https://myXXXXXX.sapbydesign.com/sap/byd/odata/crm_serviceentitlements_analytics.svc/
If not appending “/” after .svc, the system returns an 401 authorization error, since it was not getting the correct path and hence cannot not authenticating the web service call.