cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Making a connection to the tables in Dynamics app through the dataverse TDS endpoint

AbkSpl
New Contributor III

I wish to do some analysis on tables that are stored in dataverse in databricks. I know that PowerBi uses its Dataverse connector to fetch the data using a Dataverse's TDS endpoint. The tables that we import in PowerBi using this connector is nearly perfect with all the relationships accounted for.

I am trying to establish a similar connection in databricks using connectors or through azure data lake/data warehouse services. Connecting directly to the web app is not an option because it fetches the tables without proper lookup values and relationships.

Is there some suggestions or docs that I could follow?

1 ACCEPTED SOLUTION

Accepted Solutions

AbkSpl
New Contributor III

I finally followed the following method for my requirement:

1) Connected the data verse tables from PowerApps to synapse workspace using the synapse link

2) Used the standard template provided in it to convert the multiple .csv files to parquet format

3) Translated the missing columns in the converted parquet file using the metadata information as given here in Access Dataverse choices

It is still difficult to translate the metadata within databricks since there could be multi-level relationships. But replicating the PowerBI dataverse connector seems to be currently not possible. So I am able to temporarily resolve this issue.

View solution in original post

11 REPLIES 11

Kaniz_Fatma
Community Manager
Community Manager

Hi @Arjun Bala Krishnanโ€‹ , If you're using standard dataflows that store the data in Dataverse, you can still connect to Dataverse from many Azure services. The following image shows that in Azure Data Factory, the output of dataflow from Dataverse can be used as a source.

Please go through this doc.

image 

tjs1
New Contributor II

Hi @Kaniz Fatmaโ€‹ , could you share how to connect to Dataverse from Databricks specifically? This doesn't really answer the question, the doc doesn't help as well unfortunately.

Kaniz_Fatma
Community Manager
Community Manager

Hi @Tomasz Starakiewiczโ€‹, Databricks is a cloud-based service that provides data processing capabilities through Apache Sparkโ„ข. When paired with the CData JDBC Driver, customers can use Databricks to perform data engineering and data science on live Microsoft Dataverse data. This article walks through hosting the CData JDBC Driver in Azure and connecting to and processing lives of Microsoft Dataverse data in Databricks.

Please let me know if this helps you.

AbkSpl
New Contributor III

CData drivers are not free. To connect data from one microsoft service to another must be without the help of outside (paid) solutions. This was also the first google suggestion.

AbkSpl
New Contributor III

Thank you @Kaniz Fatmaโ€‹ for the reply. Is it possible that PowerBi is using an internal azure datalake when importing tables using its TDS (SQL) endpoint connection? It would be helpful to know since I am struggling to reproduce the exact table that Bi imports on databricks (View table data in Power BI Desktop). I could then go ahead and try a connection using an external data lake storage.

Hi @Arjun Bala Krishnanโ€‹, Power BI desktop already supports the Azure data lake Gen2 connector; you can use this to get data from your data sources.

AbkSpl
New Contributor III

I am not looking to connect data lake to power BI. The objective is to connect dataverse tables to databricks including the relationships.

Anonymous
Not applicable

Hey @Arjun Bala Krishnanโ€‹  and @Tomasz Starakiewiczโ€‹ 

Hope everything is going great!

Does @Kaniz Fatmaโ€‹'s response answer your question? If yes, would you be happy to mark it as best so that other members can find the solution more quickly?

We'd love to hear from you.

Thanks!

AbkSpl
New Contributor III

Unfortunately the topic is not resolved so far. I have been in contact with MS Support ever since. Both mail and live support has not got us to the expected target. I will propose the best solution that I know of in this thread.

AbkSpl
New Contributor III

I finally followed the following method for my requirement:

1) Connected the data verse tables from PowerApps to synapse workspace using the synapse link

2) Used the standard template provided in it to convert the multiple .csv files to parquet format

3) Translated the missing columns in the converted parquet file using the metadata information as given here in Access Dataverse choices

It is still difficult to translate the metadata within databricks since there could be multi-level relationships. But replicating the PowerBI dataverse connector seems to be currently not possible. So I am able to temporarily resolve this issue.

Thank you for your sharing the solution with our community @Arjun Bala Krishnanโ€‹ !

Would you mind selecting your answer as the best? Thanks.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group