Schedule refresh stopped working.

[sdjensen]

Hi,

At a customer we have had a Power BI report since august 2016 with no issues with the scheduled refresh. Of course there has been made changes and improvements since, but the last changes was made on February 21st 2018, but suddenly starting on April 3rd 2018 we can no longer schedule the refresh from the Power BI site - no changes was made to the Gateway or data sources prior to error. We get this error when trying to setup the schedule "You can't schedule refresh for this dataset because one or more sources currently don't support refresh." We can update the model manually through Power BI desktop and publish it to the service.

The report uses 2 data sources, both on-premise SQL databases from the same SQL server.

In the process of trying to fix this we have updated the Gateway to the newest current version (14.16.6650.1 march 2018) and update Power BI desktop (2.57.5068.501 april 2018) and then refreshed the data in Power BI Desktop before publishing it to the service, but neither has helped?

[GilbertQ]

Hi @sdjensen

Could you possibly check what the data sources are in the PBIX file?

I am hopeful that this might be a change between the the PBIX and what is on the Power BI Service Gateway

Also if you can confirm that there were not changes to the Gateway Data Sources in the Service?

[sdjensen]

I have now investigated this issue some more and had a 2 hour support call with Microsoft yesterday and I am now very sure that this is because that Microsoft has made some changes in the service and I have recreated the issues on our development environment. The issues only happen if we republish the model, if the model is already in the service we can schedule updates.

I work as a BI consultant and we have created several models (Sales, Finance, Stock etc) for Microsoft Dynamics NAV. When we developed the model, we wanted a model that we could install at our customers as easy as possible, hence we use parameters for server and database (to the SQL database) and also a parameter to loop over several tables in the source and merge these into one table.

To explain the issue you first have to understand the way Microsoft Dynamics NAV is structured. Each company using NAV can have multiple legal entities in the same database and in the database a set of tables is then created for each entity, using the entity name as part of the same name, so if a company have 2 entities called entity1 and entity2 – 2 sets of table is created for storing data. E.g. the table for storing customer metadata is called Customer, but in the database there is 2 table one called entity1$Customer and one called entity2$Customer.

Because of the way Microsoft Dynamics NAV structures it’s database and because we wanted a model where we had to change as little as possible to install it at our customers, every table we load is created with a function that can loop over multiple entities (called companies in our model) – this approach has worked for us since we started looking at this in the summer of 2016 and we have many customers using this model.

I will give you an example of our m-code that we use to load the data from Microsoft Dynamics NAV:

So we have a table called “CompaniesToLoad” – this is a manual table and it could look something like this

Then for each table we need to load into the model we first lookup the companies (entities) from the table above that we want to be included in the model (LoadCompany = 1), for each company we then run our SQL query and then merge the result of each query into one table – the example below is from the customer table:

let
    //Get table containing each company from static table CompaniesToLoad where LoadCompany = 1
    CompaniesToLoad = Table.SelectRows(CompaniesToLoad, each ([LoadCompany] = 1)),
    //Get a list containing each value in CompanyTablePrefix
    Company = Table.Column(CompaniesToLoad,"CompanyTablePrefix"),
    //Define a function to get data from specified companies
    Companies = (Company as text) =>
