Forum Discussion

mksaa's avatar
mksaa
Frequent Visitor
2 years ago

Help designing architecture in Power BI, appending multiple fact tables

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 do...
dataflow
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago
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's avatar
mksaa
Frequent Visitor
2 years ago

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.