Training Module:

Product Dimension

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

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
  

Data Area

go to Table of Contents

Add Data Area

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
   

Relate with Lookups

go to Table of Contents

What are conditional lookups?

Relations

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

MDW Incremental Load

go to Table of Contents

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
   

Denormalize Using Lookups

go to Table of Contents

Validate Product Model Name

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?

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