Using the Analytics Connector

This guide will show you how to link and unlink event forms from your data warehouse, sync historical data to your data warehouse and modify event forms linked to your data warehouse.

Connecting event forms

Linking an event form from Smartabase to your data warehouse triggers the creation of a table in your data warehouse with the same name as the event form.

Upon creation, this new table includes all the fields from the event form as column headers, but it does not include data.

Link a form to your data warehouse

1. Log in to the builder interface.
2. Go to the event form tool.
3. Open the form you want to connect.
4. Select the form name to access the form properties.
5. Tick the option to sync your form to your data warehouse.
6. Save your changes to the form.

Unlink a form from your data warehouse

To unlink a form from your data warehouse, you can simply untick the same box. When you do this, you'll see a message letting you know that the form is no longer synced to the data warehouse. Any additional data recorded using the form will not be added to the data warehouse nor will the table in the data warehouse be updated with any changes you make to the form (such as adding new fields, changing options or removing calculations).

The analytics connector will not delete existing data from your data warehouse when you unlink an event form. This is an action you must perform from within the data warehouse if required.

An open-source database manager such as Dbeaver can provide a simple GUI to help manage the database. Connect to the database using db user credentials with enough permissions to run the SQL command to remove/drop that table (e.g. DROP TABLE public.table_name).

Syncing existing data from your event forms

If you connect an event form with existing data and you want that data to become available in your data warehouse, then you will need to manually synchronize it. All data recorded in a form after it is connected to your data warehouse will automatically synchronize.

If you have a lot of historical data, syncing a form can take some time. The duration will depend on the performance characteristics of your data warehouse but may also be slower if your Smartabase server is under high load. Synchronization requests are placed in a queue (like resaves and migrations) and are handled in the order that you enact them. Remember that once an event form is synchronized with your data warehouse, the permission-based access rules (roles and groups) set up in Smartabase will not also be applied to your data warehouse. You must choose how you want to control access to the data warehouse separately.

Synchronize historical data from a form within your data warehouse

1. Log in to the builder interface.
2. Go to the event form tool.
3. Find the form you want to synchronize.
4. Select the option to sync the form history from the form management options.

Understanding the relationship between your event forms and your data warehouse

When you make changes to an event form such as changing its name or adding and/or removing fields, this has an effect on the table in your data warehouse where your data is stored.

Modifying the properties of an event form

Changing the name of a form

When you change a form name in Smartabase, the name of the linked table in the data warehouse will change accordingly.

• Any space in the form name in Smartabase is replaced with an underscore. For example, an event form called Daily Monitoring in Smartabase will have a table called Daily_Monitoring in your data warehouse.
• If you try to create a form in Smartabase with the same name as an existing table in the data warehouse, you will receive an error message that prevents you from proceeding.

Modifying the structure of an event form

Adding a new field to a form

If you add a new field in a form, a new column is added to the end of the table in the warehouse and all existing observations will be populated with a null value for that specific column. If you add a calculation to a form, you must perform a resave to calculate the results for any existing records in Smartabase. Once the resave is complete, you can manually synchronize the form with the data warehouse to populate existing rows in the table with the calculated values.

Changing a field name

When you change a field name in a form, the name of that field's column in the data warehouse table changes accordingly. Spaces in a field name are replaced by underscores in the column's name. For example, a field named Jump Height in an event form would become a column named Jump_Height in the linked table. Name sanitation is enforced in situations where there may be a name conflict. For example, in an event form with the following field names:

• Exercise 1
• Exercise_1

These fields would be named in the data warehouse's table for that event form as follows:

• Exercise 1 would become Exercise_1_2
• Exercise_1 would become Exercise_1

Removing a field

Deleting a field from one of your connected forms will remove the matching column from the table.

Changing the position of a field

The order of the columns in the data warehouse's table reflects the order in which the fields were added to the form in Smartabase. However, altering the placement of a field in an event form does not update the order of its column in the table.

Migrating a field name

Migrating a field in a form causes the name of the matching column in the table to change accordingly. Any spaces in the new field name will be replaced by underscores in the column name.

Modifying the data recorded by an event form

Changing a field type

If you change the type of field used within Smartabase, you can cause the data stored in that field to be lost. If this happens within Smartabase, the data stored within the data warehouse will reflect this. Most field types within Smartabase cannot be changed from one type to another. For example, changing a field from a number field to a single selection field will cause data loss, as numeric data does not get mapped to categorical data. It is possible to safely change a single line text field to a paragraph field and a single selection field to a dropdown field, but we don't recommend this approach. It is generally better that you add a new field to your form to capture the new data type.

Migrating an option name

If you migrate an option's name to a new one, the values in the data warehouse table for the relevant field which have the old name are converted to the new option name.

A note on character volatility

While Smartabase itself accepts many characters (such as $, #, *, %) within the names of event forms and fields or as data entered into forms, your data warehouse might have different character limitations. You should check that your Smartabase data is compatible with your data warehouse. You can use the following lists as a starting point for understanding which characters are supported by different data warehouses.

Supported | Not Supported

If you have any questions, please don't hesitate to contact our support team.