Setting Up Connectors in AnalyticsCreator

Connectors in AnalyticsCreator allow users to establish data source connections, enabling efficient data management and analysis. Here’s a comprehensive guide to understanding and setting up various connectors.

Navigating Connectors

To create or edit a connector, navigate through the toolbar menu:

Connectors define the data source logic.

Here’s a list of connector types supported by AnalyticsCreator:

• MS SQL Server
• Oracle
• CSV
• Excel
• DuckDB (Parquet, CSV, S3)
• MS Access
• OLEDB
• SAP (using Theobald connector)
• ODBC

Connection String and Templates

AnalyticsCreator provides a friendly interface for generating connection string templates.

For several connector types, users can access these templates by clicking the “Template” button. Here’s an example template:

PROVIDER=SQLNCLI11;Data Source=[SERVER];Initial Catalog=[DATABASE];Integrated Security=SSPI;

Make sure to replace the placeholders [SERVER] and [DATABASE] with the actual server and database names.

CSV Connector Properties

The CSV connector has unique properties enhancing file handling capabilities. Users should pay attention to these additional settings to ensure seamless file integration and processing.

Row Delimiters

When defining row delimiters, users can utilize specific abbreviations for ease. These include:

• {CR} for Carriage Return
• {LF} for Line Feed
• {t} for Tabulator

These specifications enable seamless formatting and data structuring within your source files.

Automating Data Source Descriptions

For automatic data source description retrieval, ensure your connections to these data sources are active and functional. This automation simplifies data management and improves operational efficiency.

Cloud Storage for Connectors

Store connector definitions and associated data sources in the cloud. Cloud storage provides a durable and accessible platform for managing your data across different repositories, enhancing collaboration and data security.

Encrypted Strings

We highly recommend keeping your connection strings encrypted.

To encrypt your string, simply click on Options → Encrypted Strings → New.

To use your encrypted strings in your sources, enclose the name you’ve created with # on both sides.

For example, if your DSN=DuckDB, the connection string will be #DuckDB#

Configuring AnalyticsCreator

The source contains a description of the external data.
Each source belongs to a connector.
Each source has columns, and references (Table Keys) between sources can be defined.

To open the source definition, use the "Edit source" option from the source context menu in the navigation tree or diagram.
To add a new source, use the "Add new source" option from the source context menu in the navigation tree or diagram.

Below is a typical source definition:

The properties of sources depend on the connector type and the source type.

There are three source types:

• TABLE
• VIEW
• SAP_DELTAQ
• QUERY

For the QUERY source type, the source window will display an additional tab containing the query definition. You cannot create a source manually. The only source that can be created manually is the CSV

The user can check for changes in the source and propagate any detected changes to the data warehouse objects. To check for changes in all connector sources, use the connector context menu and select "Refresh all sources" in the navigation tree.

To check for changes in imported connector sources only, use the connector context menu and select "Refresh used sources" in the navigation tree.

To check for changes in a specific source, use the source context menu and select "Refresh source" in the navigation tree.

Refresh Source Options:

The following refresh options are available:

• Detect differences: Detects changes in the source but does not modify the repository.
• Delete missing sources: Deletes any missing sources from the repository.
• Refresh source descriptions: Refreshes the descriptions of the sources.
• Refresh columns in imported tables: Refreshes columns when there are new or changed source columns.
• Delete missing columns in imported tables: Deletes columns in imported tables if the source columns have been deleted.
• Refresh primary keys in imported tables: Updates primary keys if the source’s primary key has changed.
• Refresh descriptions in imported tables: Updates descriptions of imported tables and columns.