Flexible file uploads

Elimity Insights v3.41.0 included significant improvements to data imports via file uploads. This page explains how to use this feature, and also provides some technical details about what happens behind the scenes.

We designed the 'flexible file uploads' feature for two types of users:

1. Technical experts that know how to convert application-specific export files into entities and relationships for Elimity Insights

2. Application owners that know how to get the export files and upload them in Elimity Insights

The general idea is that technical experts define which files are needed and how Elimity Insights should process them, allowing application owners to simply export and upload without the need for any knowledge about what happens behind the scenes.

1

Technical expert creates file upload definitions

To start using the 'flexible file uploads' feature, a technical expert should define which export files are needed for the data import. More specifically, Elimity Insights expects a list of file upload definitions, each of which containing the following information:

• A textual label to help application owners know what to upload.

• Technical details about which file types to accept (e.g. CSV, Excel, ...).

• A flag indicating whether application owners can upload multiple files for the definition.

For example, let's say we have an HR application containing employee records that we want to review in Elimity Insights. The HR application supports exporting these employees to CSV in batches of 1000 employees per file. In this case the technical expert should create a file upload definition in Elimity Insights with the following configuration:

• A textual label of 'Employees', so the application owner knows to only upload export files containing employee records.

• Uploaded files should have a .csv extension.

• Multiple files are allowed, because the HR application might contain more than 1000 employees.

2

Technical expert configures SQL queries

Having defined which files are needed for the data import, a technical expert should now configure the business logic to convert these uploaded files into entities and relationships for Elimity Insights. More specifically, Elimity Insights expects an SQL query for each entity type and relationship type in the target source. These SQL queries can refer to named parameters (e.g. $p0, $p1, ...) which Elimity Insights will replace with glob patterns matching the uploaded files' paths. Technically this is what happens behind the scenes:

1. Application owner uploads one or more files for each definition created by the technical expert.

2. For each definition, Elimity Insights copies the uploaded content into temporary file(s). Elimity Insights also builds list containing the paths of these files.

3. For each entity type in the target source, Elimity Insights executes the SQL query configured by the technical expert, passing the file paths from step 2 as named arguments. More specifically, each file upload definition corresponds to one named argument. If the definition allows multiple files, then Elimity Insights provides a list of file paths instead of a single file path. Each of the query's resulting rows should correspond to an entity in the final data import:

   1. The number of columns must be equal to n + 2, where n is the number of included attribute types.

   2. The first column must have a textual type and represents the entity's unique identifier. This value must never be NULL.

   3. The second column must also have a textual type and represents the entity's human-readable name. This value must never be NULL.

   4. The remaining columns must follow the ordering of included attribute types, and their type has to match with the attribute's data type. A NULL value corresponds to an attribute that's not assigned.

4. For each relationship type in the target source, Elimity Insights follows a process that's very analogous to step 3. The main difference is that the first two columns in SQL query results should represent 'from' and 'to' entity identifiers instead of 'id' and 'name'.

It's important to know which SQL query engine we rely on behind the scenes. This version of Elimity Insights uses DuckDB v1.3.0; we highly recommend reading through their documentation before writing SQL queries.

3

Application owner uploads export files

Having set up the file upload definitions and SQL queries, application owners can now simply upload their export files into Elimity Insights without knowledge about any processing that happens behind the scenes.