User Behavior Analysis in Practice 5: Using Dimension Table
Target task:
We have a user events table T. Below is its structure and part of its data:
| Time | UserID | EventTypeID | ProductID | Quantity |
| 2022/6/1 10:20 | 1072755 | 3 | 100001 | |
| 2022/6/1 12:12 | 1078030 | 2 | 100002 | |
| 2022/6/1 12:36 | 1005093 | 5 | 100003 | 3 |
| 2022/6/1 13:21 | 1048655 | 1 | ||
| 2022/6/1 14:46 | 1037824 | 6 | ||
| 2022/6/1 15:19 | 1049626 | 4 | 100004 | 4 |
| 2022/6/1 16:00 | 1009296 | 5 | 100005 | 6 |
| 2022/6/1 16:39 | 1070713 | 2 | 100006 | |
| 2022/6/1 17:40 | 1090884 | 3 | 100007 |
Fields in table T:
| Field name | Data type | Description |
| Time | Datetime | Time stamp of an event, accurate to milliseconds |
| UserID | String | User ID |
| EventTypeID | Integer | Event type ID |
| ProductID | String | Product ID |
| Quantity | Numeric | Quantity |
Dimension table EventType:
| EventTypeID | EventType |
| 1 | Login |
| 2 | Browse |
| 3 | Search |
| 4 | AddtoCart |
| 5 | Submit |
| 6 | Logout |
Dimension table Product:
| ProductID | ProductName | Unit | Price | ProductTypeID |
| 100001 | Apple | Pound | 5.5 | 1 |
| 100002 | Tissue | Packs | 16 | 2 |
| 100003 | Beef | Pound | 35 | 3 |
| 100004 | Wine | Bottles | 120 | 4 |
| 100005 | Pork | Pound | 25 | 3 |
| 100006 | Bread | Packs | 10 | 5 |
| 100007 | Juice | Bottles | 6 | 4 |
| … | … | … | … | … |
Fields in dimension table Product:
| Field name | Data type | Description |
| ProductID | String | Product ID |
| ProductName | String | Product name |
| Unit | String | Sales unit |
| Price | Numeric | Unit price |
| ProductTypeID | Integer | Product type ID |
Dimension table ProductType:
| ProductTypeID | ProductType |
| 1 | Fruits |
| 2 | Home&Personalcare |
| 3 | Meat |
| 4 | Beverage |
| 5 | Bakery |
| … | … |
Relationship between tables:
Computing task:
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 Join tables through dimension table rather than generating a wide table. This can reduce the volume of data to be stored and increase retrieval speed.
2 Use a global variable to pre-load dimension tables and establish associations for later reuse.
Sample code
1. According to our previous practices, we dump data from user events table T and store it in composite table T.ctx according to the order of Time field; and then dump data in those dimension tables as bin files EventType.btx, Product.btx and ProductType.btx.
2. Import each dimension table into memory, set primary key for them, open the composite table cursor, establish associations with dimension tables, and perform grouping &aggregation.
Suppose we need to summarize data that falls in between 2022-03-15 and 2022-06-16:
| A | |
| 1 | >start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
| 2 | =file("T.ctx").open().cursor(UserID,EventTypeID,ProductID,Quantity;Time>=start && Time<=end && (EventTypeID==5 || EventTypeID==3)) |
| 3 | >EventType=file("EventType.btx").import@b().keys@i(EventTypeID) |
| 4 | >ProductType=file("ProductType.btx").import@b().keys@i(ProductTypeID) |
| 5 | >Product=file("Product.btx").import@b().keys@i(ProductID) |
| 6 | >Product=Product.switch(ProductTypeID, ProductType:ProductTypeID) |
| 7 | =A2.switch(ProductID,Product:ProductID;EventTypeID,EventType:EventTypeID) |
| 8 | =A7.groups(EventTypeID,ProductID.ProductTypeID;EventTypeID.EventType,ProductID.ProductTypeID.ProductType,sum(Quantity):Quantity,count(1):Num, icount(UserID):iNum) |
A2 Retrieve records within the specified time range and where the event type is “submit order” and “search” from the composite table file and create a cursor based on them.
A3 Load dimension data from bin file EventType.btx, and set primary key and create index on it.
A4 Load dimension data from bin file ProdcutType.btx and set ProductTypeID as its primary key.
A5 Load dimension data from bin file Prodcut.btx and set ProductID as the primary key.
A6 Establish association between Product and ProductType.
A7 Associate A2’s cursor with in-memory dimension tables Product and EventType respectively.
A8 Perform grouping calculation to generate small result sets from A7’s cursor, the joining result.
When using switch() function to join with a dimension table, you need to set primary key for the dimension table in advance and perform join operation through the key. The join amounts to adding references of dimension table records in the associated field of the original table. Then you can reference any field of the dimension table using the syntax "field of the original table.field of the dimension table".
With hierarchical dimension tables, like table T – Product table – ProductType table in this instance, you can use the dot operator (.) to reference levels of dimension tables one by one, such as "ProductID.ProductTypeID.ProductType". It means that table T’s ProductID field references ProductTypeID field in its dimension table and then ProductType field in the dimension table’s dimension table.
3. Dimension tables are often used repeatedly. As they are generally not large, we can load them into the memory, establish associations and store each of them as a global variable. Then there is no need to load dimension tables and establish associations again for summarizations and just use the global variable directly. This way the above code can be regarded as including two parts. The first part is to load dimension tables as global variables at the startup of the server. The second part is the code for performing summarization.
Part one (execute once at the startup of the server):
| A | |
| 1 | =file("EventType.btx").import@b().keys@i(EventTypeID) |
| 2 | =file("ProductType.btx").import@b().keys@i(ProductTypeID) |
| 3 | =file("Product.btx").import@b().keys@i(ProductID) |
| 4 | >env(EventType,A1),env(ProductType,A2),env(Product,A3) |
| 5 | >Product.switch(ProductTypeID,ProductType:ProductTypeID) |
A4 Store each of the in-memory dimension tables as global variables for reference by the code for performing summarization.
Part two (summarization):
| A | |
| 1 | >start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
| 2 | =file("T.ctx").open().cursor(UserID,EventTypeID,ProductID,Quantity;Time>=start && Time<=end && (EventTypeID==5 || EventTypeID==3)) |
| 3 | =A2.switch(ProductID,Product:ProductID;EventTypeID,EventType:EventTypeID) |
| 4 | =A3.groups(EventTypeID,ProductID.ProductTypeID;EventTypeID.EventType,ProductID.ProductTypeID.ProductType,sum(Quantity):Quantity,count(1):Num, icount(UserID):iNum) |
Execution result:
| EventTypeID | ProductTypeID | EventType | ProductType | Quantity | Num | iNum |
| 3 | 1 | Search | Fruits | 0 | 499586 | 48735 |
| 3 | 2 | Search | Home&Personalcare | 0 | 508897 | 49872 |
| 3 | 3 | Search | Meat | 0 | 403213 | 39923 |
| 3 | 4 | Search | Beverage | 0 | 324567 | 29045 |
| 3 | 5 | Search | Bakery | 0 | 335498 | 30234 |
| … | … | … | … | … | … | … |
| 5 | 1 | Submit | Fruits | 206938 | 103469 | 13523 |
| 5 | 2 | Submit | Home&Personalcare | 463188 | 154396 | 14656 |
| 5 | 3 | Submit | Meat | 94378 | 93366 | 8754 |
| 5 | 4 | Submit | Beverage | 217504 | 54376 | 5233 |
| 5 | 5 | Submit | Bakery | 339480 | 67896 | 5844 |
| … | … | … | … | … | … | … |
