Forum Discussion

ANerat's avatar
ANerat
Regular Visitor
5 years ago
Solved

Iterate SQL query based on list of sources in another table

Hi,   I'm executing identical SELECT statements from different servers. I'm using the approach below. But as you can see it's a problem if number of servers go up.   My current solution for n num...
Need Help
  • KNP's avatar
    KNP
    5 years ago

    I did a little more digging (and testing). Looks like you might be running into this issue.

    https://aka.ms/dynamic-data-sources 

     

    The only way to get around this is to use parameters and as far as I know, you can't use dynamic parameters with a SQL data source. So you'd be back to square 1 with a solution that isn't dynamic having to define each source as a parameter. 

     

    You may be able to do something with dynamic SQL, unfortunately I don't have the time to try. To be honest, even if that worked you'd probably want to ask the question "should I?" for obvious reasons.

     

    It looks like the 'E' in your ETL may need to be external to Power BI.

     

KNP's avatar
KNP
Super User
5 years ago

Hi ANerat, 

 

See link to PBIX (very basic) example. Change the server list to valid server names for you.

I think you should be able to adapt this to suit your needs.

 

QueryMultipleSQLServers.pbix 

 

I hope this helps.

 

Regards,

Kim

  • ANerat's avatar
    ANerat
    Regular Visitor
    5 years ago

    Hi, your solution works as well but having the same problem, data sources are not visible as they are dynamic so I cannot use scheduled refresh. Do you know about this?

     

    Thanks. 

    • KNP's avatar
      KNP
      Super User
      5 years ago

      Are you able to provide any more detail about the query/output you're trying to achieve?

      Is any part of the server names consistent and known? 

      You may be able to do something with SQLCMD -L wrapped in a stored proc depending on your permissions. 

      • ANerat's avatar
        ANerat
        Regular Visitor
        5 years ago

        Yes, they are consistent and I easily adapted your solution changing the server names.

         

        The query works within PBI Desktop but when it's deployed on report server, data sources are not recognized. Because within PQ script, data sources are not explicitly defined, it's parameterized and source is not known until after it's executed and the string from servers table are read.