Key Expression Error: The key didn't match any rows in the table

[Anonymous]

I am connected to my gateway and I setup a schedule refresh for my dataset that is connected to an oracle server. However every time the dataset it set to refresh I get the (Key Expression Error: The key didn't match any rows in the table). The data and table is still good considering I can update maually on my power bi desktop. However when I try online to do refresh now or th schedule refresh I keep getting this error.

[Eric_Zhang]

@Anonymous

Based on my test, the error message in subject would be caused by missing tables/views, either dropped or the user set in data source don't have access to. Please check the highlighted part in your refresh history to determine what is the problematic table.

Also make sure that you've set the correct user with sufficient premission in the data source of your gateway. If the user is correct, try to delete and re-create the data source. If it still doesn't work, try to save your pbix in another name and publish it as a new report.

[Anonymous]

Hello @Eric_Zhang

The credentials I have entered has access to all data in our Oracle Database. The table hasn't been altered in anyway since I have uploaded it and setup the gateqay initially. I have also re-published several times under the same name and a different name and still nothing. When I go to my refresh history and look at the technical details as you did I do not see anything you are seeing except 

-Data Source Type

-Data Source Error

-Cluster URI

-Activity ID

-Request ID

-Time

in that order. Nothing that gives me any information as to what specifically. 

[Eric_Zhang]

@Anonymous

Then can you try to capture what query does the Power BI Service send to your Oracle server? There should be some trace tool for Oracle.

[Anonymous]

@Eric_Zhang

[Anonymous]

Power BI Gateway Query:

select pkcol.COLUMN_NAME as PK_COLUMN_NAME

     , FKCON.OWNER AS FK_TABLE_SCHEMA

     , FKCON.TABLE_NAME AS FK_TABLE_NAME

     , fkcol.COLUMN_NAME as FK_COLUMN_NAME

     , fkcol.POSITION as ORDINAL

     , FKCON.OWNER || '_' || FKCON.CONSTRAINT_NAME as FK_NAME

from ALL_CONSTRAINTS FKCON

   , all_cons_columns fkcol

   , all_cons_columns pkcol

where pkcol.OWNER = FKCON.R_OWNER

and pkcol.CONSTRAINT_NAME = FKCON.R_CONSTRAINT_NAME

and FKCON.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME

and FKCON.owner = fkcol.owner

and fkcol.POSITION = pkcol.POSITION

and FKCON.CONSTRAINT_TYPE = 'R'

and pkcol.OWNER = N'HES'

and pkcol.TABLE_NAME = N'EOM_SALES_DATA_VW'

order by FKCON.OWNER || '_' || FKCON.CONSTRAINT_NAME, fkcol.POSITION

Power BI Desktop Query:

SELECT "$Table"."CUSTOMER"           AS "CUSTOMER"       ,

        "$Table"."SITE"                           AS "SITE"           ,

        "$Table"."CORPORATE"              AS "CORPORATE"      ,

        "$Table"."CUSTOMER_GROUP"  AS "CUSTOMER_GROUP" ,

        "$Table"."CUSTOMER_CLASS"    AS "CUSTOMER_CLASS" ,

        "$Table"."INVOICE_MONTH"      AS "INVOICE_MONTH"  ,

        "$Table"."FISCAL_YEAR"              AS "FISCAL_YEAR"    ,

        "$Table"."FISCAL_QTR"               AS "FISCAL_QTR"     ,

        "$Table"."FISCAL_MONTH"         AS "FISCAL_MONTH"   ,

        "$Table"."COMPANY"                 AS "COMPANY"        ,

        "$Table"."LOCATION"                 AS "LOCATION"       ,

        "$Table"."BUDGET_CATEGORY"  AS "BUDGET_CATEGORY",

        "$Table"."EXCLUDED"                 AS "EXCLUDED"       ,

        "$Table"."BUDGETED"                 AS "BUDGETED"       ,

        "$Table"."AR_NAME"                  AS "AR_NAME"        ,

        "$Table"."AC_NAME"                  AS "AC_NAME"        ,

        "$Table"."CAM_NAME"               AS "CAM_NAME"       ,

        "$Table"."CAC_NAME"                AS "CAC_NAME"       ,

        "$Table"."HIS_PM_NAME"          AS "HIS_PM_NAME"    ,

        "$Table"."HIS_AC_NAME"          AS "HIS_AC_NAME"    ,

        "$Table"."SALES"                       AS "SALES"          ,

        "$Table"."BUDGET"                   AS "BUDGET"         ,

        "$Table"."DIFF"                         AS "DIFF"           ,

        "$Table"."MONTH_CLOSED"    AS "MONTH_CLOSED"   ,

        "$Table"."ROLLING_PERIOD"    AS "ROLLING_PERIOD" ,

        "$Table"."ROLLING_MONTH"     AS "ROLLING_MONTH"  ,

        "$Table"."SALES_REGION_CODE" AS "SALES_REGION_CODE"

FROM "HES"."EOM_SALES_DATA_VW" "$Table"

So what is going on that the query changes it so that its not readable? 

[Eric_Zhang · Accepted solution]

@Anonymous

Based on my test, the refresh from desktop and gateway use the same queries. In your previous reply, you say "The credentials I have entered has access to all data in our Oracle Database", Can you confirm it is the same credential used in your desktop where, as you state, the refresh works fine. If not the same, login Oracle with that credential and try to running the queries captured from Power BI gateway.

[Anonymous]

You were right it all came back to the credentials I was using to login. Sorry I am interning and trying to show this to my bosses and I guess what I was logging in with didn't have the right credentials. Thank you for all the help!

@Eric_Zhang

[kcantor]

@Anonymous

Check your query. The only time I have received that error was when their was a small change to my source table. I double checked my query (and made a slight adjustment) then republished the data model. It fixed the problem.

[Anonymous]

I haven't altered my data which is why I am confused I setup the connection made a report on power bi desktop and then published it to power bi gateway. Setup schedule refresh and it gives me this error. I went back and clicked edit queries on power bi desktop to check query and clicked refresh preview to try to help the problem. Then republished and still nothing.