Answered

How to interface with my Dataverse tables

(0) Share

Report

Posted on by ebonnett

Hi all,

I have a process I wrote in C# that pulls in Excel spreadsheets, dynamically creates a table per worksheet using C# and the Dataverse SDK, then populates the table with the data in the spreadsheet so folks can link up and display the data using PowerBI. Once the process is run, the tables are in the Dataverse. However I would like to do some analysis like what is in table A that isn't in table B and vice versa. Normally I would use a view or something in Sql Server. What are my options in the Dataverse? Is there an IDE to query the tables I create in the Dataverse? Something like Sql Server Management Studio? I'm having a real hard time figuring out how the hell to get to the tables and query them once I create them. What gives? What's the best way to ad hoc query my tables?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (2)

Verified answer

ivan_apps 2,189 Moderator on at

Like (1)

Report
Copy link

Link copied!

For basic data querying - you can just create a model driven app and create views and add filters to get the data you need. It has the ability to save personal views and share them with users. These are equivalent to filtered views in SQL.

i believe you can connect to the sql database for Dataverse via SSMS, you’ll have to enable the TDS endpoints and have the right IPs and ports opened up.

there is an XRM toolbox extension called SQL4CDS which queries the tables using limited sql commands - it transforms SQL to fetchxml if I remember correctly.

Lastly, you can use PowerBi and connect to the Dataverse environment and query your data that way. It’s not exactly SQL terminology but joins and a lot of analytics can be done that way.

several ways, hope one works for your needs!

Was this reply helpful? Yes No
Verified answer

ebonnett 35 on at

Like (0)

Report
Copy link

Link copied!

Hi all, I managed to figure out how to connect SSMS to the dataverse environment using this link:
https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query

Thanks, I had tried this before but it didn't work.

Was this reply helpful? Yes No
ebonnett 35 on at

Like (1)

Report
Copy link

Link copied!

Thanks Ivan. Appreciate the extra info too. I figured it out after I posted and saw your response so you get the "Accept as Solution" for being a good samaritan 😉 Have a great day!

Was this reply helpful? Yes No