Question on implementing SQL Views or a Power Query Dataset for use with reporting

[D_PBI]

Hi,

I'm looking to extract MS Dynamics 365 (D365) data into a Azure SQL database, using Data Export Services. From here, I shall de-normalise the the D365 tables. However, I have a question on whether I should do this de-normalisation using SQL Views or via a stand-alone Power Query dataset. The PBI reporting (the PQ and DAX sides) will connect to either the SQL View or the PQ Dataset.

My question is, can you think of any reason(s) why I should choose one of the following methods over the other:
1) SQL Views within a Azure SQL database to de-normalise the data.
2) A stand-alone Power Query Dataset to de-normalise the data.

It will be me who will de-normalise the data by creating the SQL Views or the PQ Dataset. Other report developers will simply call the SQL Views or connect to the PQ Dataset - so I'm not aware of any real security considerations that will be a factor in the sense that the report developers will not be able to connect directly the Azure SQL database but only via the SQL Views/PQ Dataset.

I'm seeing SQL Views and the PQ Dataset as both one level down from the Azure SQL database that is housing the migrated data. The volume of data isn't great so I expect a PQ dataset can handle it. I'm more familar now with PQ than SQL so it may be easier for me, although I will implement the de-normalisation using the right method (not what's convenient).

Can anyone think of reason why I should look to implement one option of the other?

Also, I have looked into the use of Dataflows. I understand these to be an ETL tool in the cloud, however as we are using Pro licenses here (not Premium capacity) I saw great limitations with Dataflows and therefore saw no use for them. Has anyone come to the same conclusion as me when using Dataflows with a Pro license?

Thanks.

[edhans]

If you can take advantage of Query Folding, which you should be able to for most transformations of SQL data, I prefer that. It is much easier to tweak the data on your end and tailor it to your solution. 

If there are transformations you need that will not fold (many text manipulations like Text.Start()) and it is critical and more than you want the gateway engine processing locally, then you'll need a SQL View. Even at that, I will fold as much as possible, then do the non-folding transformations locally or in the gateway vs setting up a view most of the time. 

The problem with Views are they crop up like weeds and there is often little documentation as to what they are for, or someone else changes the view to tweak for their needs, and then your report breaks.

[Mariusz]

Hi @D_PBI 

If you know how to leverage Query Folding in Power Query then you can just go for PQ.

https://docs.microsoft.com/en-us/power-bi/guidance/power-query-folding

I find working with PQ a lot easier and faster than SQL MS and you have everything in one place, but I guess it a question of preference.

If you decided already to go down the Azure SQL path, Dataflows would not be much of use, as essential both do the same thing.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn