cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
soldstatic
Resolver I
Resolver I

Dual Storage Mode Refresh In Service

Dual storage mode is working fairly well for one of my reports, however once I publish it to the service it fails to refresh. The error code states that the query result is too large. I tried to set up incremental refresh for these tables, but apparently direct query doesn't work with the parameters, because when I filter on the parameters it says 'this transformation is not supported for direct query'. How do I get my large, dual mode, composite model data set to refresh in the service?

 

Thanks,

Andy

5 REPLIES 5
Norobo
Regular Visitor

I get the same error.  Refresh works fine in the tool, but fails because the query is too large in the gateway service with a scheduled refresh.

v-diye-msft
Community Support
Community Support

Hi @soldstatic 

 

If the query result is too large, you can try below some best practices to optimize your model:

 

  • Remove unused tables or columns, where possible. 
  • Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.  
  • Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique DateTime values into separate columns – for example, month, year, date, and so on. Or, where possible, use rounding on high-precision fields to lower cardinality – (for example, 13.29889 -> 13.3).
  • Use integers instead of strings, where possible.
  • Be wary of DAX functions, which need to test every row in a table – for example, RANKX – in the worst case, these functions can exponentially increase run-time and memory requirements given linear increases in table size.
  • When connecting to data sources via DirectQuery, consider indexing columns that are commonly filtered or sliced again. Indexing greatly improves report responsiveness.  

For more information on optimizing data sources for DirectQuery, see DirectQuery in SQL Server 2016 Analysis Services.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Thanks, I will give some of that a shot but doubt it will do much. I know the data set is large, and I don't need to refresh the whole data set each time. I would rather set up change detection or incremental refresh. Also - why does one have to set up ranged loads for incremental refresh instead of just doing change detection (ie look at modified DT column). 

 

dkangel21
Frequent Visitor

We are having a similar issue sort of.  The Dual Mode datasets do not always refresh.  When refreshing via desktop it works fine but when doing a refresh in service not all of the records come across.  I did a profiler via SQL Server and it showed the calls were being made.  They messed something up bad.

Anonymous
Not applicable

Hi,

 

Did you manage to resolve this? I am facing a similar issue.

 

Thanks

 

Mike

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 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