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

[AbkSpl]

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?

[Kaniz]

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.

 

[tjs1]

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]

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]

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]

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.

[Kaniz]

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]

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

[Anonymous]

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]

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 · Accepted solution]

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.

[Kaniz]

Thank you for your sharing the solution with our community @Arjun Bala Krishnan​ !

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