Forum Discussion
Desktop refresh vs gateway refresh on service
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.
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
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
Hi aj1973
I'm embarrassed to keep missing the point - I suspect I am a bit like a kid who belongs in the beginner class who is trying to ask sensible questions in the advanced class! That's probably fairly accurate, I'm afraid! I've read a fair bit of content about dataflow vs dataset and watched a number of the ' guy in a cube' videos but I've ended up feeling like my only chance to grasp it was to try some practical implementation that relates to my real-world requirements. Unfortunately, I think my 'in at the deep end' approach has, this time, been more sink than swim.
Experiment 1
I tried a test dataflow with two of the tables from my old dataset. To try to test some of what I knew I'd need, I included the tables used in Example1 of my original post but decided to try only one of the three pivots. When I tried to edit the dataflow (in what I assume is the Power Query Online Platform - it looks near-identical to Power Query in Desktop) to merge the 2 tables, I got the message 'Computed tables require Premium to refresh. To enable refresh, upgrade this workspace to Premium capacity, or remove this table.'Experiment 2
At that point, I considered that I was only supposed to use the Dataflow to pull the source data so I saved the Dataflow without any transformation steps then opened a fresh pbix in the desktop and used the dataflows connector in the Get Data dialog in the desktop. In Desktop, I recreated the steps to merge, filter and pivot the data. When I applied the changes, the refresh began and was obviously going to take a while. I timed the refresh as processing around 950 rows per minute. The data being processed measures rows in the millions so I abandoned that.Experiment 3
My last throw of the dice was to replicate the merge/pivot steps in the online editor of the dataflow then create a new pbix in the Desktop and refresh the data in the Desktop. I kind of suspected that was a waste of time and I wasn't terribly surprised to see that the structure imported matched the output of the pivot that I had saved in the Dataflow but there was no data, presumably because the Dataflow was not refreshed for the reason above.Do those three experiments make sense?
Cheers
mmilegal
It all makes sense, I like the way you described your 3 experiments.
From the first experiment I could tell that you don't have permission to use or store the dataflow in Azure Data Lake Storage https://docs.microsoft.com/en-us/power-query/dataflows/configuring-storage-and-compute-options-for-analytical-dataflows. That's why the dataflow didn't refresh at first. Bad news good news from your experiment is that your data source (SQL 2012 server) is accessible through your Gateway, that was the aim of all this experiment wasn't it?
So what is left for you to do is ask your Azure admin the permission to use the Data lake in order to save your dataflow. Once you have it then right after you build your dataflow in Power Query online and save the changes the service will ask you to refresh the dataflow, if it goes through then you will be good to go.
Let me know