Skip to main content

How do I merge modelled data sources?

Updated

Merging modelled data sources enables you to:

  • Add columns of data to a modelled data source from one or more other modelled data sources - without having to manually write formulas.
  • Add clarity by aligning columns that contain related data.

This article includes:

When should I merge my data?

Merging modelled data sources enables you to bring your information together. This means you can consolidate complementary modelled data sources (provided they share at least one column in common) in order to create a modelled data source that provides a more complete version of your data and is easier for users to understand.

Here are some examples of when to merge data:

Simplifying by associating related data
You can add clarity to your data by associating complicated or technical data with more user friendly terms from a different modelled data source. For example, let’s say Modelled Data Source A contains a list of sales data by country, but each country is only represented by an id number, not by a name. If Modelled Data Source B contains both the country ids and the corresponding country names, you could merge the country names column from Modelled Data Source B into Modelled Data Source A, making the sales data easier to consume and understand at a glance.

Adding context and dimensions to your data
Merging modelled data sources can help you enrich your data. For example, let’s say you have sales by country in a modelled data source and you also want to show the sales regions those countries are located in (with multiple countries in each region). You could merge your modelled data source with another modelled data source that has the same list of countries with their corresponding regions. Now you can build a Klip that has both region and country as dimensions, giving you more options for displaying and breaking down your data.

What do I need to know before merging my data?

Before merging modelled data sources, consider the following:

  • Any modelled data source can have up to four modelled data sources merged into it.
  • The total combined size of your merged data source cannot exceed the data source size limit for your account plus 3 MB. By default, the maximum size limit for a data source is 10 MB and the maximum merged data source size is 13 MB.

Note: You can increase your maximum data source size to 15 MB (and merged data source size to 18 MB) by upgrading your plan to include the Larger Data Sources Power-Up. To learn more, click your Account Name at the bottom of the left navigation sidebar and select Account > Plan to see all of our upgrade options. If you don't see this option, click the button in the left navigation sidebar and select Plan.

  • Nesting of merged data sources is not supported; You cannot select modelled data sources that already contain columns that have been merged from other modelled data sources.
    Note: If you want to see whether a modelled data source already contains merged columns, check its data source details page (About this Modelled Data Source > Uses Modelled Data Source(s)).

How do I merge modelled data sources?

To merge modelled data sources, open an existing modelled data source to use as the base and click the Merge data button. Follow the steps below.

Step 1. Choose a modelled data source to merge

Choose a modelled data source that has at least one column with data in common with a column in your base modelled data source (note that the columns don’t have to have the same column name). Once you have selected your modelled data source, click Next.

Step 2. Align your data sources

Identify a column in the base modelled data source and a column in the secondary modelled data source that contains the same data in order to align the two. This is your join key. The join key columns must be of the same data type (numeric or text based). The column from the second modelled data source should not contain repeating values (if it does, only the first value will be used). Your list of columns displays the column heading and type of data to help you make your selection. Once you have selected your columns, click Next.

Notes:

  • If there are no matching values in the aligning columns (the join keys), the added columns from the second modelled data source will be blank. Only rows with a common value will have data in the added columns.
  • If you select a modelled data source that does not align with your base modelled data source, the added columns will be empty.
  • Columns with the Duration format type cannot act as join keys.

Step 3. Add columns

Once you’ve aligned your data, you can select the columns from the secondary data source that you want to add to your base modelled data source. After selecting the columns you want, click Merge.

Step 4. Edit your merged modelled data source

Newly merged columns will be added to the right of the base modelled data source. You can edit the merged column names, data types, and properties, but you will not be able to access the formula bar from here. Learn how to edit the formulas for these columns. You can modify the columns of the base modelled data source just as you would for a single modelled data source. To learn more, see "How do I model a data source?"

How can I tell where my data came from?

To find out which modelled data sources were used to create a merged modelled data source, you can check the data source details page or the merged data pane (if you’re currently editing the merged modelled data source).

In the data source details page:

  • Under Connection, see Uses Modelled Data Source(s) or Used by Modelled Data Source(s).
    • If Uses Modelled Data Source(s) is displayed, the modelled data source is the base for a merged modelled data source and contains data from the modelled data source(s) listed here.
      Note: You will not be able to add columns from this modelled data source to another merged modelled data source.
    • If Used by Modelled Data Source(s) is displayed, columns from this modelled data source have been merged with the listed modelled data sources.

In the merged data pane:

  • Click the Merge data button to open the merged data pane.

The merge map (a way to view and edit the connections between merged modelled data sources) is displayed under Merged Data.

Depending on the order in which you’ve merged new columns with your base modelled data source, the data sources and their associated column headings will display one of the following colours:

Colour Indication
Original data source
1st merged modelled data source
2nd merged modelled data source
3rd merged modelled data source
4th merged modelled data source

The names of each data source you merge are displayed in the merge map under the heading Merged Data (below the toolbar) so you can easily associate each column with its source.

How do I remove columns added through the Merge data button?

You may remove columns individually or delete the merge action to remove all columns from a particular data source at once.

To remove columns individually:

  1. Click the column you want to delete to select it.
  2. Click the Remove column button in the toolbar.
    Note: You can also remove a column using its drop-down arrow button.

To remove all columns from a particular data source at once:

  1. Click the Merge data button to open the merge data pane.
  2. From the merge map, click the three-dot menu for the merged data you want to remove.
  3. Click Delete merge.
    Note: Deleting a merge does not affect the underlying data source, only the modelled data source you are currently editing will be impacted.

What happens if my data sources change?

If one or more of the data sources you used to create your merged modelled data source changes, you may notice that some of your columns display as empty. The most common reasons for this are:

  • Your join key no longer exists as a model column
  • The column you used as a join key is no longer valid (for example, because its formula was edited)
  • At least one merged data source has been deleted

If your data source contains errors, your Klips may not display correctly. You can correct these errors by doing one of the following:

If your join key no longer exists as a modelled column or is no longer valid:

  1. Click the Merge data button to open the merge data pane.
  2. Click the three-dot menu of the affected merged data in the merge map.
  3. Click Edit merge.
  4. Select a new pair of matching columns to align the two modelled data sources.
  5. Click Apply.

If at least one merged data source has been deleted:

  1. Click the Merge data button to open the merge data pane.
  2. Click the three-dot menu of the affected merged data in the merge map.
  3. Click Delete merge.
  4. If you are able to replace your deleted data source, you can go through the merge procedure again.

How do I edit my merged modelled data source?

After creating a merged modelled data source, you can use the formula bar to edit columns from your base modelled data source. If you want to edit columns brought into the modelled data source through the Merge data button, you will need to navigate to the original modelled data source first.

If you try to access the formula bar to edit a column that has been added to your data source using the Merge data button, the function bar will not display. Instead, you will see the message This column is from followed by a hyperlink to the information page for the modelled data source from which the columns originated.

To edit columns added to your base model using the Merge data button:

  1. Navigate to the modelled data source’s data source information page.
  2. Click Edit Model.
  3. Use the formula bar as usual. Any changes to shared columns will also display in your merged data source.
    Note: Variables and formula references are not currently supported when editing a modelled data source.

Learn more

 

Related articles