Dataflows vs Datamart

Dataflows and datamarts serve different purposes, and the best use-case scenario depends on the specific needs of your organization.

Dataflows are designed to help automate the process of data preparation, transformation, and cleansing. They provide a simple way to build ETL processes in Power BI, enabling you to ingest data from multiple sources, apply transformations using Power Query, and store the processed data in a centralized location. Dataflows are particularly useful for building repeatable data preparation processes that can be reused across different reports and analyses.

Datamarts, on the other hand, are designed to provide a centralized repository for storing and managing data. They are typically used to consolidate data from multiple sources and create a single source of truth for reporting and analytics. Datamarts can be designed to support different levels of aggregation, providing a range of reporting options for different users and departments.

In your case, since you frequently do ad hoc reporting and add new data sources, dataflows may be a better choice. Dataflows are more flexible and allow for more agility in modifying data sources and building new queries. They can also be easily incorporated into new Power BI reports.

However, if you require a centralized repository for storing and managing data, and you have a defined set of data sources and requirements, a datamart may be a better choice. Datamarts offer greater control over the data, and can provide more consistent and reliable results.

Ultimately, the choice between using a datamart or a dataflow will depend on the specific needs and goals of your organization. It may be helpful to consult with a data architect or data engineer to help determine the best approach for your particular situation.