Customer Dimension¶

Setting up conditional transformations, and updating conditional lookups from multiple fields.

In this module: adding customer tables · field transformations · lookups and conditions

The problem¶

AdventureWorks sells products to stores that resell them — resellers — and also sells online directly to individuals — retail customers. Its customers therefore include both people and stores, and we want to tell them apart to analyse purchasing patterns.

There are many ways to handle this. In this training we denormalize both the person name and the store name into a single field on the Customer dimension, so all the information about each customer sits in one organized table with a clearly defined grain.

Important considerations about the data:

• The Person table contains different types of people, indicated by the PersonType field. The two that matter here are SC (store contacts) and IN (individuals — retail customers).
• The Customer table contains some records with both a PersonID and a StoreID. In those cases the PersonID refers to a store contact, not a retail customer.

Add the customer tables¶

1. Open DSA.
2. Open the Table Selector on the Ingest instance.
3. Search for Customer and drag Sales.Customer into DSA.Tables.
4. Search for Store and drag Sales.Store into DSA.Tables.
5. Search for Person and drag Person.Person into DSA.Tables.
6. Reorder the tables as: Store, Person, Customer.

Look up store names¶

1. Under DSA.Tables.Store, rename 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 onto the Customer table.
4. On the Customer table, rename the StoreName field to CustomerName.

What are field transformations?¶

Transformations change the values within a field — anything from flipping a number's sign to complex calculations using other fields in the table.

Reference: Data Transformation

Create a new PersonName field¶

1. Right-click DSA.Tables.Person → Add Field.
2. Enter the field name PersonName.
3. Set text length to 150.
4. Right-click the PersonName field → Field Transformations.
5. Set Operator to Custom Value and click Add.
6. Concatenate the names by dragging the first and last name fields into the coding box, so the code reads:

[First Name] + ' ' + [Last Name]

7. Click OK.

Reference: Custom Fields · Customized Code and Custom Scripting

Why take the first non-empty value?¶

When building a data warehouse you are usually ingesting from multiple sources, which often produces duplicate records for the same real-world entity — the same customer, for instance.

The Take the first non-empty value option lets you keep one customer record while looking up data from several different source tables. If one source lacks some information, another fills it in, giving a fuller picture of the customer.

When both source tables contain data, you control which value wins by reordering the fields in the Lookup Fields folder.

In the example above, the Customer table's CustomerName field is populated by first trying the Store table's StoreName via CustomerID. If no matching StoreName is found — the value is empty — the lookup falls back to the Person table's PersonName. Each customer record therefore gets the most relevant available name, whether the customer is a store or a person, producing a complete and accurate CustomerName column.

Next in the course¶

Sales Transactions · Finalize the Presentation Layer