Training Module:

Product Dimension

Learn about Deployment & Execution and Conditional Lookups while creating a Product Dimension.

Geometrical Illustration

Table of ContentS

  • Data Area
    • Add Data Area
    • Select Tables
    • Reorder Tables
    • Deploy & Execute
  • Relate with Lookups
    • What Are Conditional Lookups?
    • Rename Product Fields
    • Lookup Product Model Name
  • MDW Incremental Load
    • What is Automatic Incremental Loading?
    • Keep Field Values up to Date
  • Denormalize with Lookups
    • Validate Product Model Name
    • Rename Product Category Fields
    • Lookup Product Category
    • Lookup Product Subcategory

Hexagons Background

Data Area

Add Data Area

Data areas allow users to create separate data warehouse areas in the same database, separated by different schemas. Depending on the business needs you can create one or multiple data areas.

  1. Open the MDW Instance
  2. Right-click on Data Area > Add Data Area
  3. Enter name: DSA (Data Staging Area)
  4. Click OK

Select Product Tables

  1. Right-click on the ODX > Open Table Selector
  2. Enter table name: Product
  3. Select the desired database
  4. Click Search
  5. Open DSA
  6. Drag and Drop the following tables into DSA:
    • Production.Product
    • Production.ProductCategory
    • Production.ProductModel
    • Production.ProductSubcategory

Reorder Tables

  1. In DSA reorder the product tables in the following order:
    • Production.ProductModel
    • Production.ProductCategory
    • Production.ProductSubcategory
    • Production.Product

Deploy & Execute

  1. Right-click DSA > Deploy and Execute
  2. Click Start
  3. Add a version note
  4. Click Save
  5. Click Close
Hexagons Background

Relate with Lookups

What are conditional lookups?


In TimeXtender, Relations are used to specify how two tables are related, most commonly to subsequently add a lookup. Relations are created by dragging a primary key field from the source table (One side) onto the related foreign key field on the destination table (Many side).

Conditional Lookup

Conditional Lookups are how you join tables together in TimeXtender. Once the relation is in place, you can drag and drop any field from the One side table, onto the Many side table. This creates the conditional lookup field using the related fields as the join. Once the table is executed, the field will be populated using the desired logic.

A traditional SQL JOIN might result in more or less rows in a table, which can lead to undesired results. However, a TimeXtender Conditional Lookup will never affect the rows in a table and will only be populated if a resultant value is found in the source table. However, be sure your join criteria is unique. If not, the lookup will pull in any one of the possible values.

Rename Product Fields

  1. Expand DSA > Tables > Product Model
  2. Select the Name field and press F2 to rename: ProductModelName
  3. Expand the Product Table
  4. Select the Name field and press F2 to rename: ProductName

Lookup Product Model Name

Add Relation

  1. Right-click Product Model
  2. Click Open New Window
  3. Expand the ProductModel table
  4. Drag and drop ProductModelID to ProductModelID in the Product table
  5. Click Yes to create the product relation

Add Conditional Lookup

  1. Drag and drop ProductModelName to the Product table
  2. Use default relation (created above) and click OK
  3. Notice the Lookup and Relation created at the bottom of the field list

Hexagons Background

MDW Incremental Load

What is Automatic Incremental Load?

When Incremental Load is enabled in an ODX data source, Data Warehouse tables mapped from this source will inherit these settings automatically.

Identify if a table has Automatic incremental load by the little I on the icon of the table mapping.

OR Right-click on the table > Table Settings > Data Extraction, and see if Automatic is selected.

Why are my new fields null?

When Incremental Load is enabled, a newly created table will load all available rows. However, each subsequent execution will only load new or modified data. This means that newly added fields, conditional lookups or transformations will not show any new data until the table is executed using full load.

To execute a table using Full Load you can use one of the options below:

Just Once

  1. Deploy & Execute the Table
  2. Click Review Tasks
  3. Under Deployment steps, select
    1. Full Load valid table
    2. Table Structure

From now on

  1. Right-click the table > Table Settings
  2. Click the Data Extraction tab
  3. Select Full Load

Keep Field Values up to Date

On tables that you wish to incrementally load, but have lookup values that may update on older records, you can enable Keep field values up-to-date to ensure those values are updated even when the table is incrementally loaded.

  1. Right-click the Product table > Table Settings
  2. Click the Data Extraction tab
  3. Select Keep field values up-to-date
Hexagons Background

Denormalize Using Lookups

Validate Product Model Name

  1. In the Product table, reorder the field ProductModelName to come right after ProductModelID
  2. Deploy & Execute the DSA
    1. Select the option Only Modified tables and views
  3. Right-click the Product table > Preview Table
    1. Note the ProductModelName field is Null (this is because in these rows the relation ProductModelID is also Null)
  4. Open the Query Tool
  5. Add the following WHERE clause to the query:

  6. Click execute to preview the results and validate the Lookup is performing correctly

WHERE ProductModelName IS NOT Null

Rename Product Category Fields

  1. In the ProductCategory table rename the Name field to ProductCategoryName
  2. In the ProductSubcategory table rename the Name field to ProductSubcategoryName

Lookup Product Category

  1. Create a relation from ProductCategory.ProductCategoryID to ProductSubcategory.ProductCategoryID
  2. Create a Lookup by dragging ProductCategory.ProductCategoryName to ProductSubcategory table

Lookup Product subCategory

  1. Open the ProductSubcategory table in a new window
  2. Create a relation from ProductSubcategory.ProductSubcategoryID to Product.ProductSubcategoryID
  3. Create a Lookup from ProductSubcategory.ProductCategoryName to Product table
  4. Create a Lookup from ProductSubcategory.ProductSubcategoryName to Product table
  5. Deploy & Execute the DSA
    1. Select the option Only Modified tables and views

exercise Expand the product table

Use what you've learned so far to complete the following exercise.

  1. Add the following fields to the Product Table.
    1. ProductModel.CatalogDescription
    2. ProductModel.Instructions

Section Quiz...

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

What is the difference ​between Deployment ​and Execution

How do you create a Lookup?

Why might my recently added fields be null?

Brushstroke Arrow Smooth Curve Down Small

When you're ready, see Answers Below

Section Quiz Answers

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

What is the difference between Deployment and Execution

Deployment creates the tables, views, and Stored Procedures and Execution loads the tables with data.

How do you create a Lookup?

  1. Relate two tables by dragging the field from one table, to the related field on another table.
  2. Add the Lookup by dragging the source field onto the destination table name.

Why might my recently added fields be null?

  1. The table has not yet been deployed & executed
  2. The table has incremental load enabled & needs to be fully loaded

Congratulations! You've completed the training module

Product Dimension


Thumbs Up Illustration
Thumbs Up Illustration