Ddataflow can't be saved - too many data?

[NovaBI]

Hi all,

I've created a pretty huge SQL Server db where we store historical SFDC data. As SAP BW is not able to perform the operations in a direct query mode, or even import mode, I had to build this workaround.

I've created a view that unions 3 years of data - each year has around 800MB-900MB as a single dataflow.

When I create a dataflow that is using the SQL view that catches all 3 years combined, it is not possible to save the dataflow.

First, I got the message that "another operation is running in the background" - I was thinking that the dataflow is perhaps still catching metadata? The preview in PowerQuery was created within seconds, not a problem. The view in SQL takes around 35 minutes to be created in SSMS e.g..

When i wait a couple of minutes, the error message changes from "operation is running in background" to "there is a newer version of the dataflow already stored". But nothing is in the workspace. Waiting another couple of minutes...the dataflow that could not be saved appears, but: Metadata is there (name of the view, connection e.g.), but no data is available.

If I split the data into seperate years, it works fine.

[lbendlin]

A dataflow is probably not the right vehicle for that data. Consider using a dataset with incremental refresh and partition bootstrapping.

[NovaBI]

I was thinking about using the ML part of the dataflow gen 2 - or at least use a gen2 with the possibility to add a data source (SQL Server/Azure SQL Server).