The Supermetrics API makes it easy to get fresh marketing data into Power BI using the “web data connector”. 

However, Power BI starts to run into issues when the data volume grows beyond 10k rows from a single query. For marketing teams and agencies with large amounts of advertising data, this is simply a non-starter.

To overcome these limitations, we recommend storing a historical CSV export of your marketing data locally in Power BI and then creating a new calculated table that draws data from both the web-based table and your CSV-based table.

How to use CSVs and incremental queries to load data to Power BI

To get there, all you have to do is follow these five simple steps.

But before we start, if you don’t have a license to Supermetrics API just yet, you can always start your free 14-day trial now.

Get started today

Try Supermetrics API for free with a 14-day trial. No credit card needed.

Step 1: Download your historical data from query manager

Supermetrics’ query manager is a great place for profiling your data before loading it into your BI tool, but it can also be used to export historical data from your marketing data sources. Simply run your query for the dates you’re interested in, and use the button at the bottom to export the results to a CSV. 

historical data query configuration

Pro tip: since the query manager is designed for data preview, it automatically limits the results shown to the first 1,000 rows. You can change this using the option under the “Rows to fetch” option under the “Split by” dropdown.

You may need separate CSVs if your data is too large, perhaps one per year, per quarter or per month depending on your data size. See the “Limitations” section below for more details.

Rows to fetch

Step 2: Configure your incremental URL

Historical data is great, but it is static and unchanging. You want to be able to click one button and have Power BI pull in the latest data directly from your sources, don’t you? This quick trick that I’ll show you will help make sure that’s a reality.

Copy the URL from Query Manager and paste it into a notepad, or anywhere you can easily edit it. It should look something like this:

https://api.supermetrics.com/enterprise/v2/query/data/PowerBI?json=%7B%22ds_id%22%3A%22FA%22%2C%22ds_accounts%22%3A%5B%22act_XXXXXXXX%22%5D%2C%22ds_user%22%3A%XXXXXXXXXXX%22%2C%22start_date%22%3A%222021-05-21%22%2C%22end_date%22%3A%222021-05-21%22%2C%22fields%22%3A%5B%22Date%22%2C%22adcampaign_name%22%2C%22impressions%22%2C%22cost%22%5D%2C%22max_rows%22%3A1000%2C%22api_key%22%3A%22api_XXXXXXX%22%7D

Now, you’ll want to change the “end_date” parameter to just say “today”:

https://api.supermetrics.com/enterprise/v2/query/data/PowerBI?json=%7B%22ds_id%22%3A%22FA%22%2C%22ds_accounts%22%3A%5B%22act_XXXXXXXX%22%5D%2C%22ds_user%22%3A%XXXXXXXXXXX%22%2C%22start_date%22%3A%222021-05-21%22%2C%22end_date%22%3A%22today%22%2C%22fields%22%3A%5B%22Date%22%2C%22adcampaign_name%22%2C%22impressions%22%2C%22cost%22%5D%2C%22max_rows%22%3A1000%2C%22api_key%22%3A%22api_XXXXXXX%22%7D

You’ll want to add “today” right after the %22. %22 is URL speak for a “ symbol.

If you’re wondering why all of those % symbols are in the URL, this is standard URL encoding.

You’ve now given the query a dynamic end date. Every time Power BI goes to refresh the query, Supermetrics will see “today” as the end date for your query and return all of the data that is available up to the given day.

The “start_date” parameter should stay fixed, but make sure that it aligns with the last day of data in your historical export.

Step 3: Import your historical data into Power BI

Next, you’ll load your historical data into Power BI. Note that if you have large datasets, it might be advisable to break your data into multiple different files rather than one single large file. 

I’m going to do this and rename my table “facebook_historical”.

Import historical data

Step 4: Import your incremental data query

After you’ve imported all of your historical data, you’ll use your modified query URL from the query manager to set up a web-based datasource. For additional instructions on this, follow these steps.

import incremental data

I’m going to name this table “facebook_incremental”.

Step 5: Create a calculated union table

In this last step is where the magic happens. 

Now, we’re going to create a calculated table that merges our two tables as one. The (UNION) function in Power BI can take any number of tables and perform a SQL-style UNION on them. Basically, that means that with this single calculated table, you’re able to build visualizations based on the data contained in both “facebook_historical” and “facebook_incremental”.

Create union table

The best part about this is that when you refresh your incremental table, your refreshes will run significantly faster since you’re only pulling a few months worth of data from the Supermetrics API rather than potentially several years.

Refreshing incremental data

Limitations:

The main purpose of this procedure is to increase refresh speed and work with larger data volumes without using any other tools. There are, however, some limitations that you should take into account:

  1. You may not be able to get all of your historical data from the query manager in a single query. The query manager can return up to 1M rows from a single query, but that may not be enough for everyone. If you find that your row counts are exceeding the maximum threshold, try breaking them down into smaller time intervals, maybe by year, quarter, or month.
  2. The query manager has a query timeout of 30 minutes. If you can’t retrieve your data in 30 minutes, try reducing the date range of data requested for a single query and use the chunking method described above.
  3. Power BI does not allow uploading files more than 1GB in size. If your files are approaching this limit, it is best to chunk them out as described above.
  4. There is technically no limitation to the size of an overall Power BI workbook. However, several users have reported to us that they experience significantly decreased performance when the size of their workbook approaches or exceeds 1GB.

One way to overcome these limitations is by investing in a scalable, cloud-based data warehouse for your marketing data. Data warehouses solve these size problems because they store data in the cloud, not on your local computer, freeing up your computer’s resources for actually running Power BI. The other big reason is that data warehouses are architected to store several terabytes of data, so size will not be an issue as long as it has been correctly architected.

If you’re interested in learning about data warehouses that are compatible with both Supermetrics and Power BI, please check out information on the following solutions:

Power BI

Start your trial today

Try Supermetrics for free with a 14-day free trial. No credit card needed.

Try Supermetrics for free

Get full access to Supermetrics with a 14-day free trial.
No credit card required.