Changing source of existing semantic model to lakehouse delta tables

Question

Changing source of existing semantic model to lakehouse delta tables

Sunay Pistulkar 5

Hi,

We newly created a lakehouse in the existing power bi workspace, and I want to change the source of the semantic model to the lakehouse. But it is throwing the following error.
"Semantic model connectivity with the XMLA endpoint"

'XMLA endpoint': it needs multiple approvals, we want to find the workaround, because existing models are very critical and complicated, and creating new ones with precision would be a bigger task.
Please let me know if you have any suggestions or solutions.

Thanks!!

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-07-26T07:42:52.02+00:00
Hi @Sunay Pistulkar

Thanks for the question and using MS Q&A platform.

As I understand you are trying to change the source of the semantic model in Power BI to the newly created lakehouse, but you are encountering an error related to the XMLA endpoint.

The XMLA endpoint is used to connect to Analysis Services models in Power BI, and it requires multiple approvals to be enabled. If you are unable to enable the XMLA endpoint, there are a few workarounds you can try:

Use DirectQuery: If your data is stored in a supported data source, you can use DirectQuery to connect to the data source directly from Power BI. This will allow you to create a semantic model without using the XMLA endpoint.

Use Power BI dataflows: Power BI dataflows allow you to create a semantic model using Power Query, which is a data transformation and cleansing tool. You can connect to your lakehouse using Power Query and create a semantic model using the dataflows feature.

For more information refer the below articles:

XMLA endpoint

Troubleshoot XMLA endpoint connectivity

Analytics end-to-end with Azure Synapse

Deploy an End-to-End Azure Synapse Analytics and Power BI Solution using CMS Medicare Data

Enterprise Data Warehouse Architecture

I hope this helps! Let me know if you have any further questions.
Sunay Pistulkar 5 Reputation points

2024-07-26T20:42:46.52+00:00

Hi @Smaran Thoomu
Thanks for your input! The constraint of the problem statement is we need a direct connection to Lakehouse.

We wanted to use Lakehouse as a source in the existing Power BI semantic model(currently, the source is an SQL server). So, we have created the Lakehouse with the same tables used in the existing model. We are not able to find a way we can change the source.

Is there any way we can clone the semantic model and change the source in JSON code?

Thanks!
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-07-30T13:37:26.5466667+00:00

@Sunay Pistulkar We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-07-26T07:42:52.02+00:00

Hi @Sunay Pistulkar

Thanks for the question and using MS Q&A platform.

As I understand you are trying to change the source of the semantic model in Power BI to the newly created lakehouse, but you are encountering an error related to the XMLA endpoint.

The XMLA endpoint is used to connect to Analysis Services models in Power BI, and it requires multiple approvals to be enabled. If you are unable to enable the XMLA endpoint, there are a few workarounds you can try:

Use DirectQuery: If your data is stored in a supported data source, you can use DirectQuery to connect to the data source directly from Power BI. This will allow you to create a semantic model without using the XMLA endpoint.

Use Power BI dataflows: Power BI dataflows allow you to create a semantic model using Power Query, which is a data transformation and cleansing tool. You can connect to your lakehouse using Power Query and create a semantic model using the dataflows feature.

For more information refer the below articles:

XMLA endpoint

Troubleshoot XMLA endpoint connectivity

Analytics end-to-end with Azure Synapse

Deploy an End-to-End Azure Synapse Analytics and Power BI Solution using CMS Medicare Data

Enterprise Data Warehouse Architecture

I hope this helps! Let me know if you have any further questions.
Sunay Pistulkar 5 Reputation points

2024-07-26T20:42:46.52+00:00

Hi @Smaran Thoomu
Thanks for your input! The constraint of the problem statement is we need a direct connection to Lakehouse.

We wanted to use Lakehouse as a source in the existing Power BI semantic model(currently, the source is an SQL server). So, we have created the Lakehouse with the same tables used in the existing model. We are not able to find a way we can change the source.

Is there any way we can clone the semantic model and change the source in JSON code?

