Clone and Re-bind with on-prem SQL Server

[heit0050]

 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. Everytime we on-board a new client, the goal would be to:

1) Setup a new DataSource on the gateway (I've successfully completed this step)

2) Setup a new Dataset to connected to the above DataSource (this is where I am stuck)

3) Perhaps use the BindToGateway to connect these two

4) Use the CloneReport call to duplicate our report template and connect it to the new Dataset (I've successfully been able to do this if I manually create the dataset)

It seems to me that ther is a missing enum value for the "DefaultMode" property on the Dataset object. These are the options today:

'AsAzure', 'AsOnPrem', 'Push', 'Streaming', 'PushStreaming'

I feel like we need a "SqlOnPrem" to do what I want? It seems clear that it can be done - since a dataset gets created during the import of a pbix file that connects to the gateway and then to the on-prem SQL server.

[sivanagamahesh]

I am endeavoring to mechanize the "clone and rebind" technique with a report that is associated by means of DirectQuery to an on-prem SQL server database. We are an ISV and every client is in their own particular DB. Everytime we on-load up another customer, the objective would be to:

1) Setup another DataSource on the entryway (I've effectively finished this progression)

2) Setup another Dataset to associated with the above DataSource (this is the place I am trapped)

3) Perhaps utilize the BindToGateway to associate these two

4) Use the CloneReport call to copy our report layout and associate it to the new Dataset (I've effectively possessed the capacity to do this on the off chance that I physically make the dataset)

I can't help suspecting that there is a missing enum esteem for the "DefaultMode" property on the Dataset protest. These are the alternatives today:

'AsAzure', 'AsOnPrem', 'Push', 'Gushing', 'PushStreaming'

I feel like we require a "SqlOnPrem" to do what I need? It appears to be certain that it should be possible - since a dataset gets made amid the import of a pbix document that associates with the entryway and after that to the on-prem SQL server.

[Eric_Zhang]

---

@heit0050 wrote:

 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. Everytime we on-board a new client, the goal would be to:

1) Setup a new DataSource on the gateway (I've successfully completed this step)

2) Setup a new Dataset to connected to the above DataSource (this is where I am stuck)

3) Perhaps use the BindToGateway to connect these two

4) Use the CloneReport call to duplicate our report template and connect it to the new Dataset (I've successfully been able to do this if I manually create the dataset)

It seems to me that ther is a missing enum value for the "DefaultMode" property on the Dataset object. These are the options today:

'AsAzure', 'AsOnPrem', 'Push', 'Streaming', 'PushStreaming'

I feel like we need a "SqlOnPrem" to do what I want? It seems clear that it can be done - since a dataset gets created during the import of a pbix file that connects to the gateway and then to the on-prem SQL server.

---

@heit0050

As far as I know, there's no such 'SqlOnP' item. As to your requirement, below approach can work

1. Create a pbix file in DirectQuery mode in Power BI Desktop.
2. Everytime we on-board a new client, import the pbix file to your Power BI Service workspace as a new report, see API demo or SDK demo.
3. Update the connection string of that dataset with the new cilent's.
4. Setup a new DataSource on the gateway for that new imported dataset.
5. Bind the datasource to the gateway seems not necessary. Based on my test, once the step 4 finished, the dataset is binded to the specific gateway automatically.

[heit0050]

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...

Thanks again for all your help!

[Eric_Zhang · Accepted solution]

---

@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...

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]

That did the trick! Thanks again for all your help.

For anyone else, here are the rough steps using the C# PowerBI V2 Rest wrapper API:
1) Authenticate (AcquireTokenAsync)

2) Get your gateway (GetGatewaysAsync)

3) Create a datasource under your gateway (CreateDatasourceAsync)

4) Upload your report that is using DirectQuery to your onPrem SQL Server (PostImportWithFileAsyncInGroup)

5) Update the connection string on that new report (SetAllDatasetConnectionsInGroupAsync)