Forum Discussion

DaxNovice's avatar
DaxNovice
Frequent Visitor
6 years ago

Cannot Schedule refresh Azure SQL Database

Hi

I am currently getting the error message below when trying to refresh from the service (works fine on desktop):

 

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

(List of Queries, all from SQL Database)

Query contains unsupported function. Function name: Sql.Database

 

From the refresh history the error message is :

Data source error: Unable to refresh the model () because it references an unsupported data source

The purpose of the function is to append together mulitple databases & servers that have the same structure into one single table.  e.g. advanced editor below:

 

let
Source = #"DB & Server",
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Server", type text}, {"DB", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "pbig MP", each #"pbig MP"([DB], [Server])),
#"Expanded Query1" = Table.ExpandTableColumn(#"Invoked Custom Function", "pbig MP", {"Name", "Data", "Schema", "Item", "Kind"}, {"Name", "Data", "Schema", "Item", "Kind"})
in
#"Expanded Query1"

 

The Source "Db & Server" has two columns, DB & Server, and 4 rows with unique Db & server combinations.

 

Has anyone had this issue before & managed to solve?

 

advanced editor text of the function is:

 

(DB as text, Serv as text) as table =>
let
Source = Sql.Database(Serv, DB)
in
Source

Need Help

4 Replies

Replies have been turned off for this discussion
    • DaxNovice's avatar
      DaxNovice
      Frequent Visitor
      6 years ago

      HI Lin

       

      I am not sure that helps.  The source is SQL.Database, which is a supported source for data refresh.  This is also an Azure SQL database so there is no need for a gateway.

       

      I gather that this is probably due to the functions but there is no documentation that I can find to help with this.

       

      Refresh works find on the .pbix file

  • venal's avatar
    venal
    Memorable Member
    6 years ago

    DaxNovice 

    I tried to re-pro the issue with On-Prem SQL Server from my end by connecting to a DEPT table.

     

    let
    Source = Sql.Database("DESKTOP", "Venal"),
    dbo_DEPT = Source{[Schema="dbo",Item="DEPT"]}[Data]
    in
    dbo_DEPT

     

    Created a sample report and published.

    Tried to check with "refresh Now", it got completed.

    If the issue is persists, please share the error details.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more
    If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

    • DaxNovice's avatar
      DaxNovice
      Frequent Visitor
      6 years ago

      Hi venal 

       

      Thanks.  Yes, it works with static Sql.Database("Server name","Database name") but not with parameterized server & DB which is the issue.

       

      from this link here

       

      Note

      Power BI currently does not support parameterized data source definitions, also known as dynamic data sources. For example, you can't parameterize the data access function Sql.Database("SqlServer01", "AdventureWorks"). If your dataset relies on dynamic data sources, Power BI informs you that it detected unknown or unsupported data sources. You must replace the parameters in your data access functions with static values if you want Power BI to be able to identify and connect to the data sources. For more information, see Troubleshooting unsupported data source for refresh.

       

      But clicking that link gives you no further info on how to fix other than static server name & database which is not a viable option for this dataset.

       

      I understand this could be a bit of a niche problem but if anyone has had this and has a solution I would be most grateful

       

      Error message here if this helps:

       

      Something went wrong
      There was an error when processing the data in the dataset.
      Please try again later or contact support. If you contact support, please provide these details.
      Data source error: Unable to refresh the model (id=7923608) because it references an unsupported data source.
      Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
      Activity ID: ff09dc5c-42df-458a-97c7-7b0a060af6bc
      Request ID: 0e77e53b-7597-d328-b8dc-7363438cb002
      Time: 2019-11-13 06:08:02Z