I have a question regarding the best way to connect to all databases on a dedicated server with the same database schema in a PowerBI application.
Let me explain: I am developing software, and the backend part is the same for all my clients. However, each client has their own database (with an identical database schema). All databases are stored on the same dedicated server. My goal is to perform reporting with Power BI by executing the same queries across all databases on the server. However, I am struggling to find a way to execute the same query on all databases on the server without specifying the database name. I would like to avoid having to specify the name of each database, as this needs to be automated, given that there are around a hundred databases on the server.
I have searched online but haven't found documentation that seems to fit my needs. Since I am a beginner with Power BI, I'm turning to the community to get some guidance.
I am using SQL Server 2016/2019.
Thanks for your help!
I attempted to create a dynamic connection in Power BI to fetch data from multiple SQL Server databases sharing an identical schema on a dedicated server. My expectation was to automate the process, eliminating the need to specify each database manually.
To achieve this, I explored various Power BI documentation. But unfortunately, I couldn't find a solution that precisely fit my requirements. I experimented with different connection configurations and explored Power BI functionalities related to database connections
One option is to do this in SQL with Views that UNION ALL across all the databases. Then in Power BI you are only making the one query to that View.