Process Add with partitions in SSMS

[SpikeSpiegel_3]

I created this partition in SSAS (SELECT [dbo].[Dim_Orders].* FROM [dbo].[Dim_Orders] where [DD/MM/YY]>=20190701 and [DD/MM/YY]<=20190930) for Orders table, then I try to use Process Add to only add new rows but it returns this error (Failed to save modifications to the server. Error returned: 'Note. Analysis Server has persisted any security information specified as part of the Connection string portion of the Datsource object definition.
Column 'Order_Id' in Table 'Dim_Orders' contains a duplicate value '10012411' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
'.) 

I checked several times and made sure that there are no duplicates in the Orders table and in the partition, so when the partition is empty the Process Add works but when I run it once again it doesn't, what is the problem here exactly ?

[sjoerdvn]

You can't run a "process add" with the same source data twice. If you want to reload a partition you need "process data" or "process full"; or run a "process clear" prior to the add.

[SpikeSpiegel_3]

I don't think that's the problem because I need to use 'process add' to process only new rows, if I can't use it so how is it even useful ? I mean process full & data processes the whole data and I need only new data. 

[sjoerdvn · Accepted solution]

If you're using "process add" you need to make sure your query doesn't return any data that has been loaded before. To be honest I don't believe too many people are using "process add", because it requires quite some programming to do this reliable in an operational enviroment.
You might be better of using incremental refresh.

[v-zhouwen-msft]

Hi @SpikeSpiegel_3 ,

You mentioned that the operation is success when the partition is empty. However, the error message indicates that there are duplicate values.Is the amount of data large? You can write a SQL statement to check whether there are duplicate values ​​in the entire data set.

Something like this:

SELECT [dbo].[Dim_Orders].[Order_Id],COUNT(*) as count FROM [dbo].[Dim_Orders]
GROUP BY [dbo].[Dim_Orders].[Order_Id]
HAVING count > 1

If no value is returned, it means that there are no duplicate values ​​in the data set.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SpikeSpiegel_3]

I tried this code and it returned no value, so there are no duplicates. I don't think that the problem is in Sql server but from the SSAS. The issue there is that I don't know how to fix this problem and don't even know how to access it.