[et_pb_section][et_pb_row][et_pb_column type=”1_4″][et_pb_sidebar admin_label=”Sidebar” orientation=”left” area=”et_pb_widget_area_1″ background_layout=”light” /][/et_pb_column][et_pb_column type=”3_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

Project Configuration

[/et_pb_text][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

Introduction

With Dimodelo Data Warehouse Studio new projects have a default development, test and production configuration file that targets the locally installed instance of Microsoft Server (if there is one), and generate code to the Generated_Code folder in the project directory. While this is suitable for demonstration and proof of concept purposes, a real project that must support deployment to multiple environments requires a different configuration. This chapter describes our recommended approach to project configuration to support multiple environments. The first step is to define a Project folder structure to cater for multiple environments. Dimodelo Solutions has a recommended Project folder structure. Read the Step 1 Set up the Solution Folder Structure section for more details. Dimodelo Data Warehouse Studio has flexible code generation and deployment capabilities allowing it to support:

  • Multiple target environments (e.g. Development, Test, Prod)
  • Generating code to different file locations
  • Deploying to non-local servers.

Typically there would be a Development, Test and Production environments. Each of these environments may have different SQL Server Instance Names, Source System Connection strings and deployment paths. To support multiple environments it is necessary to create a Dimodelo Data Warehouse Studio Config file for each environment. Each configuration file contains connection strings, and deployment paths specific to the target environment. To understand DA Config file set read the Step 2 – set up Dimodelo Data Warehouse Studio Configuration Files section. Each Dimodelo Data Warehouse Studio Configuration file is associated with a different Visual Studio Project Configuration. The user must select a Visual Studio project configuration prior to executing a Generate, Deploy and Batch operation. DA uses the DA Config file associated with the currently selected Visual Studio project configuration to determine target servers etc for the action. To generate and deploy to a different target environment, you simply switch between different Visual Studio Project Configurations. Visual Studio Project Configurations are a standard Visual Studio feature that allow you to define different environmental properties for each target environment and build for those different environments. To learn more about using Visual Studio Project Configurations with DA read the Step 3 –Project Configuration section. Finally… the output paths for the generated code artefacts must to be modified to match the Project folder structure setup in step 1. See the Step 4 – Set the Output Paths for the Generated Code section for more details.

Step 1 – Set up the Solution Folder Structure

Because Dimodelo Data Warehouse Studio uses information in the DA Config file to generate SSIS configuration files and set configuration file paths in packages, it is necessary to generate a version of the SSIS ETL for each target environment. To facilitate this we recommend a particular folder structure and generated code output structure. We recommend the following folder structure for your solution

  • Solution (Source Code)
    • Dimodelo (The Dimodelo Data Warehouse Studio Project)
    • Workflow. (Contains the Batch Workflow file(s))
    • Cube (The SSAS Multidimensional Project if required)
    • Tabular (The SSAS Tabular Project if required)
    • Custom (The generated code)
      • Custom Database (Custom Stored Procedures (if required))
        • DEV
        • TEST …
        • PROD …
      • SSIS (Custom SSIS Project if required)
      • Custom Config (Custom SSIS Config files for the Custom SSIS project)
        • DEV
        • TEST …
        • PROD …
  • Build. (Build folder for full Solution automated Build)
  • Package (Package folder for full Solution automated Build)

Step 2 – set up Dimodelo Data Warehouse Studio Configuration Files

As discussed in the introduction, typically you need a DA Config file for each of the environments you are going to deploy the solution too, i.e. Development, Test, Production etc. This section describes how to create a DA config file. The following steps describe how to set up a DA config file for a TEST environment using our recommended Solution folder structure.

  1. Right click the Config Folder in the Solutions Explorer, and select Create New.
  2. A new DA Configuration File is added to the folder and opens in the content window.
  3. You can change the name of the DA Configuration File by selecting the DA Configuration File in Solution Explorer and changes the File Name property in the Properties Tool Window. If you don’t see the Properties Tool Window, the from the Visual Studio menu click View>Properties. In this example the name would be set to TEST.cuf. .cuf is the extension for a DA config file.
  4. Set the ETL code (SSIS) deploy path, by setting the ‘ETL Code Deploy Path/Server Connection String’ property on the Connections tab. Without a deploy path, the deployment process for ETL will fail. For Development environment DA Config files, set the deployment path to a local folder path (e.g. F:\ DW\ SSIS).  For external server deployments (i.e. TEST and PROD environments) set the property to the network path URI for server. For example, if our TEST server was TSTSR01 then the property might be set to \\TSTSR01\F$\DW\SSIS. F:\DW\SSIS on the TSTSR01 server. DA currently only supports the package file deployment model for SSIS 2012 projects. The path can also be relative e.g. ..\Deploy.
  5. Modify Data Warehouse and Staging Database Connections to match the target server as required. On the Connections tab, use the configure buttons to create connection strings to your Data Warehouse and Staging Databases for the target environment. In the TEST example we would set the connection string of the Data Warehouse to Data Source= TSTSR01;Initial Catalog= DW_Warehouse;Integrated Security=True
  6. Modifying Source System Connection strings.

The ‘Connection strings’ table allows you to specify environment specific connections strings for the source system connections specified in your Dimodelo Data Warehouse Studio project. This way you can define different source connections for each environment, i.e. development, test, production etc. The name corresponds to the Connection Manager name in the project. You can modify the connection strings for each DA Config File (targeting a different environment) using the Configure button on each row. To add or modify a Connection strings:

  • In the Name field, type the name of the connection from your project. Note this must ‘Connection Manager Name’ in the Connection Manager dialog in your Dimodelo Data Warehouse Studio Project exactly.
  • Use the configure button to configure your connection string. Alternatively you can type or paste a connection string into the connection string field. To manually set the Server Name and Provider fields, view the code of your connection in the Dimodelo Data Warehouse Studio project, and use the values of the ServerName and Provider elements.

Note: DA will maintain all DA Config files, as you add, remove Connection Managers in the project. It only maintains DA Config files that are associated to a Visual Studio Project Configuration. While new connection managers are added to all DA Config files, modifying a Connection Manager only maintains the DA Config file associated with the currently active Project Configuration. For example if a connection manager connection string is modified, it will only modify the connection string in the DA Config file associated with the active project configuration.

  1. Additional Configuration

The configuration tab is used to set some defaults for Data Warehouse tables. In most cases it is not recommended to change anything on this tab.

  1. Data Warehouse Database Schema: Use this field to specify the schema name of Data Warehouse tables in the Data Warehouse database. The default is dbo. Note DA currently does not use this property when generated code.
  2. Dimension Table Prefix: The dimension table prefix is applied to the front of the table name of every dimension table in your schema. This allows you to keep your design free of implementation details. Note DA currently does not use this property when generated code.
  3. Fact Table Prefix: The dimension table prefix is applied to the front of the table name of every fact table in your schema. This allows you to keep your design free of implementation details. Note DA currently does not use this property when generated code.
  4. Custom Meta Data: The custom Meta Data table can be used to create custom meta data that can be used in the generation, deployment or batch execution processes.

Deleting a DA Config file

If you delete a Config file, be sure to also change any project configurations that access it, to access a DA Config file. If you don’t you will get an error message saying the DA Config file could not be found, every time you attempt to add/change or delete any connection manager.

Step 3 –Project Configuration

Once a DA Config file has been created it needs to be associated with a Visual Studio Project Configuration, so that DA can use it.

[/et_pb_text][et_pb_image admin_label=”Image” src=”https://www.dimodelo.com/wp-content/uploads/Project-Config.jpg” show_in_lightbox=”off” url_new_window=”off” animation=”off” sticky=”off” /][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

  1. Create a new Visual Studio Project Configuration. When the Generate, Deploy and Batch processes are executed, Dimodelo Data Warehouse Studio uses the active Visual Studio Project Configuration to determine which Dimodelo Config file to use in the process. Before executing the Generate, Deploy or Batch processes, the user will select the desired project configuration. Project Configurations are standard Visual Studio functionality. Dimodelo Data Warehouse Studio has extended Visual Studio Project Configurations to include Dimodelo Data Warehouse Studio specific project configuration. For more information about Visual Studio Project and Solution Configurations visit MSDN: Build Configurations. Each DA config file must be associated with a Project Configuration. To understand how to create a new Visual Studio Project Configuration visit How to: Create and Edit Configurations. We recommend you create a new project Configuration for each DA Config file that matches the name of the DA Config file.

[/et_pb_text][et_pb_image admin_label=”Image” src=”https://www.dimodelo.com/wp-content/uploads/Proj-Config.png” show_in_lightbox=”off” url_new_window=”off” animation=”off” sticky=”off” /][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

  1. Modify the generation output path to a path specific to the TEST DA configuration file. You should generate the output for each target environment to a different folder. To modify the generation output path, click the Output path row and use the … Browse button to select/create the path to the output folder. In our example the output path would be C:\Bulid\TEST, assuming the solution is saved to C:. You can also use relative paths (relative to project file) which we highly recommend if there are multiple developer using Dimodelo e.g. ..\..\Build\Test. After generation the C:\Bulid\TEST folder will contain all the generated code for the TEST environment, as defined for this DA Config environment.    When defining generation paths, make sure that the generation path in one configuration file is not the child of another path in another configuration file. The SSIS deployment action deploys all packages in the generated code path, including child folders. When deploying the code using the config file that contains the parent path, you will deploy the SSIS packages in that path, plus the packages in the child folders of that path, which includes the packages in the folder that contains the generated code from the other config file.This will causes deployment issues.
  2. Configuration File Location. Once you have created the new project Configuration (e.g. TEST), set the ‘Configuration File Location’ property (see above) to the path of the DA Config file (e.g. C:\Solution\Dimodelo\Config\TEST.cuf). If you delete or rename a Config file, be sure to also change any project configurations that access it. If you don’t, you will get an error message saying the DA Config file could not be found, every time you attempt to add/change or delete any connection manager.
  3. Is Development Environment. Set this property too false for a development environment project configuration. Set it to True for all other environments. DA will use this in the future as a safeguard to warn users before deploying to a Production environment.
  4. Target SQL Server Version. This property determine which version of SQL Server the Generate process generates SSIS packages for.
  5. Workflow Location. This property determines what workflow file the Batch process uses, when running a batch from Dimodelo Data Warehouse Studio with this VS project configuration.

Step 4 – Set the Output Paths for the Generated Code

In the Generation/Templates folder of your DA project there are a number of .mnf manifest files that control the generation of different types of code artefacts. Each file contains xml, similar to that shown below:

<Generation_Template_Manifest>
    <Output_Relative_Location>SSIS/</Output_Relative_Location>
    <Template_Display_Name>SSIS 2008 Package Config for Staging</Template_Display_Name>
    <Generation_Engine_Name>SSIS 2008 Packages Generation Engine</Generation_Engine_Name>
    <Generation_Result_File_Name_Pattern>Staging.dts Config</Generation_Result_File_Name_Pattern>
    <Template_Relative_Location>SSIS_Staging_Configuration.xslt</Template_Relative_Location>
    <OperatesOn>Staging</OperatesOn>
    <Generates_For_Collection>
       <Generates_For_TargetType>ETL</Generates_For_TargetType>
    </Generates_For_Collection>
 </Generation_Template_Manifest>

The Output_Relative_Location determines where the output for that code artefact type is written, relative to the ‘Default Generation Output Path’ of the currently active DA Config file. In the example above, this is set to SSIS/. What this means is that for the artefact type represented by this manifest (which happens to be a Staging SSIS configuration file) the resulting generated artefact is written to the ‘Default Generation Output Path’/SSIS/ folder. In our example ‘Default Generation Output Path’ = C:\Solution\Data Warehouse\TEST so the Staging SSIS configuration file is written to C:\Build\TEST\SSIS\. This works for all different DA Config files so, for example a DEV DA Config file might have a ‘Default Generation Output Path’ = C:\Solution\Data Warehouse\DEV so when generating with this DA Config file the Staging SSIS configuration file goes to C:\Build\DEV\SSIS. To change the output path of all generated artefacts to the recommended solution folder structure we recommend you make the following changes.

  1. Create a new Manifest folder under the Dimodelo Folder in the Solution and copy all the .mnf files from the generation folder into the Manifest folder. Why? From time to time Dimodelo Solutions issues updates. This may mean you need to overwrite the Generation folder of an existing project. When you overwrite the generation folder, you overwrite the manifests in that folder. By placing the manifests in their own folder it’s possible to reapply these changes at a later date.
  2. Change the manifests.

The following manifests apply to DDL generation. The Output_Relative_Location should be set to ./Database/Folder Name where folder name is the last folder name in the existing Output_Relative_Location.

  • Clean_Up.mnf
  • Control_Properties.mnf
  • Create_Databases.mnf
  • Staging_Tables.mnf
  • Table_Synonyms_Dimensions.mnf
  • Table_Synonyms_Facts.mnf
  • Table_Synonyms_Staging.mnf
  • Transform_Fact_Bridges_Pattern.mnf
  • Warehouse_Tables.mnf
  • Indexes_Staging.mnf
  • Linked_Servers.mnf

The following manifests apply to SSIS package generation. The Output_Relative_Location should be set to ./SSIS/. You may only need to modify the 2008 or 2012 manifests depending on which version of SQL Server you are generating for.

  • ExtractPattern2008_GenTemplate.mnf
  • ExtractPattern2012_GenTemplate.mnf
  • FactTransactionStandard2008_GenTemplate.m
  • FactTransactionStandard2012_GenTemplate.m
  • SSIS2008ProjectGenerationTemplate.mnf
  • SSIS2012ProjectGenerationTemplate.mnf
  • TranFactStdLedgerPattern2008_GenTemplate.mnf
  • TranFactStdLedgerPattern2012_GenTemplate.mnf
  • TransformDimensionPattern2008_GenTemplate.mnf
  • TransformDimensionPattern2012_GenTemplate.mnf
  • SSIS_Config_DB.mnf
  • SSIS_Config_Excel.mnf
  • SSIS_Config_Flat.mnf
  • SSIS_Config_Staging.mnf
  • SSIS_Config_Warehouse.mnf

The following manifests apply to Documentation generation. The Output_Relative_Location should be set to ../Documentation.

  • Data_Dictionary_Dim.mnf
  • Data_Dictionary_Fact.mnf
  • DimodeloCss.mnf

[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]