Forum Discussion
Datamarts Preview Appending data
Hello Forum,
I'm trying to use Datamarts as a master database for reporting purposes. 100GB is plenty.
This requires me to source data from a dataflow which is fine.
However I would like the ability to have change the source data for the data flow from month to month and have the complete set of data stored in the datamart. What I'm finding is that any refresh removes everything that was there.
Is this possible or do I need to build an ELT solution separately on SQL Server and use this as a Power BI source?
Many thanks,
Michael
You need a separate ELT solution. No Power BI solutions are a proper datawarehouse - not meant be. Azure SQL, or other tools are meant for that. All Power Bi tools (dataflows/datasets/datamarts) wipe all data on refresh (or keep some historical data in an incremental refresh scenario, but that is still not meant to be a datawarehouse for storing snapshots, and those historical partitions can be purged on a schema change, losing all data).
6 Replies
You need a separate ELT solution. No Power BI solutions are a proper datawarehouse - not meant be. Azure SQL, or other tools are meant for that. All Power Bi tools (dataflows/datasets/datamarts) wipe all data on refresh (or keep some historical data in an incremental refresh scenario, but that is still not meant to be a datawarehouse for storing snapshots, and those historical partitions can be purged on a schema change, losing all data).
You can create a dataflow outside of your datamart, set it up with the Bring Your Own Storage option so that you have access to the underlying datalake, then set it to a monthly scheduled refresh. This will generate monthly snapshots:
In your datamart you can then ingest from the ADLS Gen 2 bucket where you saved the output of the initial dataflow, and add the files' timestamp as your snapshot date.
Thank you otravers.
In this model I think we'd have an outside dataflow reading a folder from a growing set of source files. Over time I am concerned over the management of these source files and growing costs of reading all of these every refresh unless the data flow knows to only read what has changed since the last refresh.
