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?

[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.

[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.

[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.