Need to fix issue in Create Semantic Layer by providing sql query

[UmeshGoti]

Hello community,

Currently i am creating semantic layer based on SQL query and database details i have.
for this i am using below API 
POST : https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/semanticModels
RequestBody : 

{

    "displayName": "SalesModel1",

    "description": "SalesModel1",

    "definition": {

        "parts": [

            {

                "path": "model.bim",

                    "payload": "<base64>",

                "payloadType": "InlineBase64"

            },

            {

                "path": "definition.pbism",

                "payload": "<base64>",

                "payloadType": "InlineBase64"

            },

            {

                "path": ".platform",

                "payload": "<base64>",

                "payloadType": "InlineBase64"

            }

        ]

    }

}

For "model.bim" part i am converting below JSON into base64 
{
"name": "Cognos Semantic Model",
"compatibilityLevel": 1550,
"model": {
"culture": "en-US",
"dataAccessOptions": {
"legacyRedirects": true,
"returnErrorValuesAsNull": true
},
"defaultPowerBIDataSourceVersion": "powerBI_V3",
"sourceQueryCulture": "en-US",
"tables": [
{
"name": "product_category",
"partitions": [
{
"name": "Applications",
"mode": "directQuery",
"source": {
"type": "query",
"query": "SELECT id, name, quantity FROM product_category",
"dataSource": "SqlDataSource"
}
}
],
"annotations": [
{
"name": "PBI_ResultType",
"value": "Table"
}
]
}
],
"dataSources": [
{
"name": "SqlDataSource",
"type": "structured",
"connectionDetails": {
"server": "<server-endpoint>",
"database": "<databse_name>",
"authentication": {
"type": "Basic",
"username": "<username>",
"password": "<password>"
}
}

}
],
"cultures": [
{
"name": "en-US",
"linguisticMetadata": {
"content": {
"Version": "1.0.0",
"Language": "en-US"
},
"contentType": "json"
}
}
],
"annotations": [
{
"name": "PBI_QueryOrder",
"value": "[\"Applications\"]"
},
{
"name": "__PBI_TimeIntelligenceEnabled",
"value": "1"
},
{
"name": "PBIDesktopVersion",
"value": "2.137.1102.0 (24.10)"
},
{
"name": "PBI_ProTooling",
"value": "[\"DevMode\"]"
},
{
"name": "TabularEditor_SerializeOptions",
"value": "{\"IgnoreInferredObjects\":true,\"IgnoreInferredProperties\":true,\"IgnoreTimestamps\":true,\"SplitMultilineStrings\":true,\"PrefixFilenames\":false,\"LocalTranslations\":false,\"LocalPerspectives\":false,\"LocalRelationships\":false,\"Levels\":[\"Data Sources\",\"Shared Expressions\",\"Perspectives\",\"Relationships\",\"Roles\",\"Tables\",\"Tables/Columns\",\"Tables/Hierarchies\",\"Tables/Measures\",\"Tables/Partitions\",\"Tables/Calculation Items\",\"Translations\"]}"
},
{
"name": "__TEdtr",
"value": "1"
}
]
}
}


but i am getting below error

{

    "status": "Failed",

    "createdTimeUtc": "2025-02-10T07:38:03.1846582",

    "lastUpdatedTimeUtc": "2025-02-10T07:38:20.4501583",

    "percentComplete": null,

    "error": {

        "errorCode": "Dataset_Import_FailedToImportDataset",

        "message": "Dataset Workload failed to import the dataset with dataset id 00000000-0000-0000-0000-000000000000. Failed to save modifications to the server. Error returned: 'Power BI does not support query partition sources that reference structured data sources. In table '<oii>product_category</oii>', the partition '<oii>Applications</oii>' uses a query partition source that references the structured data source 'SqlDataSource'. To fix this issue, either replace the query partition source with an M partition source or modify the query partition source to reference a provider data source instead of the structured data source.\r\n'."

    }

}

[UmeshGoti]

Thanks @v-jianpeng-msft  your solution works for normal report
but my requirement is to have my report connected with azure SQL Database and it's query(which should be created through above way you mentioned ) 
if possible can you share some hints with example if possible on that 
will be very helpful to me

