Forum Discussion

heit0050's avatar
heit0050
Advocate I
8 years ago
Solved

Clone and Re-bind with on-prem SQL Server

 I am attempting to automate the "clone and rebind" procedure with a report that is connected via DirectQuery to an on-prem SQL server database. We are an ISV and each customer is in their own DB. Ev...
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago
    heit0050 wrote:

    Thanks for the reply, Eric! Thanks also for the super helpful sample code on getting the password encryption done to setup the gateway. I'm pretty sure I never would have figured that out on my own.

     

    I had kind of resigned myself to just having our staff create the new pbix file each time. At least the rest of the procress can be autotmated.  It just seems like they are so close to letting us 100% automate the system.  I'm more just not looking forward to the hundreds of existing clients that'll need to be setup for the first time. 

     

    But with the new pbit files it is actually pretty quick.  Maybe someday they'll let us go from pbit -> pbix via command line somehow.  I did create an "idea" for this if it is something else that others are interested in:

     

    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/32484925-powershell-automation-to-create-new-pbix-files-fro

     

    Thanks again for all your help!

    heit0050

    You only have to create the pbix file in DirectQuery for the very first time. If the customers have the same dataset and report, the pbix file can be reused. You can import the pbix file and update connection string once a new customer is on board. So maybe 100% automation can be done.

heit0050's avatar
heit0050
Advocate I
8 years ago

Thanks for the reply, Eric! Thanks also for the super helpful sample code on getting the password encryption done to setup the gateway. I'm pretty sure I never would have figured that out on my own.

 

I had kind of resigned myself to just having our staff create the new pbix file each time. At least the rest of the procress can be autotmated.  It just seems like they are so close to letting us 100% automate the system.  I'm more just not looking forward to the hundreds of existing clients that'll need to be setup for the first time. 

 

But with the new pbit files it is actually pretty quick.  Maybe someday they'll let us go from pbit -> pbix via command line somehow.  I did create an "idea" for this if it is something else that others are interested in:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/32484925-powershell-automation-to-create-new-pbix-files-fro

 

Thanks again for all your help!

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
8 years ago
heit0050 wrote:

Thanks for the reply, Eric! Thanks also for the super helpful sample code on getting the password encryption done to setup the gateway. I'm pretty sure I never would have figured that out on my own.

 

I had kind of resigned myself to just having our staff create the new pbix file each time. At least the rest of the procress can be autotmated.  It just seems like they are so close to letting us 100% automate the system.  I'm more just not looking forward to the hundreds of existing clients that'll need to be setup for the first time. 

 

But with the new pbit files it is actually pretty quick.  Maybe someday they'll let us go from pbit -> pbix via command line somehow.  I did create an "idea" for this if it is something else that others are interested in:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/32484925-powershell-automation-to-create-new-pbix-files-fro

 

Thanks again for all your help!

heit0050

You only have to create the pbix file in DirectQuery for the very first time. If the customers have the same dataset and report, the pbix file can be reused. You can import the pbix file and update connection string once a new customer is on board. So maybe 100% automation can be done.