Refresh fails on online service but works in desktop version

[uadrian92]

 
Hey,

I cant seem to solve this problem for days, nothing that I try works. I tried to refresh the dataset countless times but always the same error.

The error message is an absolute abomination and doesn't help at all. The only sensible information is: "The key didn't match any rows in the table" but it's not really helpful either...

Everything works flawlessly in the desktop version but always fails on the online service...

Send.

Help.

The error message:

Last refresh failed: Fri Mar 20 2020 14:50:35 GMT+0100 (közép-európai téli idő)
There was an error when processing the data in the dataset.Hide details

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The key didn't match any rows in the table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Key","detail":{"type":1,"value":"[Schema = \"data-warehouse\", Item = \"mortgage\"]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.Table","detail":{"type":1,"value":"#table({\"Name\", \"Data\", \"Schema\", \"Item\", \"Kind\"}, {})"}}],"exceptionCulprit":1}}} Table: data-warehouse mortgage.
Cluster URI: WABI-WEST-EUROPE-D-PRIMARY-redirect.analysis.windows.net
Activity ID: fcb620b4-3dee-4248-bb70-115751b5d2cc
Request ID: e8c5ff65-6f3f-a840-e688-b70e719293e1
Time: 2020-03-20 13:50:35Z

[GlenanPP]

Hi Everyone

I had the same problem, the table's names on the db and in the service was equal, so the solution found here didn't work for me.

After many days and hours looking for some solution on the web withouth lucky, finally this work for me:

In the service, in the workspace were is located the datset,  in the dataset menu (3 dots) ---> configuration , Gateway and cloud connections --> Assigment --> Add to Gateway

In the new window fill all required as it was a new connection. take care with Privacy Level, in my case both was Organizational, desktop and this section.

After that refreshed my dataset and woola!! works!!.

I hope this solution work for you too.

[Devesh790]

Tried your way , but facing the same error.

[Devesh790]

Are you connecting the database using ODBC Connectors ?

[tctrout]

Below are items I would walk thru to assist in troubleshooting.  A lot of it depends on your permission level and your companies configuration.

If the the data sources are on premise (SQL Server, Network drive etc)

1. The on premise Data Gateway is not created/configured for your data source.  You will need to add it to the Gateway, or have your Gateway Admin add it, assuming it is appropiate for your organization.

 2. The data connection exists within the gateway, but the dataset was not mapped to which connection within the Gateway.  

[uadrian92 · Accepted solution]

Hi,

thanks, found the solution but oh my god...

Table names on localhost were lowercase (because it automatically converts table names on import to lowercase) while on the "live" db some tables had uppercase name and those failed.

So I guess the correct error message would be Table 'xyz.abc' doesn't exist.

I admit to my mistakes but come on, Microsoft... this one really is not on me 😄

Thanks again tho 🙂

[Anonymous]

Hello, 

I have the same problema but can't fix it 😕  1 question,   how you fixed to problem ?  where did you change the name of table ?  

[Anonymous]

Hi,

I've been getting the same error for one of my report.
Could you elaborate on how you solved the issue?
Did you just change the table names into lowercase in the database?

[mrleijzer]

Where exactly did you find the "live" database table names?

[Anonymous]

Can you tell me exactly where you found the discrepencies in the names? I migrated my gateway from a desktop to a server and am now recieving the same error you had. 

thanks, 

cj

[uadrian92]

Hello,

the difference was only that on localhost I had my table named "source" but in the live db it was "SOURCE". And while it was perfectly working in the desktop app, it failed to refresh. So make sure the casing matches.

One additional thing I found: If you are using mysql views as data source make sure the casing of tables matches there aswell. (Right click on the view, alter view). I found that I had: JOIN `data-warehouse`.`source` `src` ON .... (because I copied my create view query from localhost) instead of JOIN `data-warehouse`.`SOURCE` `src` ON .... And while it seemed to work (I mean I could query this view in Workbench), Power BI didn't seem to find the (lowercase) source table.

Not sure if this was any help, let me know if you are still receiving the error.

[mpk]

Hi , 

I am also facing same issue while connecting to a snowflake DB view. Desktop everythigns is woring but failing in online refresh. 

I have removed all transformations in report and it's simple drag and drop report still giving error in refresh. Can't seem to fix it . Please help. 

Something went wrong

There was an error when processing the data in the dataset.

Please try again later or contact support. If you contact support, please provide these details.

• Data source error{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The key didn't match any rows in the table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Key","detail":{"type":1,"value":"[Name = \"ITAI_DB\", Kind = \"Database\"]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}} Table: AIP_IBSA_CHNG_MNGMT_REPORT_VEW.
• Cluster URIWABI-US-NORTH-CENTRAL-B-redirect.analysis.windows.net
• Activity ID8a90031e-9c47-478d-93e5-e8c005a7270a
• Request ID61bca180-4da1-4cb1-f444-23ed55c4947a
• Time2020-08-11 14:54:04Z

[facabeza]

hello!

same problem 

refresh fail from scheduled but ok from desktop

canb you help me with the error? i couldt solve it

• {"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"No se puede convertir el valor \"[Binary]\" al tipo Binary."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}} Table: Hora.
• URI de clústerWABI-SOUTH-CENTRAL-US-redirect.analysis.windows.n