Thanks Again !!

[UmeshGoti]

@nilendraFabric  very very Thanks for your quick reply

as i am using below dataSources properties and type as 'provider'


"dataSources": [
{
"name": "SqlDataSource",
"type": "provider",
"connectionDetails": {
"server": "iptools-demoserver-dev-001.database.windows.net",
"database": "mct_Sales_Raw",
"authentication": {
"type": "Basic",
"username": "techmUsercd",
"password": "next@789"
}
}
}
],


but i am getting below issue 
Dataset Workload failed to import the dataset with dataset id 00000000-0000-0000-0000-000000000000. Couldn't open 'model.bim'.\nUnrecognized JSON property: connectionDetails. Check path 'model.dataSources[0].connectionDetails', line 38, position 28.

may be i am missing something in syntax standard
if possible can you please provide one basic working sample
then i can proceed further
this will me help me a lot !!
Thanks !!

[v-jianpeng-msft · Accepted solution]

Hi, @UmeshGoti 

Based on your description, I made a working case.
First, I created a basic semantic model in Power BI desktop:

Save it as a PBIP file:

Use the following template to make a payload:

SemanticModel definition - Microsoft Fabric REST APIs | Microsoft Learn

The format should look like this:

{
  "displayName": "SemanticModel 1",
  "description": "A semantic model description.",
  "definition": {
    "parts": [....]
  }
}

Next, let's process the Parts section one by one:

According to the section in the part, the content of each file in the folder is encrypted:

definition/database.tmdl
definition/model.tmdl
definition/tables/Table1.tmdl
definition/model.tmdl
definition/tables/Table.tmdl
definition.pbism
diagramLayout.json
.platform

Here I'm using Python to send requests. My script is as follows:

I'm using service principal authentication:

import requests
from azure.identity import ClientSecretCredential

class MyAPI:
    def __init__(self,client_id,client_secret,tenant_id):
        self.scope = 'https://analysis.windows.net/powerbi/api/.default'
        self.grant_type = 'client_credentials'
        self.client_id = client_id
        self.client_secret = client_secret
        self.tenant_id = tenant_id
    def auth12 (self):
        cred = ClientSecretCredential(
            authority = "https://login.microsoftonline.com/",
            tenant_id=self.tenant_id,
            client_id=self.client_id,
            client_secret=self.client_secret)
        acc_token = cred.get_token(self.scope)
        return acc_token.token
    
api = MyAPI(
    client_id = 'xxxx',
    client_secret = 'xxxx',
    tenant_id = 'xxx'
)

workspaceId = 'xxxx'

urlPost = f"https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/semanticModels"
bodyPost = {
  "displayName": "SemanticModel 1",
  "description": "A semantic model description.",
  "definition": {
    "parts": [
        {
            "path": "definition/database.tmdl",
            "payload": "xxxx",
            "payloadType": "InlineBase64"
        },
        {
            "path": "definition/model.tmdl",
            "payload": "xxxx",
            "payloadType": "InlineBase64"
        },
        {
            "path": "definition/tables/Table.tmdl",
            "payload": "xxxxx",
            "payloadType": "InlineBase64"
        },
        {
            "path": "definition.pbism",
            "payload": "xxxx",
            "payloadType": "InlineBase64"
        },
        {
            "path": "diagramLayout.json",
            "payload": "xxxxx",
            "payloadType": "InlineBase64"
        },
        {
            "path": ".platform",
            "payload": "xxxxx",
            "payloadType": "InlineBase64"
        }
    ]
  }
}
token = api.auth12()
headers = {
    'Authorization': f'Bearer {token}',
    'Content-Type': 'application/json'
}

res = requests.post(url=urlPost,headers=headers,json=bodyPost)

print(res.status_code)

It gets the right repercussions code and sets up the right semantic model on Power BI Service:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nilendraFabric]

Hello @UmeshGoti 

Try modifying your “SqlDataSource” definition in the semantic model JSON so that it is a provider data source rather than a structured one. 

type”: “structured”
to
    “type”: “provider”