COURSE OVERVIEW

Training Module:

Product Dimension

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

Add Data Area

Data areas allows users to create seperate data warehouse areas in the same database seperated with different schema. 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

what is deployment & Execution?

deployment

TimeXtender creates or modifies existing tables, views, and stored procedures in the target storage database. Deployment is required when tables, views, fields, or lookups are added or changed.

execution

TimeXtender transfers and cleans data, resulting in a populated table. After the execution is complete the table can be previewed or queried. Execution can be initiated manually or run on a schedule

Deploy & Execute

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

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

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 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.

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


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, 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 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

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:

  7. 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 Bellow

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

give FEEDBACK

Thumbs Up Illustration
Thumbs Up Illustration