Help designing architecture in Power BI, appending multiple fact tables

[mksaa]

Hi!

I have data sources, total of 13 SQL Server databases, that are identical but I would like to have them as one data model in Power BI Service. My challenge is, I don't know how it could be done efficiently enough and don't know where to even start.

All databases are already modeled as star schema. All ID columns that are used in relationships are globally unique.
For dimensions I can create SQL query where I use union to have results from all databases:
SELECT * FROM db1.dbo.dimension
UNION
SELECT * FROM db2.dbo.dimension
UNION
SELECT * FROM db3.dbo.dimension
UNION
... etc.

This is fast and can be done to all dimension.

The issue is the fact table in each database. The total of rows from all fact tables is currently 191 million rows. Averaging almost 15 million rows per database in a fact table. There is a datetime column available for incremental refresh (will reset 3 times in a year).

The refresh rate should be daily.

I'm not necessarily looking for a straight solution, but more like how should I approach this? I have tried to research if someone else has been in this situation and working with appending many big tables together. Because I have to append the tables together, can I leverage from incremental refresh? Full load of each fact table using dataflow doesn't take long, approx. 30 minutes and a few seconds after that with incremental refresh - but can I append them efficiently? Can incremental refresh be used to appended query?

I have a PPU license. Is this even possible and I'm wasting my time?

I appreciate all answers and help.

Kind regards,

[lbendlin]

Dataflows 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?

[mksaa]

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.

[mksaa]

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.