User Behavior Analysis in Practice 7: Dimension Table Filtering
Target task
We have a user events table T. Below is its structure and part of its data:
| Time | UserID | ProductID | Quantity | … |
| 2022/6/1 10:20 | 1072755 | 1 | 7 | … |
| 2022/6/1 12:12 | 1078030 | 2 | 8 | … |
| 2022/6/1 12:36 | 1005093 | 3 | 3 | … |
| 2022/6/1 13:21 | 1048655 | 4 | 9 | … |
| 2022/6/1 14:46 | 1037824 | 5 | 5 | … |
| 2022/6/1 15:19 | 1049626 | 6 | 4 | … |
| 2022/6/1 16:00 | 1009296 | 7 | 6 | … |
| 2022/6/1 16:39 | 1070713 | 8 | 7 | … |
| 2022/6/1 17:40 | 1090884 | 9 | 4 | … |
Fields in table T:
| Field name | Data type | Description |
| Time | Datetime | Time stamp of an event, accurate to milliseconds |
| UserID | String | User ID |
| ProductID | Integer | Product ID |
| Quantity | Numeric | Quantity |
Dimension table Product:
| ProductID | ProductName | Unit | Price | ProductTypeID |
| 1 | Apple | Pound | 5.5 | 1 |
| 2 | Tissue | Packs | 16 | 2 |
| 3 | Beef | Pound | 35 | 3 |
| 4 | Wine | Bottles | 120 | 4 |
| 5 | Pork | Pound | 25 | 3 |
| 6 | Bread | Packs | 10 | 5 |
| 7 | Juice | Bottles | 6 | 4 |
| … | … | … | … | … |
Fields of 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 |
Computing task:
Find the number of sold pieces for each product under type IDs 1, 2 and 3 within a specified time period.
Techniques involved:
1. Filtering dimension table before join
We first filter the dimension table, and then join it with the fact table. Thereisnoneedtojudgethespecifiedconditionondimensiontableforfacttablerecordsthatdonotmatchthefiltereddimensiontable. In this way the number of comparisons will decrease considerably.
2. Performing join in cursor
Join the fact table’s cursor with the filtered dimension table, during which fact table records that cannot associate with the dimension table will not be generated. This further reduces time spent in generating records in the joining result set.
3. Index reuse
Use the existing index on the filtered dimension table. When the filtering result set is still large, which means only a small number of records are filtered away, the technique saves your time for re-creating the index.
4. Alignment sequence
We can use an efficient alignment sequence to filter a numberized dimension table by generating a same-length table sequence, whose member values are whether the corresponding dimension records meet the filtering condition. When joining the dimension table with the fact table, we can directly find whether a fact table record matches or not according to its ordinal number. This saves time for performing the join and comparisons.
Sample code
Suppose T.ctx and Product.btx are already created as above introduces. T.ctx is ordered by Time and Product.btx is ordered by ProductID.
1. Filter dimension table Product and join it with T; delete records from table T that do not have matching records in the dimension table during joining.
| A | |
| 1 | >Product=file("Product.btx").import@b().select([1,2,3].pos(ProductTypeID)!=null).keys@i(ProductID) |
| 2 | >start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
| 3 | =file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end) |
| 4 | =A3.switch@i(ProductID,Product:ProductID) |
| 5 | =A4.groups(ProductID; ProductID.ProductName, sum(Quantity):Quantity) |
A1 Import the dimension table and filter it, and set index on the primary key.
A4 Join the fact table and the dimension table; @i option enables deleting records from the fact table that do not match the dimension table.
2. Move the association action in step 1 to the cursor generation statement, during which fact table records that cannot match won’t be generated in the cursor.
| A | |
| 1 | >Product=file("Product.btx").import@b().select([1,2,3].pos(ProductTypeID)!=null).keys@i(ProductID) |
| 2 | >start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
| 3 | =file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end,ProductID:Product) |
| 4 | =A3.groups(ProductID; ProductID.ProductName, sum(Quantity):Quantity) |
A3 Move the action of association between the fact table and the dimension table to the cursor generation statement; fact table records that are not matched won’t be generated in the result set.
3. Use the existing index on filtered dimension table Product
| A | |
| 1 | >Product=file("Product.btx").import@b().keys@i(ProductID) |
| 2 | =Product.select@i([1,2,3].pos(ProductTypeID)!=null) |
| 3 | >start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
| 4 | =file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end,ProductID:A2) |
| 5 | =A4.groups(ProductID; ProductID.ProductName, sum(Quantity):Quantity) |
A1 Import dimension table Product and set index on primary key.
A2 @i option enables reusing the existing index on the filtered dimension table.
Index reuse does not always make the computation faster. As records that are filtered away need to be deleted from the index table, it takes some time to perform the delete action when there are a lot of records are discarded (the number of desired records is small). In this case re-creating the index may be faster. It is important that we choose an appropriate method according to the actual situation.
4. Use alignment sequence as primary key values of dimension table Product are ordinal numbers
| A | |
| 1 | >Product=file("Product.btx").import@b() |
| 2 | Product.([1,2,3].pos(ProductTypeID))= |
| 3 | >start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
| 4 | =file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end) |
| 5 | =A4.select(A2(ProductID)) |
| 6 | =A5.groups(ProductID; Product(ProductID).ProductName, sum(Quantity):Quantity) |
A1 Import dimension table Product without setting index on the primary key.
A2 Generate an alignment sequence according to Product table. Each of its values is whether the current record meets the filtering condition or not.
A5 Directly locate the target member in A2 according to table T’s ProductID to find out whether the current record satisfies the filtering condition.
5. Move the alignment-sequence-based filtering action to the cursor generation statement
| A | |
| 1 | >Product=file("Product.btx").import@b() |
| 2 | Product.([1,2,3].pos(ProductTypeID))= |
| 3 | >start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-06-16","yyyy-MM-dd") |
| 4 | =file("T.ctx").open().cursor(ProductID,Quantity;Time>=start && Time<=end && A2(ProductID)) |
| 5 | =A4.groups(ProductID; Product(ProductID).ProductName, sum(Quantity):Quantity) |
A4 Move the alignment-sequence-based filtering action to the cursor generation statement. Records that do not meet the filtering condition won’t be generated.
Execution result:
| ProductID | ProductName | Quantity |
| 1 | Apple | 206938 |
| 2 | Tissue | 463188 |
| 3 | Beef | 94378 |
| 5 | Pork | 217504 |
| … | … | … |
