search

microsoft / coe-starter-kit

Other
753 stars 225 forks source link

How to Export COE Data for Custom Purposes #2826

Closed kbirstein closed 2 years ago

kbirstein commented 2 years ago

Hi,

I am a Power Platform Architect at Insight and specialize in Governance. I have several clients asking how to export the data from COE to external data systems in order to use the data on the growth of the adoption of the Power Platform, the cost centers for premium connectors, etc. In addition, they would especially like to use the data for custom governance canvas apps utilizing SharePoint in order to avoid having to have a premium account (because COE utilizes Dataverse).

The only solutions I have come up with is to laboriously write out the tables to Azure SQL Server (for use with SAP and other high end database systems) and SharePoint (for creating custom governance canvas apps) with the Dataverse "List rows" action in Power Automate (which is not fun because this Action does not provide dynamic values). Then utilizing the relationships I see in the PowerBI "Production_CoE Daashboard" data schema I link them up, also laboriously.

I understand that there is no way to use SQL Server queries against Dataverse since it is an abstraction on top of SQL Server but there has to be an EASIER WAY to extract and use this data, I HOPE!

I am not a Dynamics developer or Dataverse person having been a SharePoint Architect prior to working with the Power Platform, so I'm not aware of all the external tools that may have been developed for use with Dataverse.

Is there some way to use SQL-like queries against Dataverse so I can extract the data utilizing JOINs between the tables?

Any suggestions GREATLY appreciated. My clients love the COE but they really need an easier way to extract the data!!

Thanks,

Kathryn

manuelap-msft commented 2 years ago

Hello,

You can use SQL to query Dataverse. You can also enable the TDS endpoint on the environment and use SQL to query data:

For additional analytics, especially more advanced reporting I believe your best option would be to use the Azure Synapse integration with Dataverse:

I would be careful architecting a solution that writes data from Dataverse to SharePoint to avoid license costs, as that falls under multiplexing. Learn more in the Licensing guide: https://go.microsoft.com/fwlink/?linkid=2085130

Hope those pointers help. Unfortunately we're a very small team looking after the CoE Starter Kit and we can't scale to help with extensions and customizations of the kit.

Thank you Manuela

kbirstein commented 2 years ago

Thanks for the quick response Manuela!

Jenefer-Monroe commented 2 years ago

closing out as no further action for starter kit team