let
    Source = Sql.Database(Server, Database, [Query="
--SQL Query Start
SELECT 
		'" & Company & "' + '-' + a.No_ AS 'KundeKey' 
        , a.No_ AS 'Kundenr.'
		, a.Name AS 'Kunde Navn'
		, COALESCE(a.Name,'Ukendt') + ' - ' + a.No_ AS 'Kunde'
		, a.[Customer Posting Group] AS 'Debitor Bogf. grp.'
		, COALESCE(c.[Name],'Ukendt') AS 'Kunde Land'
		, a.City + ', ' + COALESCE(c.[Name],'Ukendt') AS 'Kunde By'
		, COALESCE(a.[Salesperson Code],'N/A') AS 'Sælger Kode'
		, COALESCE (b.Name, 'Ukendt') AS 'Sælger Navn'
		, COALESCE (b.Name, 'Ukendt') + ' - ' + COALESCE(a.[Salesperson Code],'N/A') AS 'Sælger'
    FROM [dbo].[" & Company & "$Customer] a
    LEFT JOIN [" & Company & "$Salesperson_Purchaser] b
		ON	a.[Salesperson Code] = b.Code
	LEFT JOIN [" & Company & "$Country_Region] c
		ON	a.[Country_Region Code] = c.Code
--SQL Query End
    "])
in
    Source,
    //Call above select for each company
    LoadCompanies = List.Transform(Company, each Companies(_)),
    CombineData = Table.Combine(LoadCompanies),
    EnglishTranslation = Table.RenameColumns(CombineData,{{"Kundenr.", "Customer No."}, {"Kunde Navn", "Customer Name"}, {"Kunde", "Customer"}, {"Debitor Bogf. grp.", "Customer Posting Grp."}, {"Kunde Land", "Country"}, {"Kunde By", "City"}, {"Sælger Kode", "Salesperson Code"}, {"Sælger Navn", "Salesperson Name"}, {"Sælger", "Salesperson"}})
in
    EnglishTranslation

I tried creating a model where I have removed the loop from all our tables and published this to my workspace and if I do this I am able to schedule the refresh – the above code then looks like this:

let
    Source = Sql.Database(Server, Database, [Query="
--SQL Query Start
SELECT 
		'CRONUS EXT International Ltd_' + '-' + a.No_ AS 'KundeKey' 
        , a.No_ AS 'Kundenr.'
		, a.Name AS 'Kunde Navn'
		, COALESCE(a.Name,'Ukendt') + ' - ' + a.No_ AS 'Kunde'
		, a.[Customer Posting Group] AS 'Debitor Bogf. grp.'
		, COALESCE(c.[Name],'Ukendt') AS 'Kunde Land'
		, a.City + ', ' + COALESCE(c.[Name],'Ukendt') AS 'Kunde By'
		, COALESCE(a.[Salesperson Code],'N/A') AS 'Sælger Kode'
		, COALESCE (b.Name, 'Ukendt') AS 'Sælger Navn'
		, COALESCE (b.Name, 'Ukendt') + ' - ' + COALESCE(a.[Salesperson Code],'N/A') AS 'Sælger'
    FROM [dbo].[CRONUS EXT International Ltd_$Customer] a
    LEFT JOIN [CRONUS EXT International Ltd_$Salesperson_Purchaser] b
		ON	a.[Salesperson Code] = b.Code
	LEFT JOIN [CRONUS EXT International Ltd_$Country_Region] c
		ON	a.[Country_Region Code] = c.Code
--SQL Query End
    "]),
    EnglishTranslation = Table.RenameColumns(Source,{{"Kundenr.", "Customer No."}, {"Kunde Navn", "Customer Name"}, {"Kunde", "Customer"}, {"Debitor Bogf. grp.", "Customer Posting Grp."}, {"Kunde Land", "Country"}, {"Kunde By", "City"}, {"Sælger Kode", "Salesperson Code"}, {"Sælger Navn", "Salesperson Name"}, {"Sælger", "Salesperson"}})
in
    EnglishTranslation

[sdjensen]

By further investigating I have narrows my issues down to the following to lines of code where I loop over the different companies and then merge the result into one table...

    LoadCompanies = List.Transform(Company, each Companies(_)),
    CombineData = Table.Combine(LoadCompanies),

The parameters (" & Company & ") in the code is not the issues - if I create a static parameter for company (like my server and database parameters) and publish this model to the service I am able to schedule the refresh.

I suspect that MS in the lastest change to the service have made some kind validation against the SQL source, but my company parameter is a list of values and not just one value, hence validation is not posible, because the company parameter is unknown until the list is populated with values and used in the function. It's really anoying - we have been using this technique since the summer of 2016 and have sevaral customers that is using our models, and right now we can't make changes to any of these or install the models at new customers.

[sdjensen · Accepted solution]

Apparently Microsoft have made some changed to the service - atleast I am now able to schedule refresh on republished data models.

[Framet]

I ended up removing all parameters from my queries and functions in an attempt to get this to load reliably but previosuly this hadn't helped. I also had a recursive query across workbook tabs of an excel file which I had to replace with an equivilent SSIS package and point Power BI at a SQL table even though this worked without issue for many weeks.

On my issue, I have recently heard back from Microsoft support who indicate something has been "fixed" with the cluster and my reports indeed refresh again so perhaps we have different issues with the same error message?

[Anonymous]

Hi @Framet, instead of creating a full SSIS package to create a table you can always create a view to pull the data into your model. Since you are refreshing on a schedule pulling from a view should not be determinal.

[Framet]

I can't help much other than to say we have had the exact same issue triggered almost at the same time. I'd say arround the 28th of March.  

To make things worse some report could refresh intermittantly before getting these exact errors other would fail 100% of the time. "Cancellation occured" is the most comment error. All these reports had been in production for months and refreshing error free. The reports can refresh error free in the deskop client.

North US service, latest gateway and now April Desktop Client (Which also seems to have seperate issues opening files.)

I have tried an extensive list of things to try and find the route cause and still haven't got down to it. I have a ticket with Microsoft who also haven't given any constructive help yet, in fact at one point the decided to google the error whilst on a desktop share :S. It almost appears to be capactiy related issue for me but I can't say this with any certainty other than it is our more extensive reports that suffer but what has changed to make them fail after months of error free refreshes I'm not sure. 

[kcantor]

@sdjensen

I ran into this problem in the past. In my case, the file was originally pulled into the service through sharepoint. At some point a republish was completed from the desktop and the scheduled refresh through sharpoint broke. Microsoft had to go in and reset it from the backend. 

Was your file brought in through sharepoint?

[bdunzweiler]

I just posted a similar question (issue) on the forum with the same error (I didn't see yours for some reason).   We narrowed it down to usage of Value.NativeQuery.  We don't have an answer yet as to why that is the cause, but the reports were working fine and then we published an update unrelated to the query and then we got the error.  This appeared for us the week of April 2nd.  Wasn't sure if your reports use this feature as well.  If so, then we could combine the issues.