Creating a dataset with Power BI's REST API and associate it with a custom connector

[denodolabs]

Hi,

I would like to know if it is currently possible to create a dataset associated with a data source which has "Extension" as "datasourceType" with the Power BI's REST API (it is a certified custom connector in Power BI).

Using the REST API I have been able to create a data source, establishing my custom connector as the data source type. However, when I try to create a dataset I get an error.

Request body:
{
    "name": "ds_TestingREST02",
    "defaultMode": "PushStreaming",
    "datasources": [
        {
            "datasourceId": "fef544ac-20b6-46ea-8a5a-1a7cc1c37217",
            "datasourceType" : "Extension",
            "gatewayId": "52bbd773-b6c2-4b86-a6f0-e8a15551bc03"
        }
    ],
    "tables": []
}

Error:
{
"error": {
"code": "InvalidRequest",
"message": "TOM deserialization failed for database sobe_wowvirtualserver|5f76fabc-8536-4100-83fb-a9e969fcb364, Exception: <pi>Unrecognized JSON property: datasources. Check path 'datasources', line 1, position 72.</pi>"
}
}

I have also seen in some answers that DirectQuery through REST API is not supported. Does this mean that even though I am able to create a dataset associated with my data source, could I not take advantage of this mode?

Thank you.

[v-kelly-msft]

Hi @denodolabs ,

Would you pls check whether the source body is right?

"datasourceId": "fef544ac-20b6-46ea-8a5a-1a7cc1c37217",
            "datasourceType" : "Extension",
            "gatewayId": "52bbd773-b6c2-4b86-a6f0-e8a15551bc03"

Here is a related reference.

I've checked several docs,and it seems that there're tricks about using API in direct query.

Below is the reference.

https://www.progress.com/tutorials/odbc/directquery-your-custom-rest-api-in-power-bi

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[denodolabs]

Hi Kelly,

According to the documentation (https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets/datasets_postdataset#datasource) you can push a dataset associated with data sources adding the datasources property in the request body. This property is a datasource array (https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets/datasets_postdataset#datasource). A Power BI data source has several properties, including datasourceId, datasourceType and gatewayId. I have checked that the Id values are correct. The datasourceType is "Extension" because this seems to be the appropriate value for data sources referencing custom connectors.

Anyway, I have performed tests like:

{
    "name": "ds_TestingREST02",
    "defaultMode": "PushStreaming",
    "datasources": [
        {
            "datasourceId": "fef544ac-20b6-46ea-8a5a-1a7cc1c37217",
            "gatewayId": "52bbd773-b6c2-4b86-a6f0-e8a15551bc03"
        }
    ],
    "tables": []
}

or

{
    "name": "ds_TestingREST02",
    "defaultMode": "PushStreaming",
    "datasources": [
        {
            "datasourceId": "fef544ac-20b6-46ea-8a5a-1a7cc1c37217"
        }
    ],
    "tables": []
}

In these scenarios I get the same error:

{
"error": {
"code": "InvalidRequest",
"message": "TOM deserialization failed for database sobe_wowvirtualserver|dd13a16e-b75b-4cf9-87a4-913253295b7c, Exception: <pi>Unrecognized JSON property: datasources. Check path 'datasources', line 1, position 72.</pi>"
}
}

For all these reasons, I have doubts about whether it is possible to create a dataset associated with a data source with "Extension" type (a data source that points to a local server through the Gateway using a certified custom connector).

[v-kelly-msft]

Hi @denodolabs ，

I have checked the docs about API and I found that you cant define the datasource in API which is not allowed,in API,you can only define table and its name,in table,you can define columns,measures,name and rows.So better remove the definition about datasource and check whether it works.

Here is the reference:  https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets/datasets_postdataset#datasource

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[denodolabs]

---

I have checked the docs about API and I found that you cant define the datasource in API which is not allowed

---

Does this mean that it is not possible to create a dataset with an associated datasource in order to get the data through it? Note that I don't want to define the datasource while defining the dataset, I just want to add the reference to my datasource (previously created using the API) to have a dataset with data coming from a data source through the Gateway.

According to the documentation, when you post a dataset you can add a "datasources" property in the request body (https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets/datasets_postdataset#request-body). This is supossed to allow you to set the datasources associated with this dataset. So this is what I was trying to use to create a dataset, using the API, from the data I get through a certified custom connector in my Gateway. Maybe this is not the proper way to specify what I need. I will be grateful for any help you can provide.

Moreover, I've tried creating datasets without datasources using the API and it works but that doesn't cover my scenario. I need the data to come from a data source.

Thank you.

[denodolabs]

I would also like to mention that one of the things that makes me doubt about the API capacity for my scenario is the defaultMode property and its possible values. According to the documentation (https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets/datasets_postdataset#datasetmode), the values are AsAzure, AsOnPrem, Push, PushStreaming and Streaming. At first glance, it might appear that what I need is an AsOnPrem dataset but the description says "A dataset with a live connection to On-premise Analysis Service". Since I am not working with Analysis Service, I understand that it would not work for me.

Test:
{
    "name": "datasettest",
    "defaultMode": "AsOnPrem",
    "datasources": [
        {
            "connectionDetails": "{\"extensionDataSourceKind\":\"DenodoForPowerBI\",\"extensionDataSourcePath\":\"{\\\"DSN\\\":\\\"DenodoODBC\\\"}\"}",
            "gatewayId": "52bbd773-b6c2-4b86-a6f0-e8a15551bc03"
        }
    ],
    "tables": []
}

Error:
{
"error": {
    "code": "FailedToDeserializeDatasetError",
    "pbi.error": {
        "code": "FailedToDeserializeDatasetError",
        "parameters": {},
        "details": [],
        "exceptionCulprit": 1
    }
}
}

Therefore, among the other options, the Push mode seems to be the most suitable. However, setting this type, can a dataset be connected through the gateway in order to get its data from a custom connector?

In the example above, if I set "Push" instead of "AsOnPrem" I get the same error.

Thank you.