Intermittent occurrence of random duplicates in excel report

Question

We recently noticed a issue in production that the excel is populated with random duplicates for any record intermittently. User is filtering out the data on SAIL and clicks on EXPORT button. We can see the filtered data in the excel but few records are duplicated in excel which does not exist in view or SAIL. We ran the query entity explicitly to determine the data and it is correctly retrieved as per the filters applied. 
 So there is no pattern as such to figure out why this issue is occurring. And this is only observed in Production and not in lower environments. As we are not manipulating the data anywhere, I am guessing the issue must be with the view, especially with primary key. We are currently guessing that the issue might be with the way smart service is sorting out the data based on filters applied in excel report. 
 We are using Export DSE to Excel smart service. We are using a view with SELECT ROWNUM ID and mapping this ID as Primary Key with @ID annotation on CDT. But we are not using this CDT in the smart service, we are directly passing a constant for that view in this smart service and populating the excel. 
 Appian recommends to use a table or materialized view because it has primary key specified with a constraint which is used by this smart service for sorting the data. 
 So my understanding is the Export DSE to Excel smart service requires PRIMARY KEY CONSTRAINT on DB level for sorting the data properly and ROWNUM ID wouldn't work in sorting the data properly for this smart service. 
 Is there another possibility as to why this issue might be occurring? Also, how does this Export DSE to Excel smart service works when we are using a view with ROWNUM ID?

agam · Accepted Answer

Ashwini N 
 I did notice your order by clause in here consists of 3 columns, Do that make your record unique ? 
 For eg: 
 consider the below dataset

REQUESTID 
 COL3 
 COL4 
 COL1

1 
 2 
 3 
 5

1 
 2 
 3 
 6

3 
 3 
 4 
 7

The first 2 rows have same values for RequestID, col3, col4 but different values for remaining columns, and ROWNUM over this might sometimes put 1 against row1 (col1 = 5) or row2 (col1 = 6). 
 Since Appian re-queries on your primary key which is not uniquely identifying your row, this could be the cause behind duplicate rows. 
 
 I am not equipped with Oracle DB and cannot test this myself but I would recommend to ensure your rows are unique by your order condition if you are using ROWNUM. 
 
 Alternatively, the best solution would be to use a table primary key, can you not look into to redesign the view query to achieve this ? Your rows aren't duplicated from your current view, but must be duplicated within VIEW1, what does that hold ?

Ashwini N · Answer

agam 
 You are correct. Like you described, Appian is querying on the column which is not unique identifier for the view which is why we see this duplicates. 
 Another reason why this isn't replicated on lower environments is because the VIEW1 in the mentioned query had inconsistent SQL across environments. After making it consistent, we were able to replicate this issue. 
 Solution wise - we are going to implement of materialized view for this scenario as recommended by Appian for Export smart services. (Refer link) 
 https://docs.appian.com/suite/help/21.4/Export_To_Excel_Smart_Service.html#avoiding-query-timeouts-when-exporting-data

Intermittent occurrence of random duplicates in excel report

Top Replies