cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xfidelitydj
New Member

Incremental Refresh Error

Hello,

 

After setting up incremental refresh based on a date range parameter filter applied to "Modified Date" of a SharePoint Online list, we receive the following error in our Premium Service Refresh History

 

The JSON DDL request failed with the following error: Failed to save modifications to the server. Error returned: 'Parameter substitution failed with following error: 'Neither mashup nor gateway connection string detected for incremental refresh parameter substitution'. Parameter substitution failed with following error: 'Neither mashup nor gateway connection string detected for incremental refresh parameter substitution'.

 

Any suggestions?

 

Thanks!

1 ACCEPTED SOLUTION
gmarrone
New Member

I get the same error message, and I am too trying to implement incremental reload (as per May release of Power BI).

Honestly, I believe this is a bug as the feature is still in preview.

View solution in original post

24 REPLIES 24
ayushnigam268
Helper II
Helper II

Hi, 

I am pulling data from Salesforce.com and getting the same error.

Anonymous
Not applicable

Hi All

 

I have been getting a different error.  Please see below  desccription.

My time out limit is set to 5 hours and this returns before the limit is exceeded. 

 

 

Processing error:The JSON DDL request failed with the following error: Failed to save modifications to the server. Error returned: '{"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":"Unable to connect to the remote server"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} {"error":{"code":"DM_GWPipeline_Gateway_CanceledError","pbi.error":{"code":"DM_GWPipeline_Gateway_CanceledError","parameters":{},"details":[],"exceptionCulprit":1}}} '..
  

The error description that I catch is "Unable to connect to the remote server". That tells me that you need to dig for the error elsewhere.

Anonymous
Not applicable

Hi 

 

Thanks for reply,

 

I am currently changing my source from Json to Oracle DB, Will test this out and if it passes will update you.

However when you mean elsewhere do you mean as in where I am fetching the data from source.

Everything is setup accordingly in the desktop.

 

Also just remembered, My gateway version is 13.0.5780.159, I found this on the Question mark About PpowerBI.PNG

 

What I want to know is how recent is this version and also if the versions Desktop & PowerBI aren't current and, will it affect the incremental refresh.

 

Regards

 

In recent versions of both the Desktop and the Gateway software you see not only a version number, but a month and year next to it too on the about screen.

 

Latest versions from June are:

Desktop: 2.59.5135.421 64-bit (June 2018)

Gateway: 14.16.6724.2 (June 2018)

 

So your gateway version is at least a few month old, it may predate the release of incremental refresh preview. Thus that may very well be the reason why it doesn't work for you. Try after installing the latest gateway version.

 

Keeping the gateway and the Desktop up to date is a good idea. The mashup engine used by them differ, but they are kept align in the latest version. At least compatible enough, sometimes with more limitations on the gateway than on the desktop. But your best chance is always to use the latest versions.

 

P.S.:

Unfortunately you cannot test the incremental refresh locally in the Power BI Desktop software. It is supported only through the gateway.

Anonymous
Not applicable

This is a little frustrating. I've set up the parameters properly in Power BI Desktop, added a filter to a date/time field with the parameters, set up the table with incremental refresh, and published it into a premium workspace. After all of that, the paramters simply don't show up under the "parameters" section in the Power BI Service.

 

Sample.png

 

This then leads to the same error that the original poster is referring to.  Does anyone know why aren't the parameters showing up properly in Power BI Service?

The parameters don't show up by design for now. This is a known and documented limitation.

 

My guess for the why is the handling of the RangeStart and RangeEnd parameters, which has to be automatic for the incremental refresh. Hence they had to disable their management on the UI, because that would lead to confusion. And instead of implementing it in a granular way - which may be underway for the generally available version -, they simply disabled parameters in general.

 

I really dislike this constraint, because from the end user's point of view it makes no sense, and it imposes a big limit on how one would deal with report development.

Anonymous
Not applicable

So if the parameters are not showing up in the Power BI Service and refreshing the dataset causes an error due to the parameters not being there, does this mean that incremental refresh isn't going to work currently?  Is there a work around to this or are we stuck waiting for a more stable release by Microsoft before we can use incremental refresh?

The parameters have the values, which you were set in Power BI Desktop before you published the report to the Power BI Service. Certainly the refresh still works, otherwise releasing this feature wouldn't have made any sense at all.

Anonymous
Not applicable

