In a data warehouse, layers are a crucial aspect of its logical structure. Users have the ability to define a variety of layers, each serving a specific purpose. Below are six primary types of layers commonly used in a data warehouse architecture, along with their functions and interconnections.

This configuration facilitates an efficient workflow, transforming raw data sources into insightful, user-accessible information.

Each layer plays a distinct role in the data journey—from acquisition to end-user presentation—supporting governance, transformation, historization, and analytics.

1. Source Layer (SRC)

• Purpose: Acts as the foundational logical data layer containing external data sources.
• Characteristics:
  • Not part of the actual data warehouse storage.
  • Serves as the entry point for incoming external data.
  • Tables and transformations cannot be created in this layer.

2. Staging Layer (IMP)

• Also Known As: Import Layer
• Purpose: Loads and structures raw data from the Source Layer into tables for further processing.
• Characteristics:
  • Temporarily stores incoming data.
  • Frequently refreshed with the latest imports.
  • Prepares data for historization and persistence.

3. Persisted Staging Layer (STG)

• Purpose: Begins the commitment to data historization and traceability.
• Characteristics:
  • Stores data from the Staging Layer persistently.
  • Maintains historical records of changes.
  • Considered the first "true" layer of the data warehouse.

4. Transformation Layer (TRN)

Layers

• Purpose: Applies additional logic and refinements to the data.
• Characteristics:
  • Optional, but useful for cleansing, deduplication, or complex business logic.
  • Ensures high data quality and consistency.
  • Acts as a bridge between raw and modeled data.

5. Data Warehouse Layer (DWH)

• Purpose: Converts structured data into analytical models (e.g., facts and dimensions).
• Characteristics:
  • Core repository of business-ready data.
  • Supports advanced querying, reporting, and data analysis.

6. Data Mart Layer (DM)

• Purpose: Provides business users with access to relevant datasets in a user-friendly structure.
• Characteristics:
  • Often adopts star schema or other analytical models.
  • Optimized for reporting tools and dashboards.
  • Represents the interface between the data warehouse and the end-user.

Together, these layers enable a modular and governed approach to building scalable and maintainable data warehouse solutions in AnalyticsCreator.

Schemas

A schema is the Microsoft SQL Server schema to which a Data Warehouse (DWH) object belongs.
Each schema should be assigned to a specific layer, and each layer can contain multiple schemas.

Stars

A Star is a part of a data mart layer. The data mart layer can contain several stars. Each star corresponds to a schema. If you create an OLAP (Online Analytical Processing) model, each star will produce one OLAP cube (Tabular and Multidimensional).

Galaxies

A Galaxy is a group of several stars. Each star should belong to a galaxy.

Galaxy definition window: