Optimizing Power BI report model with multiple tables pulled from different schemas

[user_95]

Hello all,

I have a new report requirement that requires pulling in multiple database tables with similar structure, but from different schemas. The data is for different customers and each of them have their own schema along with their specific tables. The requirement here is to create a consolidated report that shows data for all customers.

I need to pull around 3 tables with similar structure from each customer schema into a single report. We have around 10 customers and that would mean around 30 tables in total that would need to be pulled into Power BI and this count would most probably increase over time. This could make the PBI data model quite messy. Each of the tables might have around 25-50K records. The database is Azure SQL server.

Please let me know if you have any suggestions to optimize this model or a better way to fetch all this data into a single report.

Customer 1 - Table A, Table and Table C 

Customer 2 - Table A, Table and Table C

Customer 3 - Table A, Table and Table C and so on

[MFelix · Accepted solution]

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.