Add support for Azure SQL Database read-only replica for gen2 dataflows
Allow gen 2 dataflows to refresh data from a Azure SQL Database read-only replica (using [MultiSubnetFailover=True]).
Steps to reproduce:
- Create an Azure SQL Database on the Business Critical tier
- Create a dataflow with the M query code as below, replacing [DatabaseServer] and [DatabaseName] with the details of your Business Critical Azure SQL database.
let
Source = Sql.Database("[DatabaseServer]", "[DatabaseName]", [CommandTimeout = #duration(0, 2, 0, 0), Query = "SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS Updateability", MultiSubnetFailover = true]),
#"Transform columns" = Table.TransformColumnTypes(Source, {{"Updateability", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Updateability", null}})
in
#"Replace errors"
- Note that the query returns “READ_ONLY” in the PowerQuery preview
- Save and refresh the dataflow
- Connect to the dataflow in PBI Desktop. Note that the dataflow now returns “READ_WRITE”, indicating that it has refreshed using the primary instance and not the read only replica as would be expected.
The above behaviour occurs whether an on-premises data gateway is used or not.
Please add support for gen2 dataflows using a read-only replica irrespective of whether the gateway is used or not.
Recent ideas
Data Pipelines - Run only selected activities
For debugging and testing pipeline activities during development, allow us to select one or multiple activities and run only the selected pipeline activities. For example, I'm working on editing ...frithjof_v4 hours agoCommunity ChampionNew612Views11likes2CommentsSemantic model connection bindings should be in source control (Git)
Semantic model data source connection bindings should be source controlled. A semantic model can contain multiple data source references, each of which can be mapped to a separate Fabric data connec...frithjof_v12 hours agoCommunity ChampionNew16Views1like0CommentsBulk changing column names in Visualizations Pane
We often use raw/api column names or measures with a set nomenclature to be consistent and to keep track of them but we do not want to display these names in the visuals. Currently we have to change ...vishal14019715 hours agoFrequent VisitorNew6Views0likes0Comments