Forum Discussion

mmilegal's avatar
mmilegal
Frequent Visitor
5 years ago

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 ...
Need Help
mmilegal's avatar
mmilegal
Frequent Visitor
5 years ago

Hi aj1973 

 

Many thanks for your suggestion - I appreciate your help.  My use of DAX for the transformation of some of the data was expedient for two reasons.  I had used that successfully in the past and it was quicker to be able to text-edit a formula to drop into 'create table' commands than to go through the process of adding transformation steps in Power Query. 

 

However, expedient isn't always best! 

 

As the removal of DAX seemed to help in the instance on that other thread I deleted all my tables created in DAX.  As a test and before trying to recreate the transformations in Power Query, I saved and published the dataset just with all DAX removed.  Once again, the desktop refresh went fine but the refresh in the service hogged resources and timed out.  

 

I cannot easily regress the Gateway version and I cannot change the SQL Server version.  I have checked our Gateway and it is not the current version.  I have considered updating the Gateway to the current version but it is demanding a .NET Framework version update.  That's well above me, so I've referred that on.  

 

I'm keeping an open mind.  I'm far from expert with Power BI, but I'm inclined to think if the refresh in Desktop can be successful and relatively quick that I can't have made too much of a mess of my methods of importing and transforming data.  Our Gateway version is 3000.66.4 (November 2020 Release 1) so we're not far out of date on that.  Whether updating it to 3000.77.7 (which seems to be the current version) will be significant, I don't know, but it seems worth trying.  I'll update the thread with actions/outcomes as they unfold.  

 

Cheers

 

mmilegal

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi mmilegal 

    it seems like your model is facing a version compatibilty issue. Please try to use Dataflow as connection mode and then re build a Sample report out of 2 tables(for example), then publish it. Logically when you try to connect to the SQL server through Dataflow, the Gateway is needed and a refresh is asked for. If it works then you know what to do.

    Please let me know.

     

    • mmilegal's avatar
      mmilegal
      Frequent Visitor
      5 years ago

      Hi aj1973 

      I've not used dataflows until now.  I'm trying to study up on that now.  I know I'll be limited to some degree because I understand that merging tables in the dataflow requires a premium workspace, which we do not have.  
      Progress might be a little slow from here but I will work on your suggestion.  Please forgive me if it takes a few days for me to report back on what I've tried and how it turned out.  
      Cheers

      mmilegal

      • aj1973's avatar
        aj1973
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Hi mmilegal 

        No, you don't need premium capacity to use Dataflows. Premium capacity is for AI and ML capabilities.

        To make it simple for you to understand, The Dataflow uses the Gateway to connect to your SQL server 2012, if it works then good. After you cennect to your source you will be using Power Query Online Platforme ( just like in your desktop) to ETL your data. After you prepare your data and refresh it  you can then call it in your desktop and build the report. after that you just publish it and you all be good to go.

        Let me know for more details