Skip to main content

Working with dynamic data sources in Klips

Updated

A dynamic data source is a data source that uses one or more variables in its query instead of hard-coded values, thereby enabling the ability to dynamically retrieve data. Learn more about variables.

Dynamic data sources offer a solution for users who need more flexibility in working with large amounts of data or when accessing APIs that need to paginate to return more entries.

Important notes

Please note the following when working with dynamic data sources:

  • Dynamic data sources are typically used for pagination, interactive dashboards with user input controls, date queries using date pickers, and with Company or User properties.
  • Dynamic data sources should NOT be used to implement security. They are variable driven and variable values can be accessed through different features, for example, permalinks, where all the variables and values are included in the URL. If you want to implement security to prevent users from accessing your data, create a specific data source with the parameters and values set in the data source definition, without using variables.
  • Modelling dynamic data sources is not currently supported.
  • When creating a dynamic data source, you have to specify any dynamic parameters directly in the Query URL.  You cannot use the Query Parameter fields.
  • Dynamic data sources create a unique instance for every variable. If you’re using a combination of variables from user input controls or date pickers, we recommend using a button component to apply the value to reduce the number of dynamic instances created.
  • Every dynamic instance is refreshed based on its data source refresh interval. Make sure your system can handle these refresh requests. For example, your database may need to process rapid, multiple requests.
  • Dynamic data source instances are regularly removed from the queue if they’re no longer being used. For example, instances based on user properties may be removed if the user has not logged in for a number of weeks. This means on the subsequent login, there may be a slight delay in the dashboard loading as all the dynamic instances for the user need to be recreated and refreshed.

This article includes:

Setting up a dynamic data source

A common use for dynamic data sources is accessing data based on a parameter from a list, for example, Google Analytics profile IDs, Facebook Ads campaigns, or Google Ads Manager Account client IDs, and displaying the data for each item in the list. You can do this effectively with dynamic data sources instead of creating a separate data source for every ID in your list.

You can also use dynamic data sources with SQL databases. Any parameter in the WHERE clause can be used as the dynamic variable and also any value in the host, port numbers, usernames, and password fields.

This example describes how to create a dynamic data source that displays data for each Facebook Ads campaign:

  1. In the Klip Editor, at Advanced Tools, click Create new variable and enter a value, for example, a variable called campaignID with the value set to a campaign ID, for example, 1234567.
    Note: The variable used in a dynamic data source must be set to a value that will return valid results when used in the data source query.
  2. Create a new data source and use the variable you created in the Query URL with the correct formatting. In this example, we use the Facebook Ads connector and enter the variable in place of a hard-coded value, using the format {props.campaignID}. For example:

https://graph.facebook.com/v23.0/act_1234567/campaigns?fields=id,name,created_time,effective_status&effective_status=['ACTIVE']&limit=100

Replace act_1234567 with {props.campaignID}.

The query will look like the following:

https://graph.facebook.com/v23.0/{props.campaignID}/campaigns?fields=id,name,created_time,effective_status&effective_status=['ACTIVE']&limit=100

  1. To create a list of values like campaign IDs, build a drop-down User Input Control component to select the campaign IDs from and enter the list of values you want to select from in the Values sub-component. The values for the campaign IDs can be a manually-entered list, or data from a standard Facebook Ads data source which returns your active campaigns.
  2. Set the variable for the User Input Control to the same variable you entered in the Query URL. In this example, we use campaignID.
  3. Build a Klip, with a Table component, with the new dynamic data source that was returned when you created the query. Select the columns of data and reference the data as you would with any data source.

When the Facebook Ads Campaign ID is selected from the User Input Control, a dynamic data source instance is created and the table or any component automatically responds to display the data for the Facebook Ads Campaign ID selected.

To see all the dynamic instances being created, modify the URL for the dynamic data source you created by replacing /view with /debug_info.

The list of dynamic instances will refresh, based on the refresh interval set for the data source.

Troubleshooting dynamic data sources 

Following are some troubleshooting ideas for your dynamic data sources. If, after going through this section, you still need debugging assistance, contact support@klipfolio.com.

This article section includes:

What do I need to know about dynamic data sources with multiple variables?

