Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to Push dataset into Power BI service using REST API. But unable to associate the datsource properties for the same.
Request:
{
"name": "PBI_Transaction_V1",
"defaultMode": "Push",
"tables": [
{
"name": "COST_CENTER_DIM",
"columns": [
{
"name": "COST_CENTER",
"dataType": "string"
},
{
"name": "DEPARTMENT_SKEY",
"dataType": "Int64"
}
]
},
{
"name": "TRANSACTION_LINE_F",
"columns": [
{
"name": "NET_AMOUNT",
"dataType": "Double"
},
{
"name": "ITEM_COUNT",
"dataType": "Int64"
},
{
"name": "COGS",
"dataType": "Double"
},
{
"name": "DEPARTMENT_SKEY",
"dataType": "Int64"
}
],
"measures": [
{ "name": "SALE_UNITS", "expression": "SUM(TRANSACTION_LINE_F[ITEM_COUNT])*-1", "formatString": "$#,##0" },
{ "name": "TTL_SALE_AMT", "expression": "(SUM(TRANSACTION_LINE_F[NET_AMOUNT])*SUM(TRANSACTION_LINE_F[ITEM_COUNT]))*-1", "formatString": "#,##0" }
]
}
],
"relationships": [
{
"name": "DEPARTMENT",
"crossFilteringBehavior": "OneDirection",
"fromTable": "TRANSACTION_LINE_F",
"fromColumn": "DEPARTMENT_SKEY",
"toTable": "COST_CENTER_DIM",
"toColumn": "DEPARTMENT_SKEY"
}
],
"datasources": [
{
"datasourceType": "Oracle",
"connectionString": "data source=\"(description=(address=(protocol=tcp)(host=myhost)(port=1521))(connect_data=(service_name=PBIcloud)))\";persist security info=True",
"connectionDetails": {
"server": "(description=(address=(protocol=tcp)(host=myhost)(port=1521))(connect_data=(service_name=PBIcloud)))"
},
"datasourceId": "d30256f3-8b6d-4944-840d-bf8d56744",
"gatewayId": "12346e06-2777-3df9-b071-3dd9c8df5586"
}
]
}
Response:
Any help would be appriciated.
Thanks,
Sandhya
Note: This API supports only Push datasets.
Meaning not Oracle.
Thank you @lbendlin for the confirmation.
Is there any way we could create a Oracle(any database) Dataset programatically?
Regards,
Sandhya
Sure, but why? And what does that have to do with PowerBI?
The design requires to create a dataset connecting to Oracle database programatically or through API and create reports in power BI.
Thanks,
Sandhya
Does the design specifically state that it has to be a Push dataset? Push datasets do not normally get fed by stationary database sources. They get fed by PubNub or other push APIs.
No it need not be push dataset specifically.
All we need to achieve is to create a report and dataset in power BI service pointing to a Oracle(any datasource) programmatically. Considering the example posted above, would like to create a dataset pointing to Oracle database and create a Power bi report using the fields from the same and share it.
Any help would be appreciated.
Thanks,
Sandhya
Then you need to use different API calls.
I would recommend https://docs.microsoft.com/en-us/rest/api/power-bi/imports/postimport with a sample .pbix file. Once uploaded to the workspace you can then modify the datasources as needed with https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasources
The above suggested approach would work if we have the dataset/report built in pbix and then we would import and change the datasource as required.
But our design required to create a datamodel/report pointing to a datasource programmatically.
For example : We have an existing report pointing to Oracle in Tableau, we would want to create an equivalent datamodel(dataset) in power BI programmatically and publish to the server for end users to consume it.
Note: The Datamodel informatica is extracted from source reporting metadata in the required format using Java/Python.
What you are doing is called "Fighting the API". Rethink your design, or push back on the ask.
Hello guys, i came here with a similar problem and after a lot of testing the question remains: is it possible to create a dataset (from Azure SQL Server in my case) programatically without creating the .pbix file in the PBI dektop tool? Thank you.
User | Count |
---|---|
15 | |
4 | |
2 | |
1 | |
1 |