Create Connectors and Relationships

The first step in AnalyticsCreator is to define how the application connects to source systems and how source objects relate to each other. This establishes the metadata foundation for importing source tables, generating staging structures, and building downstream transformations.

A connector in AnalyticsCreator defines the technical access path to a source system, such as SAP, SQL Server, or another relational source. It contains the information required to read metadata and extract data from that source.

Once a connector is available, source objects can be imported together with their structural metadata. This includes table definitions, column definitions, and, where available, relationships such as foreign keys and references.

Purpose

Create a reusable source connection and establish the structural metadata required for downstream warehouse generation.

Core Concept

AnalyticsCreator separates source connectivity from warehouse modeling. The connector is responsible for technical access to the source. Imported metadata is then used to generate persistent staging structures and to support transformation design in later layers.

Relationships imported from the source are not the final warehouse model. They serve as technical and semantic input for later transformation design, join paths, and dependency handling.

Inputs / Outputs

Inputs

• Connection definition for the source system
• Authentication and access settings
• Selected source schemas / tables / views
• Optional source-side keys and relationships

Outputs

• Connector metadata stored in AnalyticsCreator
• Imported table and column metadata
• Imported foreign keys / references where available
• Available source objects for STG and downstream modeling

Internal Mechanics

1. Connector definition

The connector stores the technical configuration required to access the source. Depending on the provider, this may include server name, database, authentication method, schema selection, and extraction settings.

2. Metadata read

After the connection is established, AnalyticsCreator reads structural metadata from the source system. This typically includes:

• Tables and views
• Columns and data types
• Primary keys
• Foreign keys or references, if exposed by the source

3. Internal representation

The imported metadata is stored in the AnalyticsCreator repository and becomes the basis for further object generation. At this stage, no warehouse tables are created yet. The system is only building the metadata model that later drives SQL generation.

4. Relationship availability

If the source system provides foreign key or reference metadata, AnalyticsCreator can reuse that information as a starting point for later joins and transformation logic. If the source does not expose usable relationship metadata, relationships must be modeled explicitly later in the warehouse design.

Types / Variants

Connector variants

• Direct database connectors (e.g. SQL Server)
• ERP/source-system connectors (e.g. SAP)
• Query-based or manually defined source access

Relationship variants

• Imported foreign keys
• Imported references without enforced constraints
• Manually modeled relationships when source metadata is incomplete

Example

Assume a SQL Server source contains the tables dbo.Customer and dbo.SalesOrder.

The source exposes:

• Customer.CustomerID as primary key
• SalesOrder.CustomerID as foreign key to Customer.CustomerID

After import, AnalyticsCreator stores both tables and the relationship metadata. This does not yet create STG, CORE, or DM objects, but it provides the structural input for later generation steps.

Conceptually, the imported relationship may look like this:

SalesOrder.CustomerID -> Customer.CustomerID

This relationship can later support transformation design for facts, dimensions, and join paths.

When to Use / When NOT to Use

Use when

• You want to import source metadata into AnalyticsCreator
• You need STG generation based on source structures
• You want to reuse source-side keys and references as modeling input

Do NOT use as-is when

• The source metadata is incomplete or misleading
• The source relationships do not reflect the analytical model
• Business-level relationships differ from technical source constraints

Performance & Design Considerations

• Connector design affects metadata import quality, not only data extraction
• Imported relationships should be reviewed before reuse in warehouse design
• ERP sources often expose many technical tables; importing everything increases complexity unnecessarily
• Restrict imported scope to relevant schemas and objects where possible
• Do not assume source foreign keys are sufficient for dimensional modeling

Design trade-off:

• Broad import gives more metadata coverage but increases noise
• Selective import is cleaner but may require additional manual relationship modeling later

Integration with other AC features

• STG generation: imported source objects are the basis for persistent staging tables
• Transformations: source metadata is reused when defining dimensions and facts
• Dependency resolution: relationships can contribute to execution order and object dependencies
• Repository / acrepo: connector and source metadata are stored as versionable metadata definitions

Common Pitfalls

• Importing too many irrelevant source tables
• Assuming every source relationship should be propagated into the warehouse
• Using technical source keys as business keys without validation
• Skipping metadata review before building transformations
• Confusing source relationships with dimensional model relationships

Summary

Connectors define technical access to source systems, and imported source metadata provides the structural basis for staging, transformations, and downstream warehouse generation.