Forum Discussion
Need to Create Semantic Layer by providing sql query
Hello Community,
As part of report migration to PowerBi. i am using Fabric Rest APIs (https://learn.microsoft.com/en-us/rest/api/fabric/semanticmodel/items/create-semantic-model?tabs=HTT...)
to create semantic layer.
here i have requirement like i need to provide direct query(i.e. SELECT USER_ID,FIRST_NAME,LAST_NAME,USER_NAME,ACTIVE_STATUS, MOBILE_NO, EMAIL_ID FROM app_users) to PowerBi and create semantic layer
can someone give idea using example if possible on how can i achieve this ?
Hello UmeshGoti ,
Thank you for following up on my previous response.
No, you generally don't need to manually define the column details and table names if your SQL query is well-formed and includes all the necessary tables and columns.
Power BI will execute the query directly and infer the schema from the query's result. The columns and tables are automatically understood by Power BI based on the provided SQL.
Power BI usually infers the schema from the SQL query automatically. However, manual intervention might be needed if it struggles with complex queries or derived columns.
Prerequisites to Implement Semantic Model with Direct SQL Query in Power BI:
- Ensure you have the connection string to your database in Base64 format.
- You should have a valid API token or other authentication details to access the Power BI API.
In a SQL query that pulls data from multiple tables, you typically use JOINs, subqueries, or other methods to combine the data. The most common approach is using JOIN statements.
Before calling the API, try to build the connection string and use that connection string with SQL query to call the API.
To send this request, use a POST method to the Power BI Fabric API using powershell or bash.
I hope this help you understanding, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
8 Replies
Hi UmeshGoti - you'll need to use Microsoft Fabric REST APIs to create a semantic model in Power BI and configure it to use DirectQuery against your database.
Use the Create Semantic Model API to define a new model in Power BI.
POST https://api.fabric.microsoft.com/v1/semanticmodel/items
More details :Power BI REST APIs semantic model permissions - Power BI | Microsoft Learn
Hope this link helps.
Thanks rajendraongole1 for quick reply but i didn't get idea can you please explore more, that will help a lot.
if possible can you share documentation link for API POST https://api.fabric.microsoft.com/v1/semanticmodel/items
and request body in which we can pass SQL query.Hello UmeshGoti ,
Thank you for reaching out to the Microsoft Fabric community forum.
Thank you rajendraongole1 for giving inputs.
To create a semantic layer in Power BI using the Fabric REST APIs, please follow these steps:
- Use the Fabric REST API to define a new semantic model in Power BI, specifying the model details and the data source.
- Configure the semantic model to use DirectQuery against your database, enabling Power BI to execute SQL queries directly on the database.
- Include your SQL query in the request body when creating the semantic model. This query will retrieve data from your database.
For more detailed information, please refer to the following documentation:
Items - Create Semantic Model - REST API (SemanticModel) | Microsoft Learn
Deploy a Power BI project using Fabric APIs - Microsoft Fabric REST APIs | Microsoft Learn
I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Thanks Anonymous
I got some idea on how can i proceed for my requirement, but still i need some help with the points you mentioned- Use the Fabric REST API to define a new semantic model in Power BI, specifying the model details and the data source.
For this already i am able to create semantic layer by providing model details like name, column details, datasource connections(database table from which data will be fetched) in Base64 format
but my question is if we have direct SQL query in our hand than still need to define column details and table name becase we can have multiple tables in SQL query - Include your SQL query in the request body when creating the semantic model. This query will retrieve data from your database.
Can you please give me more information or possible to give one sample of just basic query to report generation
These things will help me a lot towards building my solution
Thanks
Umesh Goti
Hello UmeshGoti ,
Thank you for following up on my previous response.
No, you generally don't need to manually define the column details and table names if your SQL query is well-formed and includes all the necessary tables and columns.
Power BI will execute the query directly and infer the schema from the query's result. The columns and tables are automatically understood by Power BI based on the provided SQL.
Power BI usually infers the schema from the SQL query automatically. However, manual intervention might be needed if it struggles with complex queries or derived columns.
Prerequisites to Implement Semantic Model with Direct SQL Query in Power BI:
- Ensure you have the connection string to your database in Base64 format.
- You should have a valid API token or other authentication details to access the Power BI API.
In a SQL query that pulls data from multiple tables, you typically use JOINs, subqueries, or other methods to combine the data. The most common approach is using JOIN statements.
Before calling the API, try to build the connection string and use that connection string with SQL query to call the API.
To send this request, use a POST method to the Power BI Fabric API using powershell or bash.
I hope this help you understanding, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi UmeshGoti ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
- Use the Fabric REST API to define a new semantic model in Power BI, specifying the model details and the data source.
