System Types

Source/Staging Connections

With Source/Staging connections, DWD creates a SQL Server Integration Services (SSIS) connection to retrieve data from the source system and return it to the staging area. This connection is executed on a regular basis.

The staging system has two connections:

  • The staging system destination connection defines the server containing the staging database which is always the data warehouse server.

  • The staging system source connection defines the database(s) from which data is extracted.

Source/Operational Connection

With Source/Operational connections, DWD creates a connection to a MS SQL server. This connection does not create a package that retrieves data, but a database view.

The operational system has two connections:

  • The data warehouse system destination connection defines the server containing the data warehouse.

  • The staging system source connection defines the database(s) from which data is extracted.

Note: This connection requires that DWD and the database be located on the same server.

Linked Connection

By definition, a linked source system requires no data staging. With Linked connections, DWD connects to a source system by generating an open query command. The connection returns a database view of the source.

Catalog Connection

The catalog system connection defines where the catalog objects are created for temporary transformations. The catalog source objects are created in the catalog database located on the data warehouse server. The catalog system needs only one system connection and that is to the model's staging database.

Datamart Connection

The data mart system is set up on the data warehouse system. One connection defines the destination store distributed data store connection is used to connect a data mart on a server and platform other than the DWD server.