Desktop refresh vs gateway refresh on service

Hi 

I feel sure my question must have already been asked and answered but I’ve been unable to find a search term that finds an answer that matches my experience. 

I’ve tried “desktop refresh vs gateway refresh” and “scheduled refresh vs manual refresh” and both find many discussions about refresh issues but the underlying issue is never quite what I’m looking for.  The ‘Service’ forum seems like it might be a suitable place to post.  Here’s a description of my scenario that hopefully has sufficient detail. 

• Dataset draws data exclusively from SQL 2012 database living on an AWS cloud server
• No native queries are used in the Get Data phase
• There is extensive processing of the data in the Transform Data phase using Power Query, including:
  • Several merge query operations
  • One pivoted table, duplicated and filtered with each version filtered in a mutually exclusive way to allow different post-filter data type formatting (Example 1)
  • Use of 3 custom functions, created from imported SQL UDFs for the data type formatting
  • One imported table that is duplicated to several variants each filtered and processed using DAX – the peculiarity of that one particular source table allows for different data types in the same column across the whole table.   (Example 2)
• Total size of pbix after successful refresh of the data in the Desktop version = 171Mb. 
• Time to refresh entire dataset in single refresh operation in Desktop = 16 minutes.  16 minutes to refresh the entire database ready to use the full reporting power of PowerBI is fine.  That allows for daily refresh and even intra-day refreshes. 
• 2 hours after publishing to Service (upon which a refresh begins) and the refresh shows no signs of completion.  I’ve monitored Task Manager on the server and it has been pinned at 99% CPU throughout.  Heavy consumer processes are ‘Microsoft Mashup Evaluation Container’.  The refresh eventually timed out. 
• Dataset is configured to refresh in the Service via an On-Premises Gateway installed on the same AWS server as SQL2012.

Example 1

Source Format has 3 columns:

CaseID (integer), FieldID (integer), Value (text)

There is a merge to another table on FieldID that expands out to give Field Type

Pivot turns data into:

1 row per case, one column for each field containing the field value. 

Pivoted data is then duplicated, filtered by type (text, date and number) and the imported custom functions used to format the values. 

Example 2

Source data looks something like this:

Examples of the DAX to give formatted data for ‘table type 1’ and ‘table type 2’ based on GroupID are:

Filter1 = SUMMARIZE( FILTER(GroupTableExample,GroupTableExample[GroupID]=1),GroupTableExample[ID],

"Caseid",max(GroupTableExample[InstanceID]),

"DateColumn",if(len(max(CustomFieldGroupTable[Column01]))= 10 && LEFT(max(CustomFieldGroupTable[Column01]),1) = """" && RIGHT(max(CustomFieldGroupTable[Column01]),1) = """",DATE(LEFT(SUBSTITUTE(max(CustomFieldGroupTable[Column01]),"""",""),4),MID(SUBSTITUTE(max(CustomFieldGroupTable[Column01]),"""",""),5,2),RIGHT(SUBSTITUTE(max(CustomFieldGroupTable[Column01]),"""",""),2)),BLANK()),

"TextColumn",SUBSTITUTE(SUBSTITUTE(max(GroupTableExample[Column02]),"[""",""),"""]",""),

"NumberColumn",SUBSTITUTE(max(GroupTableExample[Column03]),"""","")

)

Filter2 = SUMMARIZE( FILTER(GroupTableExample,GroupTableExample[GroupID]=2),GroupTableExample[ID],

"Caseid",max(GroupTableExample[InstanceID]),

"TextColumn",SUBSTITUTE(SUBSTITUTE(max(GroupTableExample[Column01]),"[""",""),"""]",""),

"DateColumn",if(len(max(CustomFieldGroupTable[Column02]))= 10 && LEFT(max(CustomFieldGroupTable[Column02]),1) = """" && RIGHT(max(CustomFieldGroupTable[Column02]),1) = """",DATE(LEFT(SUBSTITUTE(max(CustomFieldGroupTable[Column02]),"""",""),4),MID(SUBSTITUTE(max(CustomFieldGroupTable[Column02]),"""",""),5,2),RIGHT(SUBSTITUTE(max(CustomFieldGroupTable[Column02]),"""",""),2)),BLANK()),

"NumberColumn",SUBSTITUTE(max(GroupTableExample[Column03]),"""","")

)

These work in their real-life form - if I've made any blunders while manually amending them into generic and anonymous examples please disregard.  

I’ve used all of these techniques in some form in other datasets that are published to the Service and for which scheduled refresh is configured to use the On-Premises Gateway and all refresh within tolerances of the desktop refresh time.  They all used native queries at the GetData stage and did reduce the size of the dataset in the pbix to approximately 40Mb.  However, my requirement now is to deliver the entire database for use in a single dataset and it seems native queries are to be avoided in general terms anyway. 

I recreated this dataset from the ground up to exclude native queries because I had concerns about the processing load on the SQL server.  However, if the heavy processing on the server is ‘Microsoft Mashup Evaluation Container’, does that mean that the bottleneck with this approach is the processing power of PowerBI and/or the Gateway?

What might I need to change about my approach to be able to deliver a dataset that can be successfully refreshed on a schedule?

Apologies if this duplicates any other question - I've looked hard at related questions in the hope of working out the possible issues for myself even if no other single post perfectly reflects my issue but there's so much going on and my knowledge is sufficiently lacking to be able to connect all the dots.  I would greatly appreciate any help from the community and would be happy to contribute if this chimes with any other user having similar difficulties.  

Kind regards

mmilegal