# User Behavior Analysis in Practice 6: Numberizing the Dimension Table

We have a user events table T. Below is its structure and part of its data:

Fields in table T:

Dimension table EventType:

Dimension table Product:

Fields in dimension table Product:

Dimension table ProductType:

Relationship between tables:

Calculate the total sales amount, number of orders, search frequency and the number of distinct users performing search and ordering under each type of product within a specified time period.

## Techniques involved:

1. Associate with dimension table using ordinal-number-based location.

In both EventType and ProductType tables, primary keys are ordinal natural numbers. They can be directly used to locate records and achieve associations without creating index and computing and comparing HASH values. This can boost performance.

2. Convert a dimension table’s primary key values that are non-ordinal-numbers into ordinal natural numbers so that ordinal-number-based location can be used to speed up association.

Product table’s primary key values are not ordinal numbers represented by natural numbers, but we can first transform them into ordinal numbers and, in the meantime, convert ProductID field values in user events table T into corresponding ordinal number. Now ordinal-number-based location can be used.

## Sample code

The code has five parts:

1. According to practices in previous essays, we dump data in those dimension tables as bin files EventType.btx, Product.btx and ProductType.btx that are respectively ordered by EventTypeID, ProductType and ProductTypeID.

2. Join user events table T and EventType table, Product table and ProductType table through ordinal numbers.

3. Add an ordinal number field to Product table.

4. Dump data from user events table T to store it in composite table T.ctx ordered by Time field, and transform ProductID values into ordinal numbers of corresponding records in Product table.

5. Import each dimension table into memory, open the composite table cursor, establish associations with dimension tables during which the association with Product table is through ordinal numbers. It is not necessary to set primary key and create index for dimension table Product.

1. Dump dimension table data

A4 Sort by EventTypID.

A6 Sort by ProductTypID.

2. Change types of association between EventType table and table T, ProductType table and table T into ordinal number-based association.

A3-A4 Do not set create indexes on primary keys for EventType and ProductType.

A6 Change type of association between ProductType table and table T into ordinal number-based association.

A7 Change type of association between EventType table and table T into ordinal number-based association.

3. Add an ordinal number field in Product table.

Original data: add an ordinal number field to it directly.

Updated data: Whenever a dimension table is updated, it is wholly retrieved and compared with the dumped btx file. The comparison is based on ordinal numbers of the dumped records, otherwise historical data will be mismatched. The newly-increased data is placed at the end. Usually there are no deletion actions on dimension tables. Any deletion of dimension data will cause errors when historical records of the fact table try to reference records of the dimension table.

A1 Load the updated dimension table, add ProductNum field and set primary key and index.

A2 Load the original dimension table Product from the corresponding bin file and set primary key.

A3 Find the newly-increased records in the updated dimension table.

A4 If a record in the original dimension table exist in the updated one, use the new record; if it does not exist, use the original record.

A5 Union A4 and A3 and set ordinal numbers. As A4 keeps the order of the original dimension table, ordinal numbers in the original dimension table are retained.

A6 Write A5’s result to a bin file.

4. The code of preparing file T.ctx, during which ProductID field values are changed into ordinal numbers.

Take stocked data as an example:

A1 Load dimension table Product into the memory and create index on primary key.

A2 Sort table T by time while retrieving data from it.

A3 Replace ProductID field values in table T with ordinal numbers of corresponding records in the dimension table.

A4 Create a composite table.

A5 Append data of table T to A4’s composite table.

Similar code for the newly-increased data.

5. Perform the whole analysis on the converted data, all through ordinal numbers.

Suppose we need to summarize data that falls in between 2022-03-15 and 2022-06-16:

A1-A3 Do not set primary keys.

A6 A join via ordinal numbers.

Execution result: