Product Dimension¶

Deployment, execution and conditional lookups, learned while building a Product dimension.

In this module: adding data areas · denormalizing product tables · conditional lookups · finalizing the Product dimension

Data areas¶

Data areas let you create separate data warehouse areas in the same database, separated by schemas. Depending on business needs you can create one or several.

Add a data area

1. Open the Prepare instance.
2. Right-click Data Area → Add Data Area.
3. Enter the name DSA (Data Staging Area).
4. Click OK.

Reference: Reverse Data Area · Data Area Security

Select the product tables¶

1. Right-click the Ingest instance and choose Open Table Selector.
2. Enter the table name Product.
3. Select the database and click Search.
4. Open DSA.
5. Drag and drop these tables into Tables under DSA:
   • Production.Product
   • Production.ProductCategory
   • Production.ProductModel
   • Production.ProductSubcategory

Reorder the tables¶

When modelling in Timextender it helps to organize tables so that the lowest granularity is at the top and the final denormalized tables with the highest grain are at the bottom. That way, when creating relations and lookups, you are always dragging downwards.

Reorder the product tables as:

1. Production.ProductModel
2. Production.ProductCategory
3. Production.ProductSubcategory
4. Production.Product

Deploy and execute¶

1. Right-click DSA → Deploy and Execute.
2. Click Start.
3. Add a version note.
4. Click Save, then Close.

Reference: Deploy tables · Execute tables

What are conditional lookups?¶

Relations specify how two tables are related, most often so you can then add a lookup. Create one by dragging a primary key field from the source table (the one side) onto the related foreign key field on the destination table (the many side).

Conditional lookups are fields in a table whose values are populated from another table based on that relation. This is how a traditional SQL LEFT JOIN is expressed in Timextender. With the relation in place, drag any field from the one side table onto the many side table — that creates the conditional lookup field, using the related fields as the join. After execution the field is populated.

Reference: Conditional Lookup Fields · Table relations · Related Records

Rename product fields¶

1. Expand DSA → Tables → ProductModel.
2. Select the Name field and press F2 to rename it ProductModelName.
3. Expand the Product table.
4. Select the Name field and press F2 to rename it ProductName.

Next in the course¶

Customer Dimension · Sales Transactions