Hi all,
I have an single-entity OData service, with a very large (~40M rows) database behind. To improve performance, I forced my service to serve paginated data (max 50 items per page). When I navigate the endpoint url from a browser without $filter and $top, I get the first 50 rows as expected.
Ok, I open Excel 2013, choose "Data", "From Other Sources", "From OData Data Feed", then paste the endpoint URI, choose the Table (so I assume the Odata feed is well formed to Excel's eyes), then I chose to import data as a table, starting from $A$1.
Excel now stats to download data, page by page, until the memory blows.
If I limit may odata service to a maximum $skip value (for example 5000 items), Excel downloads the data and fails when he tries to get the data beyond the 5000th ("We couldn't get data from the Data Model. Here's the error message we got: Server Error: (400) Invalid request")
Similar behaviour If I try to download data through PowerPivot for Excel2013.
Is there a way to force Excel to download only the first N rows of each query?
Thanks in advance
I have an single-entity OData service, with a very large (~40M rows) database behind. To improve performance, I forced my service to serve paginated data (max 50 items per page). When I navigate the endpoint url from a browser without $filter and $top, I get the first 50 rows as expected.
Ok, I open Excel 2013, choose "Data", "From Other Sources", "From OData Data Feed", then paste the endpoint URI, choose the Table (so I assume the Odata feed is well formed to Excel's eyes), then I chose to import data as a table, starting from $A$1.
Excel now stats to download data, page by page, until the memory blows.
If I limit may odata service to a maximum $skip value (for example 5000 items), Excel downloads the data and fails when he tries to get the data beyond the 5000th ("We couldn't get data from the Data Model. Here's the error message we got: Server Error: (400) Invalid request")
Similar behaviour If I try to download data through PowerPivot for Excel2013.
Is there a way to force Excel to download only the first N rows of each query?
Thanks in advance
