Tutorial


Pre-requisites

To follow this tutorial, you will first need a copy of DW Architect Trial or Community edition installed. If you do not already have one of these versions, you can download via the buttons at the right. Once installed, you can continue with the rest of the steps below.

Initial Configuration

A Tutorial starter project exists in the DW Architect distribution directory, under the TUTORIAL directory. The Tutorial involves adding artefacts to this starter project.

To start the tutorial, copy the TUTORIAL project directory to C:\TUTORIAL. This is desirable so that the configuration that exists in the TUTORIAL files match your environment.

DW Architect

Install DW Architect. (View installation instructions here)

DW Management Console

  1. To generate the code you will you will need access to an Instance of SQL Server 2008 (Developer, Standard or Enterprise edition).
  2. Install DW Management Console. You can find the Installation instructions here: DW Management Console Installation Instructions
  3. Create 2 empty databases on the SQL Server. The databases should be named Warehouse_AW and Staging_AW. If you have already created these databases for the Example project and run through the example, then delete these databases and re-create them.
  4. Install the Source Adventure Works 2008 database. A creation script is included with the Example called Adventure Works 2008 Creation script.sql. This only creates the schema, so when you execute the Batch it will still run, but with no data.

    You will probably need to enable FILESTREAM to run this script. Follow the 2 step process described at this link: msdn.microsoft.com/en-us/library/cc645923.aspx
    If you want to run the Batch with data then download the Adventure Works 2008 database (AdventureWorks2008 SR4.exe) from msftdbprodsamples.codeplex.com/releases/view/37109.

Create a Connection

The first step in creating a Data Warehouse is to define connections to your source systems. The connections are used by DW Architect to import Meta Data from your source system schemas. You will learn later about defining environment specific connections for deployment purposes.

Watch the Create Connection video to learn how to create connection. Create the Adventure Works Connection in the tutorial starter project as shown.

Create a Source System

For each Source System it is necessary to define some simple Meta Data. The most important Meta Data is the source system abbreviation which is used in code generation to prefix Staging table etc. Create a Source System document for the Adventure Works Source System as shown in the video.

Create a Staging Table and Extract

The Data Warehouse relies on a Staging database. The Staging database is used to temporarily store Extracted data, prior to the Transformation and Load into the Data Warehouse.

In this exercise you will create a Staging Table and Extract for the Adventure Works Sales Territory table, as shown in the video.

For more information about defining Extracts read the Extract Chapter of the User Guide.

Create a Dimension and Transformation

One of the dimensions of the Sales Order Fact table is Sales Territory. In this exercise you will create a Territory Dimension and its Transformation as shown in the Video.

For more information about defining Dimensions read the Dimension Chapter of the User Guide.

For more information about defining Dimension Transformations read the Transformations Chapter of the User Guide.

Create a Fact Table and Transformation

There is a single Sales Order Fact table in the Data Warehouse. The grain of the Fact is the Sales Order Detail line item. In this exercise you will learn how to create a Fact table, associate it with its Dimensions, and create the Transformation for the Fact Table.

This video demonstrates creating a fact.

This video demonstrates creating a Date Range Transformation for the Fact Table.

**Note: there is an omission in the video. The OrderDate measure on the Transforms tab should have a DIL_Date_Attribute tag added to it, in the tag table of the measure. Click the … button on the OrderDate row to access the tag table. Set the value to DIL_Date_Attribute also.

For more information about creating Fact Tables read the Creating a Fact Table section of the User Guide.

For more information about creating Fact Table transforms read the ‘Defining Extract Transform and Load Processes (ETL)’ section of the User Guide.

For more information about the Fact Table Transaction Date Range Pattern used in this exercise, read the Date Range Transaction Fact Table Pattern section of the User Guide.

For more information about Expressions read the Expression Language section of the User Guide.

Setup Configuration

To Generate the Data Warehouse code, Deploy the code and Execute the Batch, it is necessary to set up Configuration information. Configuration includes things like Connection strings, Database defaults and Output directories.

