How do I model a data source?

With modelled data sources, you can use formulas and properties to organize and restructure your data. Using modelled data sources simplifies Klip® building and enables you to reuse settings and calculations across multiple Klips. You clarify and organize your data when creating a modelled data source, including converting tree structured data into tabular data, and then use the modelled data source to build a Klip or a PowerMetric.

Notes:

  • In order to model a data source, you must have already created a data source. Learn how to create a data source.
  • When viewing a modelled data source containing over 100 rows of data, all rows may not display at once. To view additional rows of data, click the message link that displays below the modelled data source. For example, “Show 100 more."
  • Modelling dynamic data sources is not currently supported.

This article includes:

How do I add a modelled data source?

To add a modelled data source:

  1. Click Data Sources in the left navigation bar.
  2. From your list of data sources, select the one you want to model.
  3. Click Create modelled data source.

All PowerMetrics must be created from modelled data sources. Learn more about creating a PowerMetric here.

Setting properties for a modelled data source

The Properties panel, that displays on the right when editing a modelled data source, is used to select general settings for your modelled data source, including the following:

  • Name: Type a name for your modelled data source here. You can also hover and click on the title at the top-left of your page to name your modelled data source. The name you choose will display for your modelled data in the Klip Editor and help you identify your modelled data source in your list of data sources.
  • Description: Type a description for your modelled data source here to identify its purpose.
  • Column Names: Choose the row number that contains the title of your data columns from the drop-down list.
  • Exclude Rows: Enable Exclude data before row to choose the row number you want your data to start with in the modelled data source. For example, if you choose 5, the data in your modelled data source will start with row 5 and onwards.

Add individual columns to your modelled data source using the + button. Select a column of data from your unmodelled data source to populate the new column. If you are using tree-structured data you must add each column of your modelled data source individually.

Access the 3-dot menu on the right-side of each column name to view keyboard shortcuts, remove a column, move it left or right, or rename it.

Access the toolbar on the top of the screen to add, duplicate, move, and remove columns. Here, you can also use the arrow icons to undo and re-do actions. Use the 3-dot menu on the toolbar to access more options.

Setting column properties for a modelled data source

The Column Properties panel displays on the right-side of the screen when an individual column is selected.

Set properties and align your data by choosing a Name and a Data Format for each column.

  • Name: The column name you choose will display in the Klip Editor. Name each column by entering a name or by selecting the column and hovering over the column name. You can also name your column using the 3-dot menu beside the column name.
  • Data Format: Choose one of the following data types for each column:
  • Text: Textual data is denoted by ABC next to the column name.
  • Number: Numeric data is denoted by 123 next to the column name.
  • Percentage: Data formatted as a percent is denoted by a percentage symbol, %, next to the column name. When choosing this data format, make sure to use the formula bar to convert percentage values to decimals between 0 and 1, if they are not already.
    For example, if your values are 23, 33, and 55, add “/100” to the formula in the formula bar so that they become 0.23, 0.33, and 0.55.
  • Currency: Data formatted as Currency is denoted by a currency symbol, $, next to the column name.
  • Date/Time: Date/time data is denoted by DATE next to the column name. When choosing this data format, you can optionally set your date/time formats using the Input Format menu and Display Format menu. When you choose your Display Format it will be reflected in your data column. You can also set your timezone here. You can either use your default account level time zone or set a different one.

What do I do if my unmodelled data source is altered?

When editing your modelled data source, you may see warning icons in your column headers and the following error message:

This message displays when there is a change to the unmodelled data source.
For example:

  • When the name of a column in your unmodelled data source has changed
  • When the order of the columns in your unmodelled data source has changed
  • If a column has been deleted from your unmodelled data source

Click on columns with warning icons and look under Column Properties to see more details. To resolve the issue, ensure your formulas are pointing to the expected spot in the unmodelled data source. For example, if column CC in your unmodelled data source is deleted, you need to delete column CC in the modelled data source and ensure the columns that follow are properly aligned. You can double-click any column to view your unmodelled data source. Once you have made the necessary changes, click Save and Exit to dismiss the warning.

Note: If you are using your modelled data for a PowerMetric, any new formulas will be applied to the PowerMetric going forward, but the previously stored data will not be changed.

If any of your columns display error icons, it indicates that your modelled data source is unable to reference data from the unmodelled data source.

For example:

  • The unmodelled data source may have been partially deleted or deleted in its entirety.
  • There may be an issue with the formula(s) used in your modelled data source.

Writing calculations for a modelled data source

You write calculations using functions and references in the formula bar. You can access the formula bar by clicking the 3-dot menu and selecting  Edit formula (or by right-clicking or double-clicking any column). With the formula bar open, you can view your unmodelled data source and compare it with your modelled data source. The formula bar works the same when editing a modelled data source as it does in the Klip Editor. To close the formula bar (and hide your unmodelled data source), click the 3-dot menu in any column and select  Hide formula or click anywhere in the dark grey workspace surrounding your modelled data source.

Learn more about using the formula bar here.

You can find best practices for modelling data for PowerMetrics here.

Note: Variables, formula references, and result references are not currently supported when creating or editing a modelled data source.

Saving a modelled data source

When you are finished creating your modelled data source, make sure to Save and Exit. You can always go back and edit your modelled data source later.

To edit a modelled data source:

  1. Click Data Sources in the left navigation bar.
  2. Click on the modelled data source.
  3. Click Edit Model.

Note: If the unmodelled data source that your modelled data source is based on changes, you may get an error asking you to confirm that the changes are reflected correctly when viewing the modelled data source. Click Save and Exit to dismiss the warnings.

Building a Klip using a modelled data source

Once you have created a modelled data source, you can build Klips with the modelled data source. You can do this by either going to the About this Modelled data source page for the data source (this page displays when you select a modelled data source from your list of data sources) and clicking Build a Klip with this Model or in the Klip Editor by clicking +Add Data Source and selecting the modelled data source.

Drag a component to your Klip workspace and begin assigning data columns to your component. References to modelled data source columns are denoted by an @ symbol and the column name.

Learn more:

Have more questions? Submit a request