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?
Discussion posts and replies are publicly visible
Hi Ashwini28,
I did face this issue once but I updated my view's key primary to reflect to a table's primary key and it resolved your issue.
Can you not make any column as the primary key of the view which is unique?
Also, can you provide your query, the database you are using and row count of the data in the view
Problem is table's primary key won't be unique in the view because view can have multiple rows for same record. There is no unique combination for any column.
We are using Oracle 19c DB and row count in view is 12K+
This is the query. We are joining table and view for generating another view out of it:
SELECT ROWNUM ID, RD.REQUESTID, .... EN.COL1, EN.COL2, EN.COL3, EN.COL4FROM TABLE1 RDINNER JOIN VIEW1 EN ON rd.REQUESTID = en.REQUESTIDORDER BY RD.REQUESTID, EN.COL3, EN.COL4;
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
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 ?
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