Data Sources don't support refresh when in the same Report

[funcy]

Hello.

This is my first post here and i know, this topic is not a rarity here. My Problem is, that i can't refresh my dataset via PBI web, as one of the Data Sources seem not to be supported. 

I have one .xlsm (Excel File), one Oracle Database, a Table containing a Calendar of 3 Years, created through M-Functions, and one Jira Instance (multiple APIs to use, each return a JSON File which is processed by Power BI to a table) to get Data from.

Each of the Sources work flawlessly when i create a single report for them. Only in my Actual Report, where all of them are combined, it gives me that Error: "You can't schedule refresh for this dataset because one or more sources currently don't support refresh."

Is there any kind of combination method or something, that is not supported by the sheduled refresh? I don't have parameters in the Queries, all of them are hardcoded. 

For the Oracle DB Connection I mainly use the Hierarchical Navigation Way, and one Time an actual SQL-Query (select * from Table where ZEIT_START >= trunc(sysdate)-365).

Also, the "Include Relationship Columns" function is activated, but works on the Test Reports as well.

I have absolutely no Idea what the unsupported source could be.

Thank you.

[v-yuezhe-msft]

@funcy,

Do you connect to On Premises JIRA Instance or JIRA Instance? And do you use function to obtain the JIRA source? We will appreciate that if you can post the code of JIRA data source in Advanced Editor, do mask sensitive information when sharing the code.

Besides, are you able to set schedule refresh for each dataset when you create single reports for each data source? I create a single report that connects to Oracle database via the similar query as yours, when I upload PBIX file to Power BI Service, there is no issue when setting schedule refresh for the dataset as long as I add the Oracle source within gateway.


Regards,
Lydia

[funcy]

Hello Again, this is the Query / queries for one datasource:

Quelle = Json.Document(Web.Contents("https://jira.company.com/rest/api/2/search?jql=issuetype=Task AND Category=Product AND updated>=-365d&startAt=0&maxResults=500")),
    ...
Quelle19 = Json.Document(Web.Contents("https://jira.company.com/rest/api/2/search?jql=issuetype=Task AND Category=Product AND updated>=-365d&startAt=9500&maxResults=500")),

    
    issues = Quelle[issues] & Quelle1[issues] & Quelle2[issues] & Quelle3[issues] & Quelle4[issues] & Quelle5[issues] & Quelle6[issues] & Quelle7[issues] & Quelle8[issues] & Quelle9[issues] &
             Quelle10[issues] & Quelle11[issues] & Quelle12[issues] & Quelle13[issues] & Quelle14[issues] & Quelle15[issues] & Quelle16[issues] & Quelle17[issues] & Quelle18[issues] & Quelle19[issues]

Don't wonder about those many queries involved, but unfortunately the API can only return 500 Results at once, which isn't really good when expecting a few thousand results. 

I have similar queries for the other APIs:

Source = Json.Document(Web.Contents("https://jira.company.com/rest/agile/latest/board/3225/issue?startAt=0&maxResults=500"))

Source = Json.Document(Web.Contents("https://jira.company.com/rest/api/2/project?jql=category=Product&expand=lead"))

The JIRA instance is on premise, as well as the oracle database.

Yes I can schedule refresh for each of the sources, when they are in a report alone. I don't use any functions, at least none which wouldn't work with the Gateway as they are all functional in a single report. 

Thank you for your help!

[v-yuezhe-msft]

@funcy，

What code do you use to create the date table? You are also able to set schedule refresh for the Excel data source, right?

Are you able to use on-premises gateway(personal mode) to refresh your dataset? I found that we are unable to add JIRA data source within on-premises gateway, as there is no such data source.

Regards,
Lydia

[funcy]

This is the code I use for the Date Table:

let
    Start = Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-12),
    End = Date.AddMonths(Start,13),
    Count = Number.From(End-Start)*1,
    Calendar = Table.FromColumns({List.DateTimes(Start,Count,#duration(1,0,0,0))}, type table[Date=date]),

Yes, I can schedule refresh for all sources, so it also works with excel and the date table. Only not in the Report, where all of them are included together. I connect to JIRA via the source "Web" as it is only an API returning a JSON string.

I don't know if it would work with the personal one as I haven't tried it yet. But I doubt that it works as the error implied that the Report / the sources is the part where it fails already, not the Gateway.

[v-yuezhe-msft]

@funcy,

Could you please use on-premises gateway(personal mode) and check if you can schedule refresh for the dataset? And would you mind sharing me the PBIX file so that I can test? You can share PBIX file via Private Message.

Regards,
Lydia

[funcy]

Hello again,

I have tried to refresh using a personal mode gateway, but it didn't make any difference.

I will send you the template for the report so you can see my steps and data sources, but not the data as it is sensitive business relevant data.

Thank you, regards

funcy

[v-yuezhe-msft]

@funcy,

Do you use merge queries or append queries in Query Editor? I would recommend you make a backup of your current PBIX file, delete the merge queries/append queries steps in Query Editor, and check if refresh works.

If you still get the error, please remove each source in PBIX file to check when refresh starts to work.

Regards,
Lydia

[funcy]

@v-yuezhe-msft

Hello again, sorry for not responding anymore.

coincidentally I managed to make the Report refreshable (Creating a new table for a column that fetches Data from a web-source and merging the two tables afterwards).

So now, the Dataset doesn't give me the error mentioned before anymore.

The problem now is, that I can't select my Gateway in the Gateway connection Settings of the Dataset.

It just keeps loading and loading

As you see, the "Use a data gateway" radiobutton is greyed out as my gateway isn't even displayed. Why does it just keep on loading? The gateway is functional as we know, all the sources and credentials are set up and so on.

I honestly don't want to try a personal gateway before I know that there is now chance of getting this gateway to work.

The gateway is also Online and connected.

EDIT: I have installed a personal Gateway now. In older versions of my report and all of the reports that i have created to test the data sources / the gateway, I can select the Data gateway (personal mode) and it works flawlessly. But in my actual report, which is the only one that actually needs to be refreshed, it still looks like in the picture above. (I installed the personal gateway on the same machine as the normal on premises one, they're running at the same time)

Thank you,

funcy

[v-yuezhe-msft]

@funcy,

Do you use the latest version of on-premises gateway?

Regards,
Lydia

[funcy]

@v-yuezhe-msft

Yes, It's the newest version.

[v-yuezhe-msft]

@funcy，

Please open a support ticket on the support.powerbi.com site. 

Regards,
Lydia

[Seth_C_Bauer]

@funcy Are you using the Personal or (Enterprise) Gateway? Only the personal gateway supports mixing Cloud and On-Premises datasources in the same dataset. The (Enterprise) Gateway does not yet support that combination.

Is this the issue?

[funcy]

Thanks for your answer. I am using the On-premise Gateway, not the personal one. 

An old version of the report works very well using this Gateway, the only sources I added are the excel sheet and the Date Table, which is created by Power Bi itself.

Unfortunately I can't try your solution at the moment, but I will as soon as I can on monday.

But there is one Question I have: If it is the Gateways fault, shouldn't there be the error "You don't have any gateway installed or configured for the data sources in this dataset. Please install a new personal gateway or configure the data source for an existing data gateway." ? 

As this one basically says, that the problem is, that no Gateway is configured for the needed Sources, while the Error I get is "You can't schedule refresh for this dataset because one or more sources currently don't support refresh." which sounds to me more like it's the Reports / Sources fault.

I hope you can follow me.