0

What's the backend database query of this Microsoft Dataverse Analytics dashboard?

I'm trying to workaround Dataverse analytics by accessing the transactional database behind that dashboard, I'm interested in getting Daily Active Users (DAU) shown above but via a SQL query and reading directly from the backend database.

It appears that the DB is this https://learn.microsoft.com/en-us/dynamics365/customer-engagement/web-api/entitytypes?view=dynamics-ce-odata-9 but I have not been able to comprehend the data model and I'm unable to find the tables to get DAU. Any thoughts?

Thanks

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
dim_user
  • 969
  • 1
  • 13
  • 24

1 Answers1

0

Basically you have to do everything what is MS doing in behind the scenes. CRM online is SaaS model and we don’t have access to Azure SQL server directly. But what you can do is, one of these options:

  1. Use “Data export service” to replicate the data to your own Azure SQL server, then build Power BI on your own from the data
  2. You can use REST Web API to pull the data and visualize (May not be so much flexible)
  3. Based on your need and urgency, you may wait or use preview version of TDS endpoint, for read-only direct SQL access. Read more
Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
  • Thanks Arun, number 3 is what I'm doing, I'm connecting to the DB (dynamics-ce-odata) via SQL/SSMS but my question is whether that is the right DB to get to metrics like "Daily Active Users" (shown in Dataverse) or if there is documentation on how these metrics get calculated. The goal is to have an ad-hoc way of querying for now and then build a dimensional DWH to land the data in-there. – dim_user Nov 25 '20 at 00:29
  • 1
    @dim_user if you see the update frequency of metrics/chart - it says 24 hours, so definitely these are not readymade data - some background massage jobs running in intervals. It used to be called as Organization Insights, even there was a plan to expose an api to get the same chart/metrics accessible for custom portals/reports - not sure on the latest. Even the auditing/logging took a makeover in product side and you have to reach out to MS team for those roadmaps and internal data logistics. I don’t think they are open sourced or made available in github or somewhere documented. – Arun Vinoth-Precog Tech - MVP Nov 25 '20 at 00:48
  • I will reach out to MS... I will keep you posted. Thanks – dim_user Nov 25 '20 at 01:12
  • @dim_user did you get any break through? – Arun Vinoth-Precog Tech - MVP Dec 04 '20 at 19:50
  • 1
    MS engineers replied back saying >> There is no entity data model or service endpoint that makes that data available via an API. – dim_user Dec 19 '20 at 01:00