Forum Discussion

iBusinessBI's avatar
iBusinessBI
Kudo Collector
4 years ago
Solved

Incremental refresh with several Appended Dataflows

I have an SQL Server datasource (accounting system) which I need to combine with Excel data in Sharepoint (consolidated journal entries that are not inside the accounting system). I need to have thi...
  • iBusinessBI's avatar
    4 years ago

    I have found the solution! First I’ve created three queries from the new Dataflow connector.

    Then I’ve added the filtering with RangeStart and RangeEnd parameters in each query.

    I’ve checked and the query folding is taking place in all queries. Then I’ve disable loading into model for the three queries and created a forth query (FactTable) which Appends the three. Then I filtered this table again with the RangeStart and RangeEnd parameters (this filter is redundant but we need it, otherwise the model will not allow us to set incremental refresh policy). On this step the query folding is lost but it doesn’t matter since we already append the filtered data from the previous, folded queries. The last step is of course to set the incremental refresh policy and publish. This can also be implemented for different data sources, for example - SQL Server and Dataflow. Hope someone can also benefit from this solution.