cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jordandakota
Frequent Visitor

Premium Per User - incremental refresh with effectiveDate - tmsl

Hey all,

 

I have a premium per user workspace, and I'm connected to the workspace in SSMS and am trying to run an incremental refresh that also captures future dates for my budget. I'm having some issues where the error's returned aren't providing me any troubleshooting hints.

 

To do this, I should be able to run something like:

 

{
   "refresh": {
   "type": "full",
   "applyRefreshPolicy": true,
   "effectiveDate": "12/31/2021",
   "objects": [
         {
            "database": "dataset_name"
         }
      ]
   }
}

 

Which works fine, but as soon as it gets down to refreshing the partitions of the fact table that has actual data, I get a response of bad request (400). All of the dimensions refreshed fine, all of the 0 row partitions refreshed fine, but the data breaks it.

 

Additionally, If I try to instead run this:

 

{
   "refresh": {
   "type": "full",

   "applyRefreshPolicy": true,
   "effectiveDate": "12/31/2021",
   "objects": [
         {
            "database": "dataset_name",
            "table": "fact_table_name"
         }
      ]
   }
}

 

It comes back with:

 

Executing the query ...
Error -1052311437: We have moved the session with ID '4A3970B7-728F-41BC-8A92-AF5352482BB1' to another Power BI Premium node. This session movement temporarily interrupted this trace. Tracing will resume automatically as soon as the node movement completes.
The remote server returned an error: (400) Bad Request.

Technical Details:
RootActivityId: ae1c8f91-9218-4e96-b2d4-4d70152cc0a3
Date (UTC): 1/20/2021 8:18:55 PM
Run complete

 

Further:

 

{
   "refresh": {
   "type": "dataOnly",
   "objects": [
         {
            "database": "dataset_name",
            "table": "fact_table_name"
         }
      ]
   }
}

 

Ran through the GUI (right click, process, data only, go) just sits there spinning and never returns.

 

I'm using the guidance shown here:

 

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

 

Which states:

 

The following parameters can be inserted into the TMSL refresh command to override the default incremental refresh behavior.

  • applyRefreshPolicy – If a table has an incremental refresh policy defined, applyRefreshPolicy will determine if the policy is applied or not. If the policy is not applied, a process full operation will leave partition definitions unchanged and all partitions in the table will be fully refreshed. Default value is true.

  • effectiveDate – If an incremental refresh policy is being applied, it needs to know the current date to determine rolling window ranges for the historical range and the incremental range. The effectiveDate parameter allows you to override the current date. This is useful for testing, demos, and business scenarios where data is incrementally refreshed up to a date in the past or the future (for example, budgets in the future). The default value is the current date.

{
   "refresh": {
   "type": "full",

   "applyRefreshPolicy": true,
   "effectiveDate": "12/31/2013",

   "objects": [
         {
            "database": "IR_AdventureWorks",
            "table": "FactInternetSales"
         }
      ]
   }
}

1 ACCEPTED SOLUTION
GilbertQ
Super User II
Super User II

Hi @jordandakota 

 

Can you please make sure you have installed the latest version of SSMS which is 18.8

 

This version includes updates to allow the use of Power BI Premium Per user XMLA end points

 

You can download and install it here: Download SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) | Microsoft Docs





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

7 REPLIES 7
GilbertQ
Super User II
Super User II

Hi @jordandakota 

 

Yeah that should solve your issue.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

GilbertQ
Super User II
Super User II

Hi @jordandakota 

 

Can you please make sure you have installed the latest version of SSMS which is 18.8

 

This version includes updates to allow the use of Power BI Premium Per user XMLA end points

 

You can download and install it here: Download SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) | Microsoft Docs





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

I have 18.7.1, I'll install 18.8 now and try again.

Hi Jordan,

 

Currently, I am also facing the same issue while trying to process the partitions of a fact table using XMLA endpoint. Could you please let me know if you were able to solve the issue?

 

Regards,

Abhijeet 

Yes, I was able to solve my issue. You need to update ssms to the newest version if processing through ssms and update your drivers (which should be done with the ssms update). If using powershell, update your powershell modules.

 

I have a new issue of not being able to process using the xmla endpoint with an app service principal however, which essentially leaves me in the same place since I can't automate the processing without using a named user with a license.

Thanks a lot Jordan. I will try to update the ssms version and check again if it works for us.

Yes that is correct because this is licensed per user you currently cannot use Service principals.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.