Gateway not allowing retrieve more then 1000 records via REST API or connect via 32bit ADODB

[NickKindt]

Dear,

We've been recording data about our machines since 2020. So we have a massive amount of data that we visualise via power BI dekstop (for now).

We have been trying to move to the cloud since 3 months, so the cloud so that other users have the same tools but we are facing issues. All of our data is is stored in an SQL database, but our historical data is encrypted in that SQL database.
We can access that data via an 32-bit ADODB provider OR via the REST API.

The power-BI gateway does not support a 32 bit ADODB provider nor does it allow us to retrive more the 1000 records via the REST-API.
The power-BI desktop app does provide connection to a 32 bit ADODB provider AND does allow us to retrive more then 1000 records (via a list of api calls)

- We can't find any solutions for these issues?
- Is there any way (maybe via scripting) to publish the data with the desktop app instead of the gateway?
- If not, would it be a solution to run the the power bi server locally and use 'Power BI Desktop for Power BI Report Server'?
- If all these are not a solution, I guess one option would be to create a 2nd SQL database and decrypt the historial data into that 2nd database. The question remains, how can we do this with the minimul load on our local server?

Kind regards,

Nick

[Anonymous · Accepted solution]

Hi,@NickKindt 

Regarding the issue you raised, my solution is as follows:

1.First of all, you're right, but unfortunately the Power BI gateway only supports 64-bit drivers, which means you won't be able to use the 32-bit ADODB provider directly with the gateway.

Here is a screenshot of the official documentation:

Here are the relevant links:

Solved: 32 Bit ODBC driver with 64 Bit Power BI - Microsoft Fabric Community
 Install an on-premises data gateway | Microsoft Learn

2.Second, by implementing pagination, you can bypass the REST API limit of 1000 records per call.

Here is a screenshot of the  documentation:

Here are the relevant links:

How to query more than 1000 rows of a dataset – Data & Insights Client Center (socrata.com)

3.Then, if you have an Office 365 account, simply publish the report in your Personal Workspace or Office 365 Group/Workspace (if you have a Professional Edition). Then open the report in the Power BI service (App.powerbi.com), a process that doesn't require the use of a gateway. However, automatic data refresh is not supported by this method. You'll need to manually refresh and republish the report.
Here are the relevant links:
Solved: Is it possible to publish the reports in cloud wit... - Microsoft Fabric Community

4.Regarding what you mentioned, running Power BI Report Server on-premises and using "Power BI Desktop for Power BI Report Server" we think might be a viable solution, and this setup allows you to manage and distribute reports locally, which may be more in line with your current infrastructure.
Here are the relevant links:
Install Power BI Desktop for Power BI Report Server - Power BI | Microsoft Learn
 

5.Finally, to decrypt historical data with minimal local server load, you can use SQL Server's function DECRYPTBYKEY.
Here is a screenshot of the official documentation:

DECRYPTBYKEY (Transact-SQL) - SQL Server | Microsoft Learn

Of course, if you have any new ideas, you are welcome to contact us.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.