Dynamic data sources can have multiple variables associated with them. If these variables are set to an invalid combination, an invalid data source instance will be created. To prevent invalid combinations of variables, the User Input Control components that set these variables should be associated with a button component configured to co-ordinate the setting of these variables. This enables better Klip performance and avoids unnecessary formula execution.

Where can I find the variable values for a dynamic instance?

Each dynamic instance displays the variable name (props.variableName) and its associated values. To see if an instance is failing, create a standard (sometimes called static) data source, with those specific values and see if data is returned.

How do I access debug information?

If you are having issues with your dynamic data sources, you can use our debug_info pages to help you resolve them.

To access the debug_info pages:

  1. In the left navigation sidebar, click the 3-dot menu beside Data Sources and select Data Source Library.
  2. Select the dynamic data source to open its About this Data Source page. A data source can be identified as dynamic if the Download Data option is not displayed.
  3. In the URL displayed in the browser address bar, replace view with debug_info.
  4. Scroll down to the Instances section. This is a list of the individual dynamic data source instances, each with its unique ID, variable (prop:<variable>) setting and refresh information.
  5. Check for dynamic data source instances with the following issues:
    • invalid or missing variable setting
    • empty last refresh value
    • small data size
    • refresh fail count with a non-zero value
  6. For each invalid dynamic data source instance, select one of the [ x | refresh | data ] actions displayed next to its identifier.
    • click data to view the dynamic data source instance and assess the contents
    • click x to delete the dynamic data source instance (this will delete only the specific instance, not the entire data source)
    • click refresh to refresh the dynamic data source instance

Following are some examples of errors and suggestions for resolution:

How can I see the data returned for a dynamic instance?

Using the /debug_info view, find the instance you’re interested in (search for the variable combination). Then, select “data” to see the data that’s been returned. Tip: This is also a great way to check that valid data is being fetched and the data structure is correct.

Why aren’t my new variables showing up in my dynamic data source instances?

Dynamic data sources can have multiple variables associated with them. If these variables are added after a dynamic data source was created, you’ll need to associate the variables with the Klip. For your new variables to work with the dynamic data source, they must be in a Klip and listed under “datasourceProps” in the schema.

There are two ways to do this:

  • Remove the dynamic data source from the Klip Editor, then add it back.
  • Edit the Klip source code and add the variable to the section called datasourceProps "datasourceProps": [
    "campaignID"
    ]

The next step is to ensure all the dynamic data source instances are removed and recreated. You can remove all dynamic instances by modifying the data source /debug_info page: https://app.klipfolio.com/datasources/debug_info/<datasourceID>?deleteAll=true

How do I queue a refresh for all my dynamic data source instances?

Dynamic data sources can be queued to refresh from the Data Source Library. https://app.klipfolio.com/datasources/index. Select the checkbox next to the data source name, then click Queue for Refresh.

Why is MAPFLAT failing when I use dynamic data sources?

Narrow down which dynamic instance is failing so you can verify that the variable MAPFLAT is passing in is valid. You can check each value that is being passed in to find the error, or look at the dynamic instance list under /debug_info.

If all the values are correct, and the variables are working, MAPFLAT can fail if data is missing from the specific instance. For example, if JSON data is being returned, MAPFLAT can fail if it’s looking for a node that doesn’t exist in the dynamic data source instance. The resulting formula errors can usually be fixed using one of the Klipfolio JSON functions.

How do I know if my dynamic data sources are failing?

You can quickly see a list of your dynamic data sources that are failing to refresh.

If you see a red icon in the left navigation bar (beside Data Sources) that means you have at least one data source that is failing to refresh. (Learn how to view clients’ failing data sources.)

Clicking the red icon opens your Data Source Library with the Failed Data Sources filter applied. (You can also access this list by clicking Data Sources in the left navigation bar and then selecting Failed Data Sources from the filter drop-down list.)

All the data sources that have failed to refresh are listed with their Last Refresh date. If you are unsure which of your data sources are dynamic data sources, click the red exclamation mark beside the Data Source Name (see below) to go directly to its About this Data Source page. If it’s a dynamic data source, the page will not include the Download Data option.

If a dynamic data source(s) is failing, you can resolve this issue by going to its debug_info page and removing the failing data source instance using the “x”. (See the previous section of this article for more detail.) If the failing instance re-appears, you will need to investigate the cause. For example, it could be due to incorrect variable options or variable values in the Klip Editor or a problem in the formula.

Related articles

Related articles