SQL Server

AnalyticsCreator provides a metadata-driven design layer for SQL Server data warehouse projects and generates native database, SSIS, deployment, and analytical artefacts. SQL Server and the selected Microsoft services execute the resulting solution without requiring an AnalyticsCreator production runtime.

Overview

Microsoft SQL Server is a primary target technology for AnalyticsCreator-generated data warehouse solutions. AnalyticsCreator uses a metadata-driven design to generate native SQL Server database structures, processing logic, SSIS packages, deployment artefacts, and supported analytical models.

SQL Server is also used for the AnalyticsCreator project repository, where the metadata describing the project is maintained.

AnalyticsCreator is a design-time application. It generates native Microsoft artefacts, while production data processing and analytical workloads are executed by SQL Server, SSIS, Analysis Services, Power BI, or other configured Microsoft services.

Supported Services and Components

SQL Server Database Engine
SQL Server Integration Services (SSIS)
SQL Server Analysis Services (SSAS) Tabular
SQL Server Analysis Services (SSAS) Multidimensional
Power BI semantic models
SQL Server-based AnalyticsCreator project repository

What AnalyticsCreator Generates

Depending on the project design and configuration, AnalyticsCreator can generate:

Data warehouse structures

Source import and staging tables
Persistent staging structures
Historised structures
Transformation views and tables
Facts and dimensions
Data marts and other analytical structures

SQL processing logic

Stored procedures
Data loading logic
Transformation logic
Historisation logic
Persistence logic
Snapshot processing

SSIS packages

Source ingestion
Data warehouse processing
Execution of generated loading and transformation processes
Workflow orchestration and dependency handling

Analytical models

SSAS Tabular models
SSAS Multidimensional models
Power BI semantic model artefacts
Relationships, measures, hierarchies, and other supported analytical definitions

Deployment artefacts

DACPAC packages for supported database deployment scenarios
Generated SQL artefacts
SSIS project and package artefacts
Supported analytical model artefacts

Supported Modelling Approaches

AnalyticsCreator supports multiple modelling approaches for SQL Server data warehouse solutions, including:

Dimensional modelling with facts and dimensions
Third Normal Form models
Data Vault modelling
Hybrid modelling approaches
Historised structures using Slowly Changing Dimension patterns
Snapshot-based historisation

The physical structures generated in SQL Server are determined by the project design. AnalyticsCreator does not require every project to use the same fixed sequence of warehouse layers.

SQL Server Repository

AnalyticsCreator uses a SQL Server repository to store the metadata that defines the project.

This metadata describes the project design, including source structures, mappings, transformations, warehouse objects, analytical structures, and other configuration required for generation.

The repository is design metadata. It is separate from the business data processed by the generated data warehouse solution.

Synchronisation and Generation

AnalyticsCreator uses the project metadata to generate and synchronise physical structures with the target SQL Server environment.

Synchronisation can create or update database structures required by the model. It does not represent the normal production execution of the data warehouse and should not be confused with loading business data through the generated processing workflow.

Changes to the model can therefore be reflected in the generated SQL Server structures through the appropriate AnalyticsCreator generation and synchronisation processes.

SSIS Orchestration

AnalyticsCreator can generate SSIS packages for ingestion, data warehouse processing, and workflow orchestration.

The generated packages coordinate the processing steps derived from the AnalyticsCreator project, including source loading and execution of generated SQL processing logic.

SSIS executes these workloads independently of AnalyticsCreator once the generated packages have been deployed.

Historisation, Persistence, and Snapshots

AnalyticsCreator can generate processing patterns for maintaining historical data and materialising transformation results.

Supported project designs can include:

Slowly Changing Dimension processing
Valid-from and valid-to historisation
Persistent staging
Persisted transformation results
Snapshot-based processing

The appropriate approach depends on the source behaviour, analytical requirements, data volumes, and project architecture.

Deployment and Execution Model

AnalyticsCreator separates model design and generation from production execution.

AnalyticsCreator stores the project design as metadata in its SQL Server repository
The design is used to generate native SQL Server, SSIS, and supported analytical artefacts
Generated database structures are created or deployed to the target SQL Server environment
Generated SSIS packages orchestrate ingestion and processing where SSIS is selected
SQL Server executes the generated SQL processing logic
SSAS or Power BI can consume the resulting analytical structures where configured

AnalyticsCreator is not required as a production runtime after the generated artefacts have been deployed.

CI/CD and Version Control

AnalyticsCreator projects and generated SQL Server artefacts can participate in source-controlled development and deployment processes.

AnalyticsCreator project definitions can be saved as file-based repository structures
Project definitions can be stored and reviewed in source control
Generated database artefacts can be incorporated into CI/CD workflows
DACPAC packages can be used for supported database deployment scenarios
Generated SSIS and analytical artefacts can participate in controlled release processes
Development, test, and production environments can be managed through the customer's deployment process

The exact CI/CD implementation depends on the source-control system, deployment tooling, SQL Server environment, and governance requirements used by the customer.

Connectors, Sources, and Targets

SQL Server as a source

SQL Server can be used as a source system. AnalyticsCreator can read source metadata and generate the corresponding ingestion and downstream processing structures as part of the project design.

Other supported sources can include:

SAP systems through supported connector technologies
Relational databases
ODBC-accessible sources
Files and other supported source connectors

Relevant targets and downstream technologies include:

SQL Server data warehouse databases
SSIS packages
SSAS Tabular models
SSAS Multidimensional models
Power BI semantic models

Prerequisites, Limitations, and Notes

A supported SQL Server environment is required for the AnalyticsCreator repository
A supported target SQL Server environment is required when SQL Server is used as the generated data warehouse target
SSIS must be available when SSIS-based orchestration is selected
SSAS must be available when Analysis Services models are part of the selected architecture
Required source connections and permissions must be configured
Performance depends on factors including model design, data volume, indexing, transformation complexity, load strategy, and SQL Server configuration
Project-specific functionality should be validated against the AnalyticsCreator and Microsoft SQL Server versions used by the implementation

Design Considerations

Select historisation and persistence patterns according to the analytical and operational requirements
Use persistent staging where it supports recovery, reprocessing, historisation, or source-load requirements
Use persistence where materialising transformation results provides an appropriate processing or performance benefit
Design indexing and physical database structures according to workload and data volume
Keep mappings, transformations, and processing dependencies in the governed AnalyticsCreator design where appropriate
Avoid manually duplicating logic that is generated and maintained from AnalyticsCreator metadata

Example Use Cases

Building a SQL Server data warehouse with generated SSIS ingestion and processing
Moving an existing manually maintained SQL Server data warehouse towards metadata-driven development
Generating dimensional data marts for analytical consumption
Implementing historisation and snapshot processing
Generating SSAS Tabular or Multidimensional analytical models
Generating Power BI semantic models from the governed analytical design
Including database, SSIS, and analytical artefacts in source-controlled deployment processes