Fix a Mess - Datasets, Dataflows, Lots of Duplication

[Xaraja]

I'm beginning a huge project to bring order to a massively chaotic Power BI ecosystem where there's a lot of duplication of datasets. There are also a few dataflows, but I'm not sure from what I'm reading that these add much value in this situation. The plan is to provide one or a few golden datasets for the report builders to work from, where in the past they have just accessed the SQL server directly. 

However, I do need to consider future proofing the solution for when the source of truth moves to Postgres (Infor SX Cloud) and we don't have that SQL server any more. Would building dataflows provide a more robust back end so that reports and datasets wouldn't have to be rebuilt when that happens (1-3 years from now)?

I am also not understanding how the memory works for dataflows and datasets. If there's a dataflow with table x in it, and each report creator makes their own dataset based on that dataflow, am I looking at the Power BI service needing to have each of those datasets in memory to display those reports? Or is there some memory benefit to using dataflows? Our biggest capacity issue right now is too many duplicative datasets that are constantly evicting each other (report builders keep creating new datasets with the same tables).

[Greg_Deckler · Accepted solution]

@Xaraja Dataflows simply allow you to reuse queries across multiple datasets, no memory benefit. To future proof, just make sure to use query parameters for your source and database names and to create a single query that is referenced by all other queries. This reference query would use the query parameters and would be set to disable "Enable load" and all it would do is connect to the server, so just a single Source line and no other steps.