This must be a Critical weakness/Bug/Problem in Data flow in Power Bi Service!
I have a multi-tenant, multi database Saas-solution running on 5 on-premise SqlServers with 200 tenant databases on each server, with the same data model (same database scheme) in each database.
The task is: for each table in the database-model, loop through all the databases on each server, and append all the data for the same table for each tenant into one Power BI table, which in the end will hold all data, for all tenants in the same table.
Repeat this for all tables in the database schema, and we will in the end have 1 common Power Bi Data model for all the tenants!
I started to do this in Power BI Desktop, and it worked perfect! For the first time in 15 years we could do reporting on top of all our 1000 tenants!
The final step was to move this into a Data Flow in the Power Bi Service. I have other Data Flows from other data sources that I want to combine with this Data Flow. I have a Premium Workspace. After moving one and one Query from Desktop to the Data Flow, it suddenly started to fail when we tried to save the entities in the dataflow with this error message: “Can’t save dataflow. One or more entities references a dynamic data store”.
After much investigation we found that it was this M-function we use that fails. I have tried to simply the code to show the code that fails:
This example is for one table in the database. I have 2 parameters, pServer and pDatabase.