Data connections act as data sources for SmartIQ forms and dashboards, allowing projects to read and write data maintained by external systems. Data can be sourced from a variety of systems and stores. While some concepts are generic, specific details differ between technologies.

SmartIQ is packaged with a collection of connectors designed to connect with industry standard technology, such as SQL Server, PostgreSQL, XML, JSON, APIs, web services, etc. Their use is vast, from populating simple drop down menus, to sourcing records from large data sets, displaying reports, generating graphs, etc; through a Data Question or a Writable Data Object.

Important Data Connection Concepts

Below are a list of data connection concepts, in order of significance, that are very important to understanding how to correctly manage data sources. It is recommended that you read through all the conceptual articles as they show you how to correctly operate data sources, even if they don't directly instruct you how to operate those data sources.

🚧

Data retrieval limitation

SmartIQ can only retrieve and hold up to 1000 rows of data from an a data source. Therefore filtering larger data sets solely in SmartIQ is not possible. You should first filter the data at the data source or use an enterprise service bus (e.g. MuleSoft) before importing to SmartIQ.

🚧

It is not recommended to filter on answers that are generated using a formula in SmartIQ (eg: concatenating first and last name)

The use of filters on fields generated by formulas forces SmartIQ to load the entire datasource and calculate the formulas before applying the filter within SmartIQ. This is much slower and will potentially run into issues with limits on the maximum number of rows permitted to be loaded by a datasource.

Filters should be applied to unmodified fields as filtering these usually occurs efficiently within the datasource.

🚧

Text based datasources (eg: CSV and text files) should have all relevant fields pregenerated (eg: concatenate into a new column before import)

A text based datasource can be slow and inefficient, and may not filter and sort in SmartIQ as expected. The use of such datasources should be limited to simple selection lists, based on static data that rarely changes, and is limited in length (eg: a few hundred rows).

Data Connection Types

The types of connection available in SmartIQ

OneSpan is an external connection that only needs to be actioned in OneSpan Configuration Settings.

Connector Usage

A single data connection to a data store might provide access to many different records. For example a CRM system might read/write entities such as customers, locations, groups etc. Whereas a database might provide access to Tables, Views, Functions and Procedures. SmartIQ manages this with data objects, where one data connection owns many data objects, as depicted below.

Data Connections

Click the new data connection button in the data connections menu and select the desired Connection Type.

New data connections require a name, folder (mirroring the project folders), and a connection type indicating the data base type. The following fields have character limits: Name (100), Password Hash (1000), Username (200), Scope (100), Client Id (200), Client Secret (1000).

👍

Good practice

It is recommended to enable "Allow Connection Export" as it will make the data source part of a project definition allowing to import it in a different environment.

Connection Attributes

Connection attributes are additional variables that provide additional configuration of the connection. The variables are entered as a single string, semicolon ; deliminated, into the connection attributes text box.

Credentials

There are multiple types of credential that can be use to verify and protect connection access. The types of credential available depend on the connection type being implemented.

Data Objects

Data Objects represent specific data entities or concepts within a singe data connection.

Click on the Data Objects button after selecting a data connection. This will give a list of defined Data Objects for the Data Source. By default a newly-created Data Source has no Data Objects.

Creating a data object is done by clicking New Data Object. The data object's options are dependent on the connection type.

A data objects name and field names are limited to 500 characters in length.

Regardless of the connection type, the objects allow the administrator to Configure:

• The specific entity or technology to target, depending on the connection type
• Caching settings for offline app data viewing
• The object access, read only or read/write where possible
• Fields the data can be filtered on, known as key filter fields (e.g. lookup a customer by customer Id)
• Fields to ignore, known as schema fields, that are irrelevant, or disruptive, to business process which the data object is required for
• Fields that are appropriate/not appropriate to be shown in a User interface, known as display fields (e.g. show an employees name but not their salary)

After successfully creating a data object a success message, together with dialogues allowing the selection of Filter, Schema, and Display Fields, will display.

Data Object Read Only or Read/Write Access

SmartIQ supports maintenance of records (add, edit and delete) via a data connection, either during a form via a data modal or in the background without the user knowing upon the completion of an individual workflow step or final competition of a workflow.

Whereas read only data objects are straight forward, writable data object has more to think about for example, whether:

It is technically possible to write to the data object within the data connection;
Or It is even appropriate to maintain data viaSmartIQ with regards to the business process and security policy.

To define an individual data as writable, select Read/Write as the access setting for the data object's access property. Doing so will allow the object to be used with Writable Data Objects.

Data Object Filters and Fields

Whilst for most scenarios the default data object configurations is appropriate, SmartIQ offers much more granular control such as; defining what fields the data can be filtered by, how it should be filtered, and what to fields to display to end users or to omit completely from responses.

The term Field can change in context depending on the connection type. In general fields refers to the the column headers for data tables, objects in JSON, elements in XML, or GET endpoints for API connections.

Filter Fields

Filter fields are passed to data objects at run time to return targeted results. Their use and how they appear will depend on both their both their connection type and object type. For technologies such as web services and database stored procedures they will mimic input parameters, as for database tables they will list the columns available to build where clauses. Add filter fields as needed.

🚧

Filter fields will only filter for non-blank values when a field is set to required

IQ doesn't send requests when filters set to required are being sent a blank request

When filtering for 'logical blank' i.e = "" then that field must not be set to required else the request to get the database will not be passed.

Custom Fields

The 'add custom' field allows you to add fields that aren't already present in your data. This allows for the addition of placeholder fields for any data that is to be written to the data connector in future, or data relationships to be drawn in the Designer.

👍

Best not to duplicate already available filter fields.

Required Filters

Required filters are filter fields where a request to a data source will not occur when the filter value resolves to NULL or to an Empty String at run time.
These can be particularly useful for calling data-connections where accidental calls with empty filters can result in large (too large to process) data sets. Required filters protect form designers from common mistakes.

🚧

Filter value not presence of filter

A required filter does not mean that the filter must be provided each time the data object is used, rather as stated above that when the filter is used ensure its value does not resolve to a null or empty value.

The image below shows the field EmployeeNo configured as a required filter.

Auto Filters

Auto filters are filter fields that is automatically applied each time the data object is used to make a request, filter fields values will be sourced from the users profile at run-time. Auto filters are useful when the results returned always relate somehow to the user at run time, for example retrieve all staff that report to the current user. Ensuring that users can only see their own staff.
Auto filters aid mobile syncing where when configured can return records of interest to the current user for offline use.
The following image depicts the EmployeeNo Filter 'Auto Filtered' to the user's UserId. Thus at run time results will be returned depending on the user making the request.

Schema Fields

Schema fields allow the SmartIQ Administrator to omit fields that are not required by the business. This could be that they are redundant or not of interest to the business processes targeted by SmartIQ Using schema fields ensures responses are kept to a minimum to ensure optimal performance.

🚧

Support For Schema Fields

Schema fields are not supported by all technologies and will appear only where applicable.

By default where schema fields are applicable a select all Schema fields option will display. To specify particular schema fields uncheck the option and use the dialog provided to select the appropriate fields.
The image below shows a redundant title field being excluded from the schema field list. Essentially making it ignored to SmartIQ.

Display Fields

Display fields define which fields can be seen by end users during a Form. In the image below the salary field is excluded as a display field.