Schedule refresh stopped working.

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