Thanks!
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-07-30T13:37:26.5466667+00:00

@Sunay Pistulkar We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Smaran Thoomu 24,110 Microsoft External Staff Moderator

Hi @Sunay Pistulkar

Thanks for the clarification. I understand that you need a direct connection to the Lakehouse and want to change the source of the existing Power BI semantic model from SQL Server to the Lakehouse.

Regarding your question, I think this might be the one possible solution to this problem is to clone the semantic model and change the source in the JSON code. You can do this by exporting the existing semantic model as a JSON file, modifying the JSON file to change the source to the lakehouse, and then importing the modified JSON file as a new semantic model.

To export the existing semantic model as a JSON file, you can follow the steps below:

Open the Power BI Desktop application and open the semantic model you want to export.
Click on the "File" menu and select "Export" -> "Power BI Template (.pbit)".
Save the .pbit file to your local machine.

To modify the JSON file to change the source to the lakehouse, you can follow the steps below:

Open the .pbit file you exported in a text editor.
Locate the "connections" section of the JSON file.
Modify the connection string to point to the lakehouse instead of the SQL server.

Once you have modified the JSON file, you can import it as a new semantic model in Power BI.

I hope this helps! Let me know if you have any further questions.

Sunay Pistulkar 5 Reputation points

2024-08-02T07:43:00.31+00:00

Hi @Smaran Thoomu ,

The idea is to change the source via JSON is not feasible. The JSON file contains more than 1000 lines and is a very manual and critical process. If we miss a single indentation, space, comma, etc. it won't work.

Do you have any other solution in mind?
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-08-02T16:08:16.5166667+00:00
Sunay Pistulkar Thank you for the clarification. I understand that manually editing a large JSON file can be challenging. As far as I know you can use some alternative methods to change the source of your semantic model to the Lakehouse:-

Use the Power BI REST API: You can use the Power BI REST API to rebind your report to a new data source. This method avoids manual JSON edits and can be automated using scripts. For example, you can use PowerShell to rebind the report to the new dataset.

Leverage Microsoft Fabric: Microsoft Fabric offers tools and features that can simplify the process of managing and modifying semantic models. Consider using Fabric's dataflows, enhanced management tools, and data integration capabilities to streamline the transition to the Lakehouse.

I hope this information is helpful. Please let me know if you have any further questions.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sunay Pistulkar 5 Reputation points

2024-08-04T05:26:18.8766667+00:00

Hi @Smaran Thoomu

Thanks for the response!
Can you please provide some clarification and links for the REST API method?

Appreciating your efforts!!
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-08-06T12:21:00.3133333+00:00
Sunay Pistulkar I would be happy to provide more information on using the Power BI REST API to rebind your report to a new data source.

The Power BI REST API allows you to programmatically interact with Power BI objects, including reports, datasets, and data sources. You can use the API to rebind your report to a new dataset, which will allow you to change the source of your semantic model to the Lakehouse.

Here are the high-level steps you can follow to use the Power BI REST API:

Register an Azure AD application: To use the Power BI REST API, you need to register an Azure AD application and grant it the necessary permissions. You can follow the steps in this article to register an Azure AD application: https://docs.microsoft.com/en-us/power-bi/developer/embedded/register-app

Authenticate with the Power BI REST API: Once you have registered your Azure AD application, you can authenticate with the Power BI REST API using OAuth2.0. You can follow the steps in this article to authenticate with the Power BI REST API: https://docs.microsoft.com/en-us/power-bi/developer/embedded/embed-service-principal

Rebind your report to a new dataset: Once you have authenticated with the Power BI REST API, you can use the API to rebind your report to a new dataset. You can follow the steps in this article to rebind your report to a new dataset: https://docs.microsoft.com/en-us/rest/api/power-bi/reports/rebindreport

I hope this information is helpful. Let me know if you have any further questions or if you need more detailed information on any of the steps.

Share via

Changing source of existing semantic model to lakehouse delta tables

1 answer

Your answer