Forum Discussion
Desktop refresh vs gateway refresh on service
Community ChampionIt 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
Hi aj1973
I think I've managed to achieve the result but in a slightly different way - I might have dodged the issue in a manner that almost feels like cheating!
The bit about access to ADLS has kind of thrown me as I checked with the one person in our organisation likely to know about that and he doesn't believe we have a dedicated subscription for that.
However, it had occurred to me that everything else was working according to your suggestions and I could make the dataflow work so long as I didn't try to use merged or computed tables in the Power Query Online Platform. All the other fancy stuff like the pivots worked just fine. My 'cheat' solution - and I appreciate I'm fortunate that it was a solution available to me and not every user will be so lucky - is based on the fact that it was possible for me to use the SQL database from which I am pulling all my data. It was a very quick and simple process to create views in SQL to perform the limited actions that I might otherwise have done with a merge in PQOP. So, in case it helps anyone else with a similar challenge, the structure of my solution is something like this:
1. create views in SQL Server with the smallest amount of joins possible to allow filtering for further processing. That really was just limited to examples like pulling in a 'type' column from table B to apply to rows in table A to allow me to filter table A by type.
2. create a dataflow in a workspace to pull the data from SQL Server tables and views as per step 1. The most significant processing in PQOP is the pivots described as Example1 in my original post.
3. create a dataset in Desktop and use the dataflow connector in the Get Data dialog to pull data for the dataset from the dataflow created in step 2.
4. the most significant processing in the dataset is the DAX SUMMARIZE/FILTER processing outlined as Example2 in my original post.
I think I now have the entirety of the data required built into a single dataset that should be able to support the reporting requirements. The dataflow has refreshed in the cloud several times and, although it did fall over once, it seems to be refreshing in about 22 minutes. I've published the dataset and, scheduling a refresh for one hour after the scheduled refresh time of the dataflow, it refreshed last night in around 16 minutes. Size of the pbix is 199Mb. Not sure if all of those statistics necessarily are too meaningful without lots of other info not included here, but they are at least indicative of one user's experience.
I suspect much of my solution still bears the hallmarks of the enthusiastic amateur and is likely to fall short of best practice in some areas but I'm sure that will be the reality for many using Power BI in small organisations.
A very big thank you aj1973 - I know for certain I would not have worked out all of this on my own and I'm sure I've had a learning experience on the journey too. I very much appreciate both the knowledge you possess and your generosity with your time in sharing it.
Cheers
mmilegal