In this exercise you will set up the Adventure Works Configuration file for your local environment.

Open the DW Architect Management Console under [All Programs] > [Data Info Leaders] from your start menu, and follow the video instructions.

Note:   1. Your connection strings may be different to those shown in the video.

        2. Set the Default Generation output path to C:\TUTORIAL\SRC

        3. There are 2 additional properties not shown in the video:

            a. Set the Batch Schedule Path to C:\TUTORIAL\PRJ\Adventure Works DW

            b. Set the Concurrent Tasks to 1

Generate Code

Follow these instructions to generate the code for your Tutorial project.

        1. Open the DW Architect Management Console.

        2. Open your configuration file. The configuration file was created in the previous exercise and saved as C:\TUTORIAL\PRJ\Adventure Works DW\AW.cuf

        3. Select the Execute>Generate option.

        4. Use the browse button beside the DW Architect Project File Path field to set the path of the DW Architect Project file (.XmlProj) that you are generating code for, in this case C:\TUT\PRJ\Adventure Works DW\Adventure Works DW.XmlProj.

        5. Click the Execute button

The application will generate the code and write it to the ‘Default Generation Output Path’ folder, in this case C:\TUTORIAL\SRC. A list of the generated files and their locations will be displayed in a dialog at the end of generation process.

Deploy Code

Follow these instructions to deploy the code for your Tutorial to the target database.

        1. Open the DW Architect Management Console.

        2. Open your configuration file. The configuration file was created in the previous exercise and saved as C:\TUTORIAL\PRJ\Adventure Works DW\AW.cuf

        3. Select the Execute>Deploy option.

        4. Select the kind of code you wish to Deploy, in this case SQL + DDL Code.

        5. Click the Deploy button

The application will deploy the code to the Data Warehouse and Staging databases of the target Data Warehouse. The code is read and deployed in folder alphabetical order. ie. all the code in folder 010 Tables, then 020 Views etc. Only code that matches the file extension defined in the manifest for the Deployment provider is deployed. eg. the ‘SQL + DDL Code’ deployment provider specifies the .sql file extension, and therefore only attempts to deploy code files with this extension.

A list of the deployed files will be displayed in a dialog during the deployment process. Any errors are displayed in the dialog.

Run a Batch

To run the Batch you need 4 things:

        1. DW Architect Management Console installed with a Full (Server), Trial or Community license. The Trial is time restricted, and the Community Edition is restricted to only 20 tasks per day. Enough to run a daily Batch for allowed small Data Warehouse.

        2. A configuration file (which you created in the previous exercises).

        3. Deployed Code (the code was deployed in the previous exercise).

        4. A Batch Workflow file.

        5. A .bat command file.

There are 2 additional configuration setting that need to be set prior to execution the batch:

        1. Open the DW Architect Management Console.

        2. Open your configuration file. The configuration file was created in the previous exercise and saved as C:\TUTORIAL\PRJ\ Adventure Works DW\AW.cuf.

        3. On the Batch tab set:

            a. Batch Schedule path to C:\TUTORIAL\PRJ\Adventure Works DW

            b. Set the Concurrent Tasks to 1

For more information about configuration settings read the DW Batch Manager Configuration section of the User Guide.

A Batch workflow exists in the C:\TUTORIAL\PRJ\Adventure Works DW\ AW_Workflow.xml file. For more information about Workflow read the DW Batch Manager Workflow section of the User Guide.

To run the Batch:

   Using Windows Vista / Windows 7:

        1. Open a command line window. Run as Administrator.

        2. Change directory to C:\TUTORIAL\PRJ\ Adventure Works DW.

        3. Execute the AW_Batch_Vista command.

        4. View the output in the out.txt file.

   Using previous Windows versions:

        1. Open a command line window. Run as Administrator.

        2. Change directory to C:\TUTORIAL\PRJ\ Adventure Works DW.

        3. Execute the AW_Batch_Vista command.

        4. View the output in the out.txt file.

For more information about running a Batch read the DW Batch Manager – Executing the Batch section of the User Guide.