Access to Analytical Model + Input Parameter leads to "more records than the limit in MaxResultRecords"

[paulvatter]

Hi @OlafFischer

I have set up the connector with version 1.80 with the generic OData URL and it is working for a relational access to a dimension without issues.

After this new feature has been published today I wanted to retry the access to an analytic model including a input parameter for the currency conversion:

Data Modeling - Consume Analytic Models with Single-Value Variables via OData API
You can now consume analytic models containing variables that require single values via the OData consumption API.
See [Consume Data via the OData API](https://help.sap.com/docs/PRODUCTS/d4185d7d9a634d06a5459c214792c67e/7a453609c8694b029493e7d87e0de60a.html?locale=en-US&version=cloud).
Info only 2024.6

Unfortunately in PowerBI I always end with the error message above including the "The selection returned more records than the limit in MaxResultRecords".

I understand that this is a potential safety belt according to the help site "Consume Data via the OData API": By default the analytical responses always include all model measures and attributes, and so no aggregation is applied on the measures. In order to aggregate the results, specify the measures and attributes to be in the result set using $select, ensuring that at least one measure is specified among the selected fields.

But even after having played a bit with the filters in the browser and getting results e.g. for some subsets like a minimal drilldown:
/dwaas-core/odata/v4/consumption/analytical/POC_SALES/R_AM_POC_Sales/R_AM_POC_Sales/(TARGET_CURRENCY='CHF')/Set?$select=TRANSACTIONCURRENCY,ORDERQUANTITY,NETAMOUNT,Net_Value_IP_Curr,RoughCountDistinctLines
and even only a measure:
/dwaas-core/odata/v4/consumption/analytical/POC_SALES/R_AM_POC_Sales/R_AM_POC_Sales/(TARGET_CURRENCY='CHF')/Set?$select=ORDERQUANTITY

I still get the following error in PowerBI even for these URLs...

Details of the error message:
{"error":{"code":"400","message":"(#42709) The selection returned more records than the limit in MaxResultRecords : actual/query/default = 1015634/1000000/1000000\n(#42768) The Cube that exceeds the limits contains the following dimensions : Rows: [ADDITIONALMATERIALGROUP1, ADDITIONALMATERIALGROUP2, ADDITIONALMATERIALGROUP3, ADDITIONALMATERIALGROUP4, ADDITIONALMATERIALGROUP5, BASEUNIT, BATCH, BILLINGCOMPANYCODE, BILLINGDOCUMENTDATE, BUSINESSAREA, CALWEEK, CONTROLLINGAREA, CREATIONTIME, CUSTOMERACCOUNTASSIGNMENTGROUP, CUSTOMERGROUP, DELIVERYSTATUS, DISTRIBUTIONCHANNEL, DIVISION, EXCHANGERATEDATE, FISCALPERIOD, IP_Curr, MATERIAL, MATERIALGROUP, ORDERID, ORDERQUANTITYUNIT, PLANT, PRODUCT, PRODUCTGROUP, PROFITCENTER, TIME_CALDAY∞1, TRANSACTIONCURRENCY, YEAR, _H_VD_POC_∞2, CALDAY∞D1, CALMONTH, CALQUARTER, SALESORGANIZATION∞D2], Columns: [CustomDimension1]\n","@Common.numericSeverity":4}}

The analytic model has been deployed today...

Do you have any suggestion how to deal with that?

Thanks in advance and best regards
Paul
P.S.: The currency conversion is also not yet working via the input parameter in the browser, but this is a different topic ;)

[gustavokath]

Hi @paulvatter,

The issue you are facing is about the number of resulting records of your query. As the message you quoted from the SAP Help page states ideally you should reduce the number of selected columns when consuming this analytic model. This will result is less records allowing it to work. Your OData query should look like $select=measure1,measure2,dimension1,dimension2,....

We on the SAP Datapsphere APIs team are working as well on an optimized pagination logic for analytical request to reduce the probability of such response from the server.

About the parameters support on Analytic Model for single value variables via OData is a feature which will be delivered on release 2024.06. So, this is only expected to work after this version.

Let me know if you have any questions
Best Regards
Gustavo Kath

[paulvatter]

Bom dia @gustavokath

I mean, this is what I am doing, just taking a measure1 and mapping the input variable.
In my browser it is working:

On the PowerBI connector (see above) with the same statement not...

The tenant is on Version: 2024.6.65, so my assumption is it is already on the release?

Best regards
Paul

[gustavokath]

Hi @paulvatter,
Sorry, my bad - made some confusion on the deployment dates. Also, I got the understanding this was not working on the browser as well.

Is this not working due to the number of fields? Are you selecting only some columns in PowerBI. Could you try selecting a small amount of columns?

[paulvatter]

Hi @gustavokath
I have selected only one column, at least with the syntax which works in the browser…
Best regards Paul

[gustavokath]

Hi @paulvatter,

The root cause for the issue is that PowerBI ignores all OData Parameters like column selection, filter for the data preview.

A possible solution for this would be to start with a blank query based on the connector. Than create the query template with a query sample that works and later change it on the fly.

Unfortunately the connector as it is does not work as PowerBI ignores the selected columns. We will be working on the server side paging to improve this as well.

[OlafFischer]

Hi @paulvatter ,
please have a look at the following issue - I have outlined a sample on inserting a view directly via the advanced query editor of PowerBI.
Best regards, olaf