Create Dataset with datasource programatically

[Anonymous]

Hi,

I’m currently working on a project which requires me to export the Dataset from MicroStrategy Reports and create the same dataset in the Power BI.

I've tables & columns of MicroStrategy reports. Now, I need to programmatically create the dataset for every report in Power BI.

Already explored solutions

REST API:
Created a PUSH Dataset, but it lacks some features like
1. Creating calculated formulas
2. Connect the Push Dataset to SQL Server or any other DB
3. Max 75 Relationships per dataset

Gateways & Dataflows:
Created Gateways & Dataflows but once the dataset is created, we can only edit it manually but not through API as its not a PUSH Dataset.

MicroStrategy Connector for Power BI:
It exports the reports metadata directly rather than the underlying dataset.

Is there any possibility (or in the premium version) where I can create a dataset with tables, columns (calculated columns & measures), and their relationships programmatically, which can be imported in the Local Power BI Instance and can be edited in the future?

I'm still a beginner to the Power BI platform, please correct me if  I'm wrong.

Thanks
D Seethend Reddy

[seethend]

Hi @Anonymous, @jithin,

Push dataset can only be used to stream live data, we cannot attach a data source and sync from it. This is why rest endpoints don't allow it.

But, if you would like to create measures and calculated columns on the already existing dataset programmatically then you can do it by tweaking your local power bi instance analytics server.

Thanks

Seethend

[lbendlin]

What you are doing is "fighting the API" 

But if you must - create a dummy dataset with the desired connector type and publish to your workspace (or store somewhere locally). Then you can use the REST API to upload datasets to the service, modify dataset connection parameters etc.  What you cannot do (I think) is to switch connection types on the fly, say, switching from SQL server connector to Oracle Connector.

[Anonymous]

Hi @lbendlin 

Thanks for the quick response.

But API doesn't allow me to modify the dataset which is created on Local instance.

{
  "error": {
    "code": "ItemNotFound",
    "message": "Dataset <dataset_id> is not Push API dataset."
  }
}

Also, it doesn't allow me to push a Datasource with a dataset. 

https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets/datasets_postdataset

The above endpoint documentation says, it takes data source property in the request, but when I add one it throws below error

{
  "error": {
    "code": "BadRequest",
    "message": "Bad Request",
    "details": [
      {
        "message": "The property 'datasource' does not exist on type 'Microsoft.PowerBI.ServiceContracts.Api.Dataset'. Make sure to only use property names that are defined by the type.",
        "target": "dataset"
      }
    ]
  }
}

 Is there any way I can link the data source to the custom dataset?

Thanks

Seethend

[lbendlin]

Correct, you can only do that in the service, not locally.  (of course if you are adventurous enough you could download the .pbix, then manipulate the meta data in the .pbix, and then upload it again.)

I'll play with my push dataset a bit to see if I can reproduce your errors.

[jithin]

Hi,

have you found any solution to this problem yet?

I am facing the same issue with push dataset - not able to include datasources in request.

[Anonymous]

Same. When trying to add a datasource in the request it blows up