I guess I don't understand then.  I've set up the RangeStart (Value of 01/01/2018) and RangeEnd (Value of 12/31/2019) parameters in Power BI Desktop.  I've implemented them in a table with a modified date >= RangeStart and modified date < RangeEnd. 

 

I've then went to the incremental refresh properties of this table and set the following rules:

Sample 3.PNG

 

I went to the source data and saw that "Modified" date for all rows is 6/21/2018 5:26 PM.  I published this to a premium Power BI workspace.  I then added a new row to the source data where the "Modified" date now shows 6/26/2018 12:40 PM.  

 

With all this set, I expect Power BI to keep all of the data when I hit refresh since the data is within the 10 days "store rows" rule I defined in Power BI Desktop.  I then expect Power BI to pick up the one additional row of 6/26/2018 since it is within the 2 days "refresh rows" rule I defined in Power BI Desktop and add it to my dataset.

 

Instead, when I hit refresh in Power BI Service, I get the following error:

Sample 2.PNG

 

I thought this error shows up because Power BI Online doesn't seem to show my parameters, but you're mentioning that that is by design for Microsoft.  So then, why am I getting this error which seems like it's saying that Power BI Service isn't detecting any parameters in my report?

Now is the time to dig in to the details of implementing incremental refresh. The details, which are not documented yet.

 

I ran into the below or a very similar error when I had composite queries combined with incremental refresh. E.g. you have a query, which uses another query to produce its result - one way or another. This may be a classical query append or merge. Or it may be just an innocent looking helper query for the query string template. This is typical in scenarios, where you use the various parameters to put together the SQL command dynamically.

The mashup engine checks for each query involved, and attempts to pass the range parameters to them. If the query takes them and does something with it, then there is no error. If the parameters are not used at all, then an error like yours is thrown.

PBI Desktop detects this requirement only on the final query: if the range parameters are not used, then it doesn't allow incremental refresh on that query at all.

 

Here is the catch:

The final query may have the range parameters used, but a subquery may not use it. This case remains unchecked in PBI Desktop, and it will allow incremental refresh to be configured. But later on it will throw an error in the Power BI Service at the first data refresh attempt.

 

So if this is your case, then make sure that you use the range start/end parameters in all the referenced queries. They don't necessarily need to be meaningful, but they must be used even if they don't do anything. Or eliminate the subqueries if you can.

 

This was only one theory for the error based on my experience. There may be another explanation too.

Anonymous
Not applicable

You were absolutely correct in your analysis of me having another query that was building my 2nd query which is where the parameters were filtering a "Modified" date.  

 

I removed this query for testing purposes and put all the logic in one query with the parameters at the end filtering my "Modified" date.  Unfortunately, I still get the same error in Power BI Service.  All I've left in my data model is one query and the two parameters to test.  I still get the same error.  I'm at a loss of solutions now.

You can always open a support case to let them dig out the details of the generic error, which you see. Digging in the dark left me out of clues for moving on in your case.

Just adding my name to the list. I can only cross my fingers that it's been fixed in the upcoming update. I am getting the same error, and although I am using merged queries I have followed the instructions provided in the documentation.

 

It would be good if the desktop was also able to utilise the incremental refresh and therefore throw up the error before publishing to the service. 

@hoffmanrat

I agree that having the incremental refresh supported in the Power BI Desktop application would help a great deal. I would be surprised if it wouldn't be in their roadmap. In the meantime, maybe a new idea should be opened for that - if there isn't one already. If anybody finds it or creates it, please link it here.

ElsD
Advocate I
Advocate I

Same error here. My source is an Excel list on Sharepoint.

Normal refresh works fine. I can see the parameters in the Service. So they are set-up well.

 

increm_ok.PNG

 

After activating the incremental refresh, the service can't find the parameters anymore.

 

increm_nok.PNG

Same error here too. Files in a SharePoint document library. Using the RangeStart and RangeEnd parameters to filter file names to pick up the last n day's files only. The logic works fine when I test it with full refresh and setting the parameters manually. But the same fails with incremental refresh.

Anonymous
Not applicable

Hi,

 

I am also facing same issue for Sharepoint dataset.

The Parameters setting is greyed out so cannot test the workaround suggested. It would be good to have a list of what data sources work with incremental refresh.

 

 

Same error here using application insights source.

Anonymous
Not applicable

Hi 

 

I've been getting same error:

 

 The JSON DDL request failed with the following error: Failed to save modifications to the server. Error returned: 'Too few arguments were passed to the DATESINPERIOD function. The minimum argument count for the function is 4.  


Any help would be apprciated 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors