¶ Description
Perform a “fuzzy” join between the 2 input tables.
¶ Parameters
Parameters:
- Match algorithm
- Originating table
- MSISDN
- Transaction time
- Terminating table
- MSISDN
- Transaction time
- Columns to join
- Prefix for joined columns
- Time window for match [in "unit of time"]
¶ About
A typical Mobile Money transaction goes, basically, the following way:
- A subscriber A wants to transfer some money to another subscriber B.
- The subscriber A sends a SMS to the central SMS server. This SMS is crypted and contains:
- The amount to transfer.
- The MSISDN from B.
- The whole transaction is logged inside 2 different tables:
- Each row of the “TABLE A” describes one money transfer between 2 subscribers identified by their MSISDN number. The columns are MSISDN_A, MSISDN_B, TRANSACTION_TIME, TRANSACTION_AMOUNT.
- Each row of the “TABLE B” describes one SMS between a subscriber MSISDN_A and the central SMS server. The columns are MSISDN, TIME, ANTENNA_ID.
We want to find, for each transaction of TABLE A, the ANTENNA_ID from the subscriber that started the transaction. In other word, we need to compute a join between the TABLE A and the TABLE B.
We could try to compute the join using the standard Join action. More precisely:
- We use the columns (MSISDN_A, TRANSACTION_TIME) from “TABLE A” as Composite-Primary Key.
- We use the columns (MSISDN, TIME) from “TABLE B” as Composite-Primary Key.
This approach is described in more detail in section 5.1.6 about Composite primary Keys. This naïve approach will fail because the “TIME” column is not exactly the same inside each of the two tables (because these are two different servers that are generating the two tables A and B and their clock is not tightly synchronized).
To be able to compute the join, we need a “Fuzzy” match on the TIME dimension. Meaning that, if the TRANSATION_TIME and the TIME are separated by less than 3 minutes (this is the “Time Window” parameter), we’ll decide that the TIME columns are “matching” (and we’ll thus produce a join and retrieve the ANTENNA_ID associated with the transaction).
The parameters of the JoinCDR action are:
- The algorithm used to compute the Fuzzy match.
- The Originating Table: the Table A: the Transaction Table.
- The Terminating Table: the Table B: the SMS Table.
- The Time window (in the example above: 3 minutes).
The JoinCDR action is written in JavaScript so you can easily tweak/modify it to suit your specific needs.
