With modelled data sources, you can use formulas and properties to organize and restructure your data. Using modelled data sources simplifies Klip building, enables you to reuse settings and calculations across multiple Klips, and is a requirement when building PowerMetrics. 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.
Note: Modelling dynamic data sources is not currently supported.
This article includes:
- How to add a modelled data source
- Setting properties for a modelled data source
- Setting column properties for a modelled data source
- Writing calculations for a modelled data source
- Saving a modelled data source
- Building Klips and PowerMetrics with modelled data sources
How do I add a modelled data source?
To add a modelled data source:
- Click Data Sources in the left navigation bar.
- From your list of data sources, select the one you want to model.
- Click Create modelled data source.
- When modelling a data source that has more than 30 columns, only the first 30 will be automatically added. Learn more here.
- 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."
- If you have a multi-sheet data source, for example an Excel file that has data in multiple sheets, you can access the data in the other sheets when modelling your data source. After bringing in your multi-sheet data source, double click a column to select it. You can select the sheet you want to add data from by using the Sheet drop-down menu on the left side. You can use this drop-down to switch between sheets when writing formulas and creating models.
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 onward. Refer to the digits that display on the left side of the rows to help you select the right number of rows to exclude.
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 and and when you are creating a PowerMetric. 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.
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.
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:
- Click Data Sources in the left navigation bar.
- Click on the modelled data source.
- 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 Klips and PowerMetrics with modelled data sources
Once you have created a modelled data source, you can use it to build either a Klip or a PowerMetric. Navigate to the About this Modelled data source page by selecting a modelled data source from your list of data sources, then click Create a Klip or Create a PowerMetric.
- For detailed instructions on building a Klip, click here.
- For detailed instructions on building a PowerMetric, click here.