Create a staging table used to store data before it is imported to the associated table.

Create a staging table

The staging table is used to map the data to the associated table. As part of the import process, the staging table is truncated before loading to the table.

The staging table must:

  • Be present before configuring a new file import.
  • Match the structure of the import file.
  • Have a name that begins with an alpha character.

Use the Add button to add new columns and Delete button to remove rows from the staging table.

Note: The following column names are not allowed in the staging file:

ALTER, AND, APPLY, AUTOINCREMENT, BEGIN, BETWEEN, BINARY, CASE, COLLATE, COLUMN, CONSTRAINT, CREATE, CROSS, CTE, DATABASE, DECLARE, DEFAULT, DELETE, DROP, ELSE, END, EXEC, EXECUTE, EXISTS, FOR, FOREIGN, FROM, FULL, GUID, HAVING, IDENTITY, IF, IN, INDEX, INNER, INSERT, INTO, IS, JOIN, LEFT, LIKE, MATCHED, MERGE, NEWID, NOT, NULL, ON, OR, OUTER, PIVOT, PRIMARY, PROCEDURE, RECURSIVE. RIGHT, ROWVERSION, SCHEMA, SELECT, SET, TABLE, THEN, TRIGGER, UNION, UNIQUE, UNPIVOT, UPDATE, USING, VALUES, WHEN, WHERE, WHILE, and WITH

Select Clear Data Settings in Data Import Definitions to clear members in the data model based on the members found in the staging table. 

For example, if the staging table or the source file contains entities such as London and Toronto, and the Clear Data Settings is set to Staging Source Values for the Entity dimension, rows with London and Toronto will be cleared from the data model before the data is imported.

Staging table example

If your import file contains columns for Account, Date, Entity, Currency, and Amount:

AccountDateEntityCurrencyAmount
10000202503USAUSD67000
11000202503USAUSD1240000
13100202503USAUSD15000
13200202503USAUSD10000
13300202503USAUSD58222
13400202503USAUSD125490

Your staging table must contain these columns:

ParameterDescription
Column NameThis lists the names of the fields in the staging table. For example, Account, Date, Entity, Currency, and Amount, need to exist in the same order as the file above. 
Data TypeThis shows the type of data each column holds. For example, string or float.
SQL Data TypeThis specifies the data type used in the underlying SQL database. For example, varchar for Account, Date, Entity, and Currency, and float for Amount.
SQL SizeFor varchar columns, this shows the maximum length in characters that can be stored.
SQL PrecisionDisplays relevant for numeric types such as Float and specifies the precision. In the example above, the Amount column has a precision of 52.
Primary KeyIf selected, this shows that the column is a primary key. A primary key uniquely identifies each row in the table.
NullableIf selected, this shows that the column can contain NULL value.