COURSE OVERVIEW

Training Module:

Customer Dimension

Learn how to setup conditional transformations and update conditional lookups from multiple fields

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
   

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 Replaces one set of characters with another
  

Create Person Name

[First Name]+ ' ' +[Last 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 often are pulling in 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 reprioritize 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 relation ships, 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 Bellow

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

← Course Overview

Next Module →

give FEEDBACK