Can’t save dataflow. One or more entities references a dynamic data store.

[Anonymous]

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.

I start with this query: Core_Customers

let

  Source = Sql.Databases(pServer),

  Navigation = Source{[Name = pDatabase]}[Data],

  #"Navigation 1" = Navigation{[Schema = "dbo", Item = "Core_Customers"]}[Data],

  #"Choose columns" = Table.SelectColumns(#"Navigation 1", {"Id", "Name"})

in

  #"Choose columns"

I turn this Query into a function: f_Core_Customers

let

  Source = (pServer as text, pDatabase as text) => let

  Source = Sql.Databases(pServer),

  Navigation = Source{[Name = pDatabase]}[Data],

  #"Navigation 1" = Navigation{[Schema = "dbo", Item = "Core_Customers"]}[Data],

  #"Choose columns" = Table.SelectColumns(#"Navigation 1", {"Id", "Name"})

in

  #"Choose columns"

in

  Source

I have a Query with info in which Server and Database each Tenant “lives” in.

Excemple:

Tenant                 – Server               – Database

1                             - Server1             - Database1

2                             - Server2              - Database2

3                             -Server1              - Database3

4                             -Server2              - Database4.

This goes on for all 1000 tenants…

I Reference this Query, and rename it to “Core_Customers All Tenants”

Then I add a column and call the M-function and expands the core_customers- data from each tenant like this:

let

  Source = Tenants,

  #"Invoked Custom Function" = Table.AddColumn(#"Source", "f_Core_Customers", each f_Core_Customers([Server], [Database])),

  #"Expanded f_Core_Customers" = Table.ExpandTableColumn(#"Invoked Custom Function", "f_Core_Customers", {"Id", "Name"}, {"Id", "Name"})

in

  Source

But this query fails in Data Flow when I try to save with this error message: “Can’t save dataflow. One or more entities references a dynamic data store.”

This is a complete showstopper for me! And as I said: This works perfect in Power Query in Power Bi Desktop!

Please! Can someone help me with this?

Regards from Norway

[v-qiuyu-msft]

Hi @Anonymous, 

I would suggest you create a support ticket to get help. 

Best Regards,
Qiuyun Yu