For data set managers, the import template editor is one of the most important areas of AAM. Import templates define how data from external systems like Projector or Salesforce should be placed into your data set's attributes and indicators. You can even massage that data on its way into the system, applying formatting, calculating custom values, combining data, splitting data, and more. Import templates must also be kept in sync with your data sources. If you change the way a data source is constructed, you'll also need to update your import template to reflect those changes.
Here is a common example of how import templates are useful. Imagine you have two data sources from disparate systems. One is Projector and the other is Salesforce. In Projector, our reports provide just the raw revenue numbers. In Salesforce reports, the revenue numbers include a currency symbol. When you import the Salesforce data, you use an import template formula to strip off the currency symbol. Now your Projector and Salesforce data is in a consistent format.
This page is only an introduction to import templates. Please see the full documentation for a full explanation of how they work.
The import template editor is reached from Data Set Menu | Select a Data Source | More | Import Settings.
Permissions and Settings
Import templates are editable by users with the Data Manager role. Users can only modify import templates on data sets they own or that are shared to them.
An import template is associated with exactly one data source. There is only one active import template per data source.
When updating an import template, if new columns of data are detected, a corresponding attribute/indicator will be automatically created to house the data.
Import Template Editor
This section teaches you how to use the import template editor.
View Import Template
The import template editor is automatically launched for any new data sources or for any manual data upload via file or copy/paste. Existing import templates can be viewed by visiting a data source and clicking More | Import Settings. In order to modify an import template, you'll need to make sure that the data source is available for analysis. If you get an error that the data source could not be found (403 error or the like), then make sure the data source exists and is accessible.
When you first enter the template editor, the first thing you'll notice is the Columns available section on the left. This shows all the columns of data found in your data source. Please see the screenshot below as a reference.
The upper section contains a number of links that filter the displayed columns.
You can tick checkboxes for each column and bulk modify them via the dropdown menu.
The table below explains the icons you'll see in the template editor.
|1, 2, 3||Column Number||The numbers correspond to the actual column ordering in your data source. For example, if you import a csv file and the second column is called "Resource," then it is listed as number two.|
|Column Type||What type of column is this? Attribute, Date/Time Attribute, or Indicator?|
|*||New Column||When there is an asterisk shown this is a new column. A new attribute/indicator will be created for the data. Or, it can be manually mapped to an existing attribute/indicator.|
|Transformation Script Applied||This attribute/indicator is being programmatically altered on import by applying a formula. You can have "virtual" columns that don't actually map to a column that exists in the data source. We call these transformation columns. Because they don't necessarily exist in the data source, this makes the column number assigned a bit of a non-sequitur.|
|Reorder Column||Take the current column definition and reorder it in the displayed list.|
|Merged Column||You can merge two or more columns and combine their data. For example, your data source might have columns First Name, Middle Name, Last Name. You could merge all three columns together to create a single entry. (This can also be performed using transformation scripts).|
Data Source Format
At the top of the template editor, you define the general configuration of your data source. Mostly you won't need to modify these settings.
|Header Row||Does your data source have a header row? For example, in csv file this would be the first row of data. If it does have a header row, then the template editor determines the names of columns based on the header row value.|
|Excluded rows||For manual entry, csv, and Excel file data sources, you can exclude the first N number of rows. For example, if the first three rows contain information about who created the csv file, and the fourth row was the header row - you would enter 3 here to exclude the first three rows of data from the import.|
For Projector reports use UTF8. For other data sources you'll need to check with the data source provider to determine the stored data format.
|Separator||For csv files, specify the separator between values|
|Data source settings||Click this to open a configuration dialog. The settings here are the same as when configuring the Projector Connector.|
|Fill the empty cells||You can optionally fill all empty cells with a default value|
|Apply import template||Browse previous imports of this data source or other data sources. You may then opt to use that import template instead of the current one.|
|Reset columns setting||Discard all changes and start as if this were the first time the data source was ever imported|
|Get new columns||Look at the data source and fetch any new columns of data that have been added. Try and preserve existing columns and transformations.|
Clicking on a column in either the left hand column or in the Data Preview area brings up the column definition editor. Here you can:
- Specify whether the column is an attribute or an indicator
- Map it to an existing attribute/indicator
- Redefine empty cells
- Transform via scripts (see next section)
Transformation scripts allow you to modify the data on import via formulas. See AAM Formulas to learn how to use the formula editor.
Modifying existing a column's values on import is pretty straight forward. For example, if your import includes a currency symbol, you could strip that symbol off and be left with the raw number. What is even more interesting though, is that you can have a column that is completely calculated via scripts. For example, you could compute accrued vacation time via a formula and create a new column for those values.
When using transformation scripts, the incoming data is always treated as a string. You'll need to cast the column data to the appropriate type (integer, decimal, etc) if you want to perform mathematical functions on it.
Here are a few real world examples of using import templates
Adding/Removing Columns to Data Source
Whenever you change a data source, you'll need to update your import template to reflect the changes. Reason being, at its core the template is pretty dumb. It basically says, column 1 from data source gets stuck associated with attribute X in my data set. If the order of your columns changes, or new columns are inserted in the middle, your data is going to end up populating the wrong attributes/indicators. So what you need to do is always make sure you go to your import template and Get new columns.
- Update your data source columns
- Edit your import template
- Click Get new columns
- Map or configure any new columns (look for the *)
Joined Data Sets
If the data set you are updating is part of a join, you'll likely want any new attributes/indicators to populate out to the joined data sets too. Make sure that when you configure your import you select the option to populate new attributes/indicators to joined tables. Otherwise you'll need to delete/readd the join manually.