KB309252: Usher Analytics location data is mapped incorrectly after upgrading the ETL to Kafka

SYMPTOM:
This technical note describes a known issue with location data in the Usher Analytics ETL when upgrading the ETL to Kafka in Strategy 10.4 and above. Transactions that did not get mapped to street addresses by the SQL ETL are mapped to incorrect addresses after the upgrade to the Kafka ETL. A workaround is provided for this issue.
 
STEPS TO REPRODUCE:

1. Install Strategy 10.4.0 and enable the SQL version of the ETL for the Usher Analytics project.
2. Download Redis (if using 10.5 this step is not needed) and upgrade the ETL to Kafka.
3. Start the Kafka ETL.
4. In the Usher Analytics warehouse ('idm_intel') run the following SQL:
   

   
   select * from lu_geo_location where location_id=-1

   
   
5. If the columns tran_long and tran_lat are not null, any transaction mapped to location_id =-1 will have an incorrect address.

 
CAUSE
This issue occurs when there are transactions with unprocessed location data from the SQL ETL. 
 
ACTION
Contact Microsoft Technical Support for an update on the status of the issue.
 
WORKAROUND
Take a backup of the idm_intel databse before following the steps below.
To workaround the issue, connect to the idm_intel database and execute the following scripts:

update access_transactions set location_id=src_tran_id+10000 where src_tran_id>0 and location_id=-1;
update access_transactions set location_id=src_tran_id where src_tran_id<0 and location_id=-1;

delete from lu_geo_location where location_id=-1;

insert into `lu_geo_location`(location_id,street_address,city_id,city_name,state_id,state_name,country_id,country_name,address_id)
 values (-1,'Unknown',-1,'Unknown (Unknown)',-1,'Unknown',-1,'Unknown',null);