User Behavior Analysis in Practice 8: The Changing Dimension Table
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 | Origin | ProductType |
| 1 | Apple | Pound | Shandong | Fruits |
| 2 | Tissue | Packs | Guangdong | Home&Personalcare |
| 3 | Beef | Pound | Qingdao | Meat |
| 4 | Wine | Bottles | Shanxi | Beverage |
| 5 | Pork | Pound | Xizang | Meat |
| 6 | Bread | Packs | Beijing | Bakery |
| 7 | Juice | Bottles | Xinjiang | Beverage |
| … | … | … | … | … |
Fields of dimension table Product:
| Field name | Data type | Description |
| ProductID | String | Product ID |
| ProductName | String | Product name |
| Unit | String | Unit |
| ProductTypeID | String | Product type |
| Origin | Numeric | Product origin |
Computing task:
Find the sales quantity of each type of product of each place of origin within the specified time period.
One thing we should take into consideration is that the place of origin is not fixed, and get sales quantity of a type of product according to the place of origin in the current transaction.
Techniques involved:
Employing time key on the dimension table. You can find more about SPL time key mechanism in Why Are There So Many Snapshot Tables in BI Systems?.
1. Generate a dimension table with time key according to the dimension table in the production system at regular time interval.
Add a time key field to Product.btx. Here’s the dimension table’s new structure:
| Field name | Data type | Description |
| eTime | Datetime | Effective time for the current record |
| ProductID | String | Product ID |
| ProductName | String | Product name |
| Unit | String | Sales unit |
| ProductTypeID | String | Product type |
| Origin | Numeric | Place of origin |
Perform daily data dump on Product.btx with a time key according to the updated information in the production system’s Product table every day.
2. Summarize data using the dimension table with a time key
Define a new composite primary key using eTime and ProductID for the dimension table while specifying eTime as the time key. Table T is associated with Product through Time and ProductID fields. Programmers do not need to take care of how to achieve the time key because SPL is designed to automatically handle the related computing logic. They just use the dimension table as a regular one.
Sample code
1. Dump dimension table Product.btx.
For original data: Directly add the time key field.
| A | |
| 1 | =connect("demo").query@x("select * from Product").derive(now():eTime) |
| 2 | = file("Product.btx").export@b(A1) |
When data is updated: Import the new dimension table, get the record with the latest time under each type of product from the dumped btx file with the time key, compare them with the new table, and append a new record to the btx file for the updated data and record a new effective time.
| A | |
| 1 | =connect("demo").cursor@x("select * from Product") |
| 2 | = T("Product.btx").keys@it(ProductID,eTime) |
| 3 | =A1.select(cmp(~.array(),A2.find(A1.ProductID).array().to(2,))!=0) |
| 4 | = A3.fetch().derive(now():eTime) |
| 5 | =file("Product.btx").export@ab(A4) |
A1 Import the new dimension table and set index on the primary key.
A2 Import the old dimension table Product from the btx file, get record having the latest time for each product, and set index on the primary key.
@t option means the last key field is the time key.
A3 Get records under each primary key value from the new dimension table where the latest (largest) eTime is different from that in the records under same key value in the old dimension table. The find function will automatically select the record having the latest time corresponding to same key value.
A4 Add effective time field to A3’s records.
A5 Append and export A4’s records to the bin file.
2. Join the fact table and the dimension table and perform aggregation
Suppose T.ctx is already generated as the above explains and sorted by Time:
| A | |
| 1 | >Product=T("Product.btx").keys@ti(ProductID,eTime) |
| 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;Time>=start && Time<=end) |
| 4 | =A3.switch(ProductID:Time,Product) |
| 5 | =A4.groups(ProductID.ProductType,ProductID.Origin; sum(Quantity):Quantity) |
A1 Import dimension table Product, and set index on the primary key while specifying the time key.
A4 Join the fact table and the dimension table using the familiar SPL join syntax. As the joining fields contains a time field, the operation will find the record having the latest time (the largest time that is smaller than eTime) before the current time field value.
Execution result:
| ProductType | Origin | Quantity |
| Fruits | Shandong | 1241628 |
| Fruits | Xinjiang | 546357 |
| Fruits | Hainan | 24526 |
| Home&Personalcare | Guangdong | 7411008 |
| Meat | Qingdao | 3303230 |
| Meat | Neimeng | 657546 |
| Meat | Xizang | 2456235 |
| Bakery | Beijing | 247673 |
| Beverage | Xinjiang | 3526574 |
| Beverage | Shanxi | 6090112 |
| … | … | … |
