Azure SQL server connect to pbix file or Connect to Dataflow (which one Better and proper way?

[Navaneethan]

I have a source data as "View" in Azure Synapse Analytics. I can able to connect the view directly to pbix file and I can create new Dataflow in PBI Service using Azure Synapse Analytics view. But I want to understand which one is the best option and better way to avoid issues in future. Please help me understand.

Thanks in advance!

[SqlBobScot · Accepted solution]

If you start adding DataFlows for each table. Think carefully about how long it will take to refresh each table.  In which case look at incremental refresh. Each refresh will require compute power which is the limiting factor long before you run into any issues about storage (IHMO).

The archtecture I recommend to my customers is always to load data into DataFlows. Do any prep, transform in the those if you have to. Then load the data into the DataSets as you need it. Have a look at https://ssbipolar.com/dataflows-in-power-bi/ as a starter for 10.  Maybe - https://ssbipolar.com/2019/10/14/power-bite-dataflows-vs-datasets/.

[SqlBobScot]

Dataflows - load data into the Power BI service and then can be used to share the data within the Power BI service. Often used to supply data to many datasets, so load once share many times.  More details on Dataflows start here - https://ssbipolar.com/dataflows-in-power-bi/

Datasets - the data in loaded into that specific dataset.  Some clients then add a dataset for each report, which means the same data source (Azure Synapse) will be requested to pull data for each dataset.

Large companies IMHO - Dataflows supplying data to multiple datasets. Smaller companies - Datasets might be suitable, depends on the number of datasets.

[Navaneethan]

Thanks for your clarification and support. I have another question. If we start creating Dataflows using each table from synapse it won't affect capacity of the server?. From loading capacity point of view, connect to dataset or connect to Dataflow which one will be better?

[SqlBobScot]

Ok, the capacity of Power BI service.
Power BI Pro licence if I have read the documentation correctly (https://learn.microsoft.com/en-us/power-bi/admin/service-admin-manage-your-data-storage-in-power-bi). You can only store up to 10GB in total.

Power BI premium limit is 100TB in total.

Connect using DataFlow or DataSet. I always recommend starting with DataFlows; this gets the data into the Power BI Service. Then use the DataFlows as a data source for the DataSets. Load once into DataFlows, then reuse the DataFlows to feed multiple DataSets. Otherwise, many data sets could import the same data multiple times from a single source. I have seen that pattern many times, and IMHO, it's not a good pattern for an enterprise solution.

Summary DataFlows to load the data once and then reuse the DataFlows as a DataSource in the Power BI service.