Setting up a SQL connector

Introduction

This page contains the instructions on how to create a new SQL connector and connect it to your source systems.

Prerequisites

It is assumed that the development tools described in Setting up a local test environment are installed.

Creating a New SQL Connector

If you want to build a new SQL connector you can download the template connector that contains the basic setup for a SQL connector.

Start Repository

It is advised to store your connector in a version control system, for example, GitHub.

Step	Action
1	Create a new repository that contains the complete connector directory. This directory must have the same structure as the downloaded (template) connector.

Configure Extraction

First, the extraction settings need to be set, to point to the desired source system and staging database. These configuration steps must be done once for each developer environment, and for the production environment.

Step 1: Choose Extraction Method

Each connector comes with a set of specific extraction methods. One of the extraction methods needs to be configured, to extract source data.

Extraction method	Description
Load-from-file	• Set up a CData job for the Load-from-file extractor. See Extractors: Load from file. • Use a custom query specific to this connector, which is provided in `extractors/load-from-file/instructions.md`.
Load-from-source	To load input data from a source system, use the specific instructions located in `extractors/load-from-source/instructions.md`. The load-from-source extractor is only available in connectors made for specific source systems, for example a Purchase-to-pay for SAP connector.

Step 2: Limit Source Data

Make sure to set the appropriate filters for your extractor, to load only the data that is necessary for your analysis. For example, limit the data to the last 6 months, or only a specific department that’s of interest.

Specific instructions on filtering input data can be found in instructions.md of the extractor.

Step 3: Configure Extraction Script

If the connector uses CData Sync for data extraction, the extract_cdata.ps1 script must be pointed to the correct job. Follow these steps to configure the extraction script.

Step	Action
1	In the `scripts/` directory, rename `config_template.json` to `config.json`. `config.json` is ignored by Git, such that development and production environments can use different extraction configs.
2	In `config.json`, change the text `JOB_NAME_CREATED_IN_CDATA` to the CData job that was just created.

If the connector does not use CData Sync for extracting data, then use the specific instructions provided in instructions.md.

Configure Transformation

To perform the transformation on the right staging database, profiles.yml needs to have a reference to your staging database for this connector. These configuration steps need to be done once for each developer environment, and for the production environment. Follow these steps to configure the transformation.

Step	Action
1	Copy the contents of `transformations/profiles.yml` of your connector.
2	Paste the contents in your personal `profiles.yml` to add those. The personal `profiles.yml` should be located outside of the connector directory.
3	Change the contents to point to your staging database.

Configure Load

To perform cache generation by using the load.ps1 script, it must be pointed to the Process Mining environment on which the corresponding app release is active. Follow these steps to configure the extraction script.

Step 1 can be omitted if it was already performed during configuration of the extraction.

Step	Action
1	In the `scripts/` directory, rename `config_template.json` to `config.json`. `config.json` is ignored by Git, such that development and production environments can use different extraction configs.
2	In `config.json`, change the text `PROCESS_MINING_ENVIRONMENT` to the environment on which the release is active.