Forum Discussion
Help designing architecture in Power BI, appending multiple fact tables
Super UserDataflows with incremental refresh is a meaningful approach in your scenario. You can append these easily in Power Query, or you can even choose to UNION them in Power BI (if you are on a large enough capacity - not PPU).
Have you tried to UNION your fact tables on SQL?
Thank you lbendlin for the answer. How should I setup the dataflows? Setup a dataflow for each source, meaning I have 13 dataflows with incremental refresh and then setup a new dataflow to another workspace where I append these dataflows as one with incremental refresh? Or should I create just one dataflow where all these 13 source are appended together and use the incremental refresh to the appended query?
I have no experience with incremental refresh so I'm not sure how it's or if it's evaluated by the source in different scenarios.
I cannot UNION these tables on SQL, since I only have read access to each database. Also, there are some transmissions I have to do in Power Query (all steps can be evaluated by source). But if I UNION them with SQL query, the steps in Power Query won't be evaluated by source anymore. Or it would be a horrendous SQL if I have the steps on the query and union.
- lbendlin
I cannot UNION these tables on SQL, since I only have read access to each database.Not sure why that would be a limitation?
Or it would be a horrendous SQL if I have the steps on the query and union.Let the SQL query engine decide if it is horrendous. General guidance is to do your transforms as far upstream as possible but not farther,
Let your data source natural refresh cadence define your dataflows structure. Only refresh each source when required. Appending dataflows in Power Query is usually fast if you refrain from additional transforms.
I'm not sure how would I take advantage of incremental refresh if I have all fact tables as one query in Power Query using UNION in native SQL query.
Thank lbendlin, I got much information from these. I think I need to atleast try to UNION fact tables with SQL and Dataflows with append as a second option. And see what the query plans and data source queries are.