This guide covers known issues and limitations with using the Supermetrics API connections to PowerBI.
For some queries, Power BI was only able to process 20000-30000 rows without running into issues. There's no clear reason why this is happening, it would just sometimes timeout during the processing if the rows amount was over 20000. The issue has been reported to Microsoft to see if this can be addressed. We're also looking into the option of building a dedicated PowerBI integration for the Supermetrics API.
To work around the problem, you can create multiple queries to cover the full range of data, splitting them up by date.
Scheduling Issues and Workarounds
PowerBI can refresh web connectors, like the Supermetrics API, when scheduled to the server. However, there are issues with the scheduling of PowerBI reports to refresh automatically which are caused by the process the tool takes to refresh the data. First, the access to the web page is checked for the web connector, and then the data is retrieved. This first step though can fail, which can cause the refresh to fail, but there are a few ways to work around this problem:
Cache Size for PowerBI Desktop
It's good practice that before you schedule the report, you validate the complete dataset. One additional help for this, especially in the cases of larger datasets retrieved from the API, is to increase the cache limits. This increase can improve PowerBI's performance and stability. To configure this:
- Click Settings->Options->Global->Data Load to expose the "Data Cache Management Options".
- Try increasing the Maximum allowed (MB): to 16000 (or about double what the current setting is).
Set API Output Type to JSON
This method avoids the web connector and the possible access failures by using a JSON file instead.
- In the Supermetrics API Query Builder, reconfigure your base query you wish to use with PowerBI, and instead of using the MS PowerBI option, set it to Supermetrics.
- Click the link to Create shortened URL and copy the URL to the clipboard.
- In PowerBI, click Get Data and select "Web" from the list.
- Past your shortened query URL into the URL box under the "Basic" option and click OK to load the data.
- Next click Edit Queries, and then click the setup gear icon next to Source (top option in the below screenshot).
- Switch to the "Advanced" option.
- Change Open file as to "Json".
- It is also recommended you set the Command timeout in minutes (optional) to something like "120" to increase the amount of time allowed for the data to refresh.
- Click OK to save the changes.
- Navigate to "List" under Data, and make the changes needed to process the data (e.g., convert to table and split data into columns based on a delimiter character. This article has some helpful hints on how to process the JSON file.
Set Up Gateway on PowerBI Online
To set up a scheduled data refresh on Power BI Online from web data sources, it is required to set up a personal gateway connection (unless there is already an enterprise gateway available for you to use). Microsoft provides documentation on how to set up a gateway, which can be found here, as well as more general information on data refreshing in Power BI and troubleshooting refresh scenarios.
Some best practices for the gateway include using the enterprise mode, as opposed to the personal mode, as well as moving the gateway to a more powerful machine with more CPU and RAM or splitting out data sets onto different gateways if the performance is poor. Recommended hardware specs for the gateway are eight CPU cores, 16-GB RAM.