Generate and Deploy the code

Introduction

Dimodelo will generate and deploy all the code to implement your Data Warehouse solution. This includes generating and deploying the code to create/modify your data warehouse databases (i.e. Staging and Data Warehouse databases) and any code required to implement the ETL/ELT, including stored procedures, polybase related objects and Dimodelo Shift jobs. The code is generated based on the design captured in Dimodelo Data Warehouse Studio.

Code can be generated from within Dimodelo Data Warehouse Studio through the Dimodelo menu. Dimodelo Data Warehouse Studio has 9 Generate and Deploy options.

• Generate> Generate Data Warehouse. Generates the DDL for the Staging and Data Warehouse databases.
• Generate > Generate ETL. Generates the SSIS packages to implement the ETL.
• Generate > Generate All. Generates both the Data Warehouse and SSIS ETL.
• Generate and Deploy > Generate and Deploy Data Warehouse. Generates the DDL for the Staging and Data Warehouse databases and executes it against the target server.
• Generate and Deploy > Generate and Deploy ETL. Generates the ETL and deploys it to the Deployment path defined in the configuration file.
• Generate and Deploy > Generate and Deploy All. Generates and deploys both the Data Warehouse and SSIS ETL.
• Deploy > Deploy Data Warehouse. Executes the previously generated DDL against the target Staging and Data Warehouse databases. The primary purpose is to update the Staging and Data Warehouse databases to match the latest version of the design.
• Deploy > Deploy ETL. Copies the previously generated SSIS Packages to the Deployment path defined in the configuration file.
• Deploy > Deploy All. Does both deploy Data Warehouse and Deploy ETL.

When in Development we recommend that you use the Generate and Deploy All option. This option refreshes everything, so no change is missed. As you become more familiar with the process, you may want to just Generate and Deploy the ETL, if your changes have only affected the ETL and not the structure of any tables.

Dimodelo Data Warehouse Studio supports generating code for multiple environments. Code must be generated for each environment, as each environment will have different source connections and different target Staging and Data Warehouse Servers.

It is important that the configuration is set up correctly before generating the code. This includes setting up connections strings for the Staging, Data Warehouse and Source databases for the target environment.

The configuration for Dimodelo Data Warehouse Studio projects, supporting multiple Environments is discussed in detail in the Project Configuration lesson.

Generate and Deploy the code for your Project

1. First, watch this video. It demonstrates how to generate and deploy code for your project. The video is from an older version of Dimodelo Data Warehouse Studio, so discusses some concepts that no longer apply. Never the less, it still provides a good demonstration of how to generate and deploy code from the development environment. The outdated concepts are:

• Dimodelo Data Warehouse Studio now generates Shift jobs and Stored Procedures instead of SSIS packages.
• Dimodelo Data Warehouse Studio no longer uses a temporary database during generation.
• The right-click Generate menu item now also deploys the generated code.
• The video refers to another config video. This is no longer relevant.

2. Make sure that the correct Project Configuration is selected for the environment you want to generate for. The Project Configuration is associated with a Dimodelo Data Warehouse Studio Configuration file and this determines connection strings that are used during generation etc. By default, there are 3 project configurations Development, Test and Production. These correspond with the 3 default configuration files Development, Test and Production. By default, the Development project configuration is selected. Make sure the Development project configuration is selected in your project.

   

3. Click the menu option. Dimodelo>Generate and Deploy> Generate and Deploy All. This option generates and deploys all the code to create/modify your Data Warehouse and Staging databases and ETL.
4. During code generation, the Generation Results Window will show a list of the files that have been generated. If the Generation Results Window is not visible, click the Generation Results tab at the bottom of the Visual Studio If there are errors, both the Generation Results Window and the Error List tool window will display the errors. You will know that the batch is complete when you see Datastore.xml at the end of the Generation Results list.

5. View the generated code. You can click on any file link in the Generation Results Window to open the Microsoft Windows default application for that file extension. Otherwise, you can view the content of the generated files in the Build project.

Troubleshooting

Sometimes code generation fails. This is usually due to an issue with the design or configuration of Meta data, or a network/authentication issue. A generation error is indicated by an X on the generation results row.

Do the following to try to identify the issue:

• Review the error message to get a better understanding of where the error message occurred. Click the … at the end of the generation results row reporting an error.

The output will look something like this:

A network-related or instance-specific error occurred while establishing a connection to SQL 
Server. The server was not found or was not accessible. 
Verify that the instance name is correct and that SQL Server is configured to allow remote 
connections. (provider: TCP Provider, error: 0 - No such host is known.)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, 
SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String 
newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, 
SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool 
pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager
sqlAuthProviderManager)

• Review the generated code. Click on the link next to the X to open the generated code artefact in the Microsoft Windows default application for that file extension. This may reveal an issue in your project design metadata.

Actions we suggest you take:

• Review the design meta data of the object being generated thoroughly for inconsistencies or omissions. This will usually be the cause of an issue.
• If the error is network, authentication or connection related, fix those issues outside of Dimodelo, or change the configuration to use the correct settings.
• Copy and paste any generated SQL query to SQL Server Management Studio (SSMS) and run it against the source database. This can sometimes identify issues with the design meta data.
• Visit dimodelo.desk.com for more information on known issues.
• Raise a ticket with our support.