topic Scheduled refresh failure: An item with the same key - Merged queries in Report Server

Scheduled refresh failure: An item with the same key - Merged queries

Yousra — Thu, 09 Nov 2017 09:14:50 GMT

Hello,

I've been trying to use the new Scheduled refresh option in Power BI Report Server October 2017.

The refresh fails with the message: "An item with the same key has already been added." when dealing with merged queries. It completes just fine when there is no merge action.

I've tried renaming the columns/tables but it doesn't fix the problem.

The merge action is very important so I can't delete it.

Re: Scheduled refresh failure: An item with the same key - Merged queries

v-qiuyu-msft — Fri, 10 Nov 2017 09:23:29 GMT

Hi Yousra,

Are you able to refresh the report successfully in the Power BI desktop optimized for report server Oct version?

Best Regards,
Qiuyun Yu

Re: Scheduled refresh failure: An item with the same key - Merged queries

Yousra — Fri, 10 Nov 2017 09:30:53 GMT

Yes, the report can be refreshed using Power BI Desktop.

Re: Scheduled refresh failure: An item with the same key - Merged queries

mgmeyer — Fri, 10 Nov 2017 17:58:00 GMT

Yousra thanks for reporting this issue and it is something we are looking in to. Do you have an isolated repro that you can private mesage me to help us reproduce this on our end? Thanks!

Re: Scheduled refresh failure: An item with the same key - Merged queries

Yousra — Mon, 13 Nov 2017 09:22:35 GMT

Hello mgmeyer, I've sent you a private message including an example that reproduces the error.

Thank you!

Re: Scheduled refresh failure: An item with the same key - Merged queries

shariq — Wed, 22 Nov 2017 09:05:25 GMT

Hi,

I have the same issue when i try merge action. Do you know some solution of this problem ?

Re: Scheduled refresh failure: An item with the same key - Merged queries

IGray — Thu, 23 Nov 2017 11:25:33 GMT

Same problem here, the report refreshes fine in desktop, but not on the report server, if I cut out the merge it works fine, but then the report is not complete

Update I did some further testing and for me it seems to be when i use a SQL statement as the source and merge, if I dump the data into tables on a SQL server and use these for the merge, it refreshes on the server

Re: Scheduled refresh failure: An item with the same key - Merged queries

Yousra — Fri, 24 Nov 2017 09:13:57 GMT

Thanks IGray for your suggestion.

Tried importing the data as a table instead of an sql query, then merging and the data refresh works!

Re: Scheduled refresh failure: An item with the same key - Merged queries

IGray — Fri, 24 Nov 2017 09:26:21 GMT

I have also discoverd you can use sql views to get around this problem

Re: Scheduled refresh failure: An item with the same key - Merged queries

M_Lechner — Tue, 19 Dec 2017 16:11:40 GMT

I have the same Problem and i can't fix it...

I use the October 2017 Update 2.51.4885.1423 (x64).

I can refresh it in Power BI Desktop but not scheduled on the Reporting Service

Has anyone a solution for this ?

Re: Scheduled refresh failure: An item with the same key - Merged queries

Muki — Tue, 27 Feb 2018 14:55:37 GMT

Same here... (PBI Server Version 1.1.6582.41691 (October 2017))

I got 2 tables as a web source and I need to merge them into 1 table.

These tables have already different column names but I tried to rename them in the merged table to make sure they are unique in Power Bi Desktop.

Manual refresh works fine... but scheduled refresh on Server doesn't work...

Hope someone has a workaround for this situation too :)

Grtz

Muki

Re: Scheduled refresh failure: An item with the same key - Merged queries

johncpratt — Wed, 14 Mar 2018 13:21:36 GMT

Hi all,

I can confirm the same issue on my end, with the added twist that I had a report working with a data refresh until I added a Merge Queries action. I have resolved the situation using the stated workaround of making sure both queries involved in the merge action are sourced from a table rather than a SQL query.

In my specific case, I had a SQL view-based fact table with a column named "FiscalYear" imported using a SQL query that was filtering on a separate SQL view containing a limited number of fiscal years. I also had a dimension table for the fiscal years using a table data source from the same view containing a limited number of fiscal years.

I then decided to limit the report to the current FY plus the prior 10, which required implementing the Merged Queries action on the fact table and a Keep First Rows action on the fiscal years dimension. The data refreshed fine in PBIRS Desktop (October 2017), but the scheduled refresh under PBIRS failed.

For the workaround, I modified the data source for the fact table to pull directly from the view with no SQL query filtering in Power BI. As soon as I did that, the Desktop refresh worked and running the scheduled refresh on the server also worked.

I hope this info helps identify the issue. I am looking forward to the next release of PBIRS!

Re: Scheduled refresh failure: An item with the same key - Merged queries

smelei — Fri, 16 Mar 2018 15:13:46 GMT

We've been experiencing this as well.

Raised as an issue, but haven't received any response as yet

Re: Scheduled refresh failure: An item with the same key - Merged queries

Yousra — Tue, 20 Mar 2018 17:30:25 GMT

The issue seems to be fixed with the March 2018 update!

Re: Scheduled refresh failure: An item with the same key - Merged queries

mgmeyer — Tue, 20 Mar 2018 17:56:34 GMT

Thanks Yousra for replying with the verification!