Forum Discussion
Optimizing Power BI report model with multiple tables pulled from different schemas
Hi user_95 ,
Since the tables have similar configuration I suggest that you make an append of the tables into a single query that way you have consistency among the data and just need to get an additional column to get the customer name on each line.
The fact that tables have 50K records should not be an issue.
You can do this making the query for all the tables and then disabling them and do a single query with the append, or do it in a single query making the join trough different steps or a SQL code to union them.
Be aware that when I say a single table it may mean 4/5 tables but each one of those combining the similar tables of Azure for each customer into a single one.
So if you have a product table for each customer you combine it in a single table etc.
The only thing you need to be carefull is to have composite keys for any common dimensions that you may have that combine the customer key and the table key column that will give you a unique value that you can then use to make a star schema.
Hi user_95 ,
Since the tables have similar configuration I suggest that you make an append of the tables into a single query that way you have consistency among the data and just need to get an additional column to get the customer name on each line.
The fact that tables have 50K records should not be an issue.
You can do this making the query for all the tables and then disabling them and do a single query with the append, or do it in a single query making the join trough different steps or a SQL code to union them.
Be aware that when I say a single table it may mean 4/5 tables but each one of those combining the similar tables of Azure for each customer into a single one.
So if you have a product table for each customer you combine it in a single table etc.
The only thing you need to be carefull is to have composite keys for any common dimensions that you may have that combine the customer key and the table key column that will give you a unique value that you can then use to make a star schema.