Page tree
Skip to end of metadata
Go to start of metadata

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.


Columns Available

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.

IconTypeDescription
1, 2, 3Column NumberThe 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 TypeWhat type of column is this? Attribute, Date/Time Attribute, or Indicator?
*New ColumnWhen 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 AppliedThis 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 ColumnTake the current column definition and reorder it in the displayed list.
Merged ColumnYou 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.

ControlDescription
Header RowDoes 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 rowsFor 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.
Encoding

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.

  • Auto
  • UTF-8
  • UTF-16LE
  • ISO-8859-1
  • Win-1250
  • Win-1252
SeparatorFor csv files, specify the separator between values
Data source settingsClick this to open a configuration dialog. The settings here are the same as when configuring the Projector Connector.
Fill the empty cellsYou can optionally fill all empty cells with a default value
Apply import templateBrowse 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 settingDiscard all changes and start as if this were the first time the data source was ever imported
Get new columnsLook at the data source and fetch any new columns of data that have been added. Try and preserve existing columns and transformations.


Column Definition

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

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.

In Practice

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.

  1. Update your data source columns
  2. Edit your import template
  3. Click Get new columns
  4. Map or configure any new columns (look for the *)
  5. Save


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.



Table of Contents
  • No labels