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 right side. You can use this drop-down to switch between sheets when writing formulas and creating models. (See below.)
Setting properties for a modelled data source
The Properties panel, that displays on the right, 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 in your PowerMetric 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.
You can add individual columns to your modelled data source using the + button (which displays beside the last column in your data) or via the 3-dot menu for each column, or via the toolbar at the top of the modelled data source window. After adding a new column, you can populate it with data by clicking a column in your unmodelled data source (that displays underneath your modelled data source window). If you are using tree-structured data you must add each column of your modelled data source individually.
For more options, click the 3-dot menu on the right-side of a column name. (See below.)
Access the toolbar at the top of the screen to add, duplicate, move, and remove columns. Use the arrow icons to undo and re-do actions. From the toolbar, you can also merge data from other modelled data sources, add all columns from the unmodelled data source, remove all columns from the modelled data source, and open the Properties panel.
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: You can either let us automatically set format or choose one of the following data types for each column:
- Text: Textual data is indicated by ABC next to the column name.
- Number: Numeric data is indicated by 123 next to the column name.
- Percentage: Data formatted as a percent is indicated by a percentage symbol, %, next to the column name. When choosing this data format, 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.
- Date: Date/time data is indicated 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.
- Currency: Data formatted as Currency is indicated by a currency symbol, $, next to the column name.
Writing calculations for a modelled data source
In the formula bar you build formulas, using functions and references, to manipulate, refine, and perform calculations on your data. For example, you can combine related information from separate columns into a single column or group your data to hide duplicate values. The possibilities are endless.
To open the formula bar:
- Right-click any column, or click the 3-dot menu in any column, and select Edit formula. You can also open the formula bar by double-clicking any column. When you open the formula bar, your unmodelled data source opens below.
To close the formula bar:
- 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. When you hide the formula bar, your unmodelled data source is also hidden.
- Variables and formula references are not currently supported when creating or editing a modelled data source.
- In the modeller, when building formulas using results references, we recommend using consistent reference types within the formula, for example, results references (&) should be combined with results references, and datasource references (@) should be combined with datasource references. Otherwise, you may notice misalignment in your results. This is because datasource references include all rows of your raw data source (including headers and leading blank rows), while result references are the final result of your modelled column (with the headers and excluded rows removed). If you want to combine datasource and results references, we suggest you create a modelled column that points to your raw data source column and include a results reference to this column in your formula.
- Tip: Select a column in your data and click the Evaluate button. Using this method you can compare information for a column in the raw data source to the same column in the modelled data source to see if the rows align. Note that the number of rows that displays for a modelled data source column may be misleading because, when modelling, we automatically add blank rows to columns when necessary to ensure row alignment.
For more detailed information on writing calculations for 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:
- 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. Once you have made the necessary changes, click Save and Exit to dismiss the warnings. For more information on error resolution, go here.
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.
Check out these additional Knowledge Base articles:
Watch this video on modelling your data: