Training Module:

Customer Dimension

Learn how to set up conditional transformations and update conditional lookups from multiple fields

Table of ContentS

• Table Configuration
  
• Add Customer Tables
  
• Lookup Store Names
• Field Transformations
  
• What are Field Transformations?
  
• Create Person Name
• Add Lookup Field to Customer Name
• Why “Take the First Non-Empty Value“?
  
• Add Customer Type Reseller
  
• What are Conditions?
  
• Add Customer Type Retail Customer
  

Table COnfiguration

go to Table of Contents

Add Customer Tables

1. Open DSA
2. Open the Table Selector for ODX Dev
3. Search for the Customer table and drag and drop Sales.Customer to DSA.Tables
   
4. Search for the Store table and drag and drop Sales.Store to DSA.Tables
5. Search for the Person table and drag and drop table Person.Person to DSA.Tables
6. Reorder the tables in the following order:
   
1. Store
   
2. Person
3. Customer
   

lookup store Names

1. Under DSA.Tables.Store edit the Name field to StoreName
2. Create a relation from Tables.Store.BusinessEntityID to Tables.Customer.StoreID
3. Create a lookup from Tables.Store.StoreName to the Customer table
4. Under the Customer table edit the StoreName field to CustomerName
   

Field Transformations

go to Table of Contents

What are Field Transformations?

TimeXtender allows you to transform the values within a specific field using Transformations. This can be anything from simply changing a number from positive to negative, to complex calculations using other fields in the table.

Here is the list of Transformation types available with their function:

• Custom Value allows for custom SQL code to be executed
  
• Fixed Inserts a fixed value that is specified by the user (Does not require string quotation)
• Upper converts all text values to upper-case
  
• Lower converts all text values to lower-case
  
• First returns the number of beginning characters specified by the user
  
• Last returns the number of ending characters specified by the user
  
• TrimLeft trims padded spaces from the left of the data
  
• TrimRight trims padded spaces from the right of the data
  
• Trim Trims padded spaces from the left and right of the data
  
• ReverseSign reverses the sign for numeric values
  
• TimeOnly returns only the time portion of a datetime field
  
• DateOnly returns only the date portion of a datetime field
  
• Replace one character (or a set of characters) with another, selected by the user
  

Create Person Name

1. Right-click DSA.Tables.Person > Add Field
   
2. Enter Field name: PersonName
   
3. Text length: 150
4. Right-click the PersonName field > Field Transformations
1. Operator: Custom Value
   
2. Click Add
5. Concatenate by dragging and dropping the first and last name fields into the [coding box?].
   
6. Add the following code to read as follows: code code
7. Click OK
   

Add Lookup Field to Customer Name

1. Drag a drop Person.BusinessEntityID to Customer.PersonID to create a Relation
2. Drag and Drop Person.PersonName to Customer.CustomerName Lookup Fields folder to create a Lookup
3. Right-click the field CustomerName > Edit Conditional Lookup
4. Select Multiple Lookup Fields > Take the first non-empty value and click OK
5. Deploy and Execute DSA
1. Select: Only Modified Tables and Views
   
2. Save and Close
   

Why take the First Non-Empty Value?

When building a data warehouse, you are most likely ingesting data from multiple sources. This may often result in duplicate records for the same real-world entity (e.g. customer).

By using the "Take the first non-empty value" option we can ensure there is only one customer record, but lookup data from multiple different source table. This will ensure that if one source happens to lack some info, it will be filled in by our other lookup source, ensuring a more full picture of customer data.

If both source tables contain data, you can select the order (or, prioritization) which value is selected by reordering the fields in the Lookup Fields folder.

Add Customer Type Reseller

1. Add a field to the DSA.Tables.Customer table
1. Name the field Customer Type
   
2. Data type: Text
2. Right-click the field Customer Type > Field Transformations
   
1. Select Operator: Fixed Value
   
2. Enter Value: Reseller and add
   
3. Right-click the [field] Fixed Value "Reseller" > Add Condition
   
1. Select StoreID
   
2. Select Operator: Is not empty and add
   

What are conditions?

Conditions are how you assign an "if" or "case" logic within TimeXtender. (e.g. SET Name = 'John Doe' IF Name IS NULL). Conditions can be applied to other functions in TimeXtender in addition to Transformations.

Here is the list of Condition types available:

• Comparative Conditions - Require user to input a fixed value OR reference another field in the table.
  
• Equal (Does not require string quotation)
  
• Not Equal
  
• Greater Than
  
• Greater or Equal
  
• Less Than
  
• Less or Equal
  
• Max. Length
  
• Min. Length
  
• In List (Comma separated list, No Spaces)
  
• Not in List
  
• Like
  
• Not Like
  

• No Field Selected
  
• Custom Allows for Custom SQL code to set Condition
  
• Field Selected
  
• Non-Comparative Conditions - Do no reference or compare the field value to another value.
  
• Empty Null or Blank String
  
• Not Empty Not Null or Blank String
  
• Is Numeric
  
• Is Not Numeric
  
• Is Date
  
• Is Not Date
  

Add Customer Type Retail Customer

1. Right-click the field Customer Type > Field Transformations
   
1. Select Operator: Fixed Value
   
2. Enter Value: Retail Customer and add
   
2. Right-click the [field] Fixed Value "Retail Customer" > Add Condition
   
1. Select StoreID
   
2. Select Operator: Is empty and add
   
3. Deploy and Execute
   

exercise add phone number & e-mail ​address to customer table

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

1. Open the ODX Table Selector and add Person.PersonPhone and Person.EmailAddress to the DSA.
2. Using the below table relationships, add PhoneNumber and EmailAddress fields to the Customer Table.
1. PersonPhone.BusinessEntityID = Customer.PersonID
   
2. EmailAddress.BusinessEntityID = Customer.PersonID
   
3. Deploy, Execute, & Preview to ensure fields are populated.
   

Section Quiz...

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

Is it possible to do ​multiple lookups on one ​Conditional Lookup ​Field?

How can you create calculated fields in TimeXtender?

How can you apply "IF" logic in TimeXtender?

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.

Is it possible to do ​multiple lookups on one ​Conditional Lookup ​Field?

Yes, by dragging additional ​fields onto the Lookup Fields ​node of the Conditional ​Lookup Field.

How can you create calculated fields in TimeXtender?

1. Add a Field
   
2. Right-click > Add Transformation
   

How can you apply "IF" logic in Timextender

Right-click a transformation > Add condition

want to Learn even more?

Learn how to quickly reuse custom transformations with SQL Snippets on TimeXtender Tuesdays

Congratulations! You've completed the training module

Customer Dimension