Configure a Dimodelo Data Warehouse Studio project to target SQL Server

Configure SQL Server target databases

If you have chosen the SQL Server Target Technology for this tutorial, then this lesson describes configuring a project for SQL Server.

Otherwise, you can skip this lesson.

To complete this lesson you must have chosen the “SQL Server” Target Technology in the “Select Data Warehouse architecture” dialog.

Prerequisites

You need to access to a local or network available SQL Server 2012, 2014, 2016, 2017 or 2019. You should have the ability to create databases on the server.

Dimodelo will create the SQL Server databases required.

Configuration connection strings for the Data Warehouse

For each Configuration in the Dimodelo project, it’s necessary to define the connection strings to the Data Warehouse, Staging, Transform and Batch databases. Your Development, Test and Production etc configurations will target a different set of databases.

1. Double click the configuration file in the Dimodelo Project.

2. Choose the connection you want to configure.

3. Edit the connection information:

• Use the “.Net Data Provider for SQL Server” (aka SqlClient).
• Use either Windows or SQL Server authentication with the SQL Server login you set up in the prerequisites.
• Select or type in the name of your target Data Warehouse.

4. Do the same for all four target connections Data Warehouse, Staging, Transform and Batch databases. The database needs to be the same for all four.

Note: It necessary that the  Staging and Transform Data Stores share the same database. Schemas are used to separate different entity types within the one database.

Configure the SQL Server Version

1. Click the Data Stores tab.
2. For the Staging DataStore, Storage technology Category, select which version of SQL Server you are targeting:

3. Do the same for the Data Warehouse Storage technology category.
4. Do the same for the Transform Storage technology category.
5. Click Save to save the changes.