Ph US: 323 521 4640 Asia Pacifc: +61 407 698 591 contact@dimodelo.com

Overview

In this tutorial you will learn how to use Dimodelo Architect to:

  • Create a new Data Warehouse project.
  • Create a connection to a Source System.
  • Use the Wizard to generate the Meta Data for a Data Warehouse and its Extract, Transform and Load processes (ETL).
  • Generate the code to create the Data Warehouse and Staging databases, and the ETL to move data from the source through to the Data Warehouse.
  • Deploy the code to the Server.
  • Execute the ETL batch.

Prerequisites

To complete this tutorial, you need to have Microsoft SQL Server Integration Services (SSIS) 2008 or higher installed on your local PC (where Dimodelo Architect is installed) and have SQL Server Database Engine 2008 installed either locally or on a server. If you don't meet these requirements, you can still complete the design part of the tutorial, but you may not be able to generate the code, and won't be able to deploy it to a server. Microsoft SQL Server Integration Services is required for SSIS package generation, and you need SQL Server Database engine as a target for the deployment of the Data Warehouse. SSIS can be installed choosing to only installing the Client Management tools and SSIS as part of an SQL Server installation.

Install the Tutorial databases

Open Dimodelo_Tutorial_Script using Microsoft SQL Server Management Studio. Connect to your SQL Server instance and execute the script. This will create the Dimodelo_Tutorial_Source database. The source database for the Tutorial. This database is a small Services database we use for demonstration purposes.

Getting Started

  1. Open Dimodelo Architect by double clicking the Dimodelo icon on the desktop.

Create a New Project

 

Create a new project.

  1. Click File>New>Project.

 

New Project Dialog Box

  1. Enter 'Tutorial 1' in the Name field.
  2. Enter where you want to save the project in Location.
  3. Enter 'Tutorial1' in the Solution name field.
  4. Un-tick the Create directory for solution checkbox.
  5. Click OK to continue.

Configure your project to generate for a different version of SQL Server

Projects are configured to generate for SQL Server 2012 by default. If you want to generate for a different version of SQL Server, then you need to change the project configuration. 

Note: SQL Server 2008/2012/2014 Integration services needs to installed on the local machine to successfully generate SSIS packages for that version of SQL Server.

You can ignore this section if you are generating for SQL Server 2012. 

projectConfigurationSQLServer2014

Dimodelo Architect Project Configuration

  1. Right click the Tutorial 1 project node in the Solution Explorer, and click properties to open the project configuration dialog (see image above). 
  2. Navigate to the Configuration Properties>Dimodelo pane.
  3. Change "Target SQL Server Version" to SQL2014 or SQL2008 as required.
  4. Modify the Workflow file name to correspond to the target SQL Server version (as shown above), either BatchDefault_Workflow_2014.xml or BatchDefault_Workflow_2008.xml.

Note: More information about these configuration settings can be found in the user guide on this website.

Create a Connection to your Source

Create New Connection Manager

  1. Expand the Connection Managers Node in the Solution Explorer tool window.
  2. Right click the DB folder
  3. Click 'Create New'.

Configure new connection manager

  1. Enter 'Tutorial Source' into the Connection Manager Name field.
  2. Click the Configure button.
  3. Select 'Microsoft SQL Server' in the Data Source list.
  4. Select '.NET Framework Data Provider for OLE DB' in the Data Provider dropdown.
  5. Click OK.

Test new connection manager

  1. In the Server Name field enter the instance name of the SQL Server that the Dimodelo_Tutorial_Source database was created on.
  2. Choose the appropriate Log on option.
  3. In the Select or enter a database name field enter 'Dimodelo_Tutorial_Source'.
  4. Click the Test Connection button to test the connection. If the connection fails check the name of the Server, a database name. Make sure the database exists on that server. The database will only exist if you have done the
    'prerequisites' step as described above.
  5. Click Ok on the Test results dialog.
  6. Click OK on the Connection Properties dialog.

Save connection manager

  1. Click the Save icon in the Dimodelo Menu.
  2. There should now be a 'Tutorial Source.db' node under Connection Managers>DB in the solution explorer.

Create a Source System Node

Add source system

  1. Right click the Source Systems folder in the Solution Explorer tool window, and click Create New, a new Source System editor panel appears.
  2. Enter 'Tutorial Source System' into the Source System Name field.
  3. Enter 'Tut' into the Source System Abbreviation field.
  4. Select 'Tutorial Source' in the Related Connection dropdown.
  5. Click the Save icon in the Dimodelo Menu.
  6. There should now be a Tutorial Source System.ss node under Source Systems in the solution explorer.
  7. Close the Source System1.ss editor by clicking the x in the editor tab.

The Source System Abbreviation is used as a prefix to the name of the staging tables for this Source System in the Staging database, to distinguish the tables from tables with the same name from another Source System. Note that the Source System appears in the Source tool window.

Next Step: Use the Wizard to Design a Data Warehouse 

Share This

Share this post with your colleagues!