Configure a Dimodelo Data Warehouse Studio project to target Azure SQL database

Configure Azure SQL database target databases

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

Otherwise, you can skip this lesson.

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

Prerequisites

The target Azure SQL database must exist in your Azure Tenant. Dimodelo Data Warehouse Studio does not create the database. The minimum database level is Standard 100 (S3). This is the first level that supports Columnstore indexes.

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.

1. 3. Edit the connection information:

1. • Only the “.Net Data Provider for SQL Server” (aka SqlClient) supports connectivity to Azure SQL Database.
   • Use 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.
   • Note the server name includes the tcp: prefix and ,1433 port. e.g. tcp:servername.database.windows.net,1433

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: Because Azure SQL Database doesn’t support cross-database queries, it necessary that the Batch, Staging, Transform and Data Warehouse Data Stores share the same database. Schemas are used to separate different entity types within the one database.

5. Click Save to save the changes.