Hi folks, to date most of my Power BI reports have been built using data from NAV, Dynamics or Business Central where i created a series of PBI Dataflows to extract and transform which i then used to build 1 Dataset which services all of my finance related PBI reports.
Now I am going to be building PBI reports where the source data is held in SQL so I am wondering if my previous process is still the correct approach or should i avoid using Dataflows and instead create SQL views and Partitions?
Any real life experience and advice would be appreciated, thanks
Hi @BCS. If possible, it’s always a good idea to move your transformations as far upstream as possible. If you can create SQL views, do it, and let the database do what it’s designed to do (one of its major purposes, at least) which is to provide an interface to your data in the shape that you want it.
Greg
Would you even bother with Dataflows then? In this situation I have the ability to create views etc. in SQL so i can do that so would there be any sense in also pulling my data into dataflows or is this another layer of refreshing that i don’t need? I like to create one dataset which feeds all my reports, finance and non-finance related.
As with most things in Power BI, “it depends” (I know, I know … consultant’s answer). My first thought is always to keep the “path” between the source data and the report as short as possible, so I’d probably not go for Dataflows unless there were compelling reasons to get the data into the Power BI Service (reuse by existing dataflows/datasets? established refresh preferences? other?). Ultimately, the best decision though will be specific to your situation and will depend on your environment, organizational practices/restrictions, and access to development resources.