Pre-filtered tables / entities, Systems Views or Stored Procedures in CDS (based on Dynamics CRM DB)

0
votes

Problem: I have our CRM connected to PowerBI through Common Data service (CDS) but I am not able to pull the views (system or personal) from Dynamics into PowerBI.

Tried: Of course, I can just get whole Dynamics Entities through the CDS connector in PowerBI but I cannot select the views I have prepared.

Situation: At the moment we use a mirror image of our Dynamics Database in SQL to run data queries of for PowerBI (as to not affect the performance of the Dynamics server when someone pulls millions of rows of data to refresh their BI reports.) We create the copy of our Dynamics Data Base using the Data Integration Tool for Dynamics CRM. The Views (& Stored Procedures) we have set up in our SQL Data Base ("....database.windows.net" image below) are what we give users access to to run their reports from. (I did not expands the views/store procedures but their are there)

Solution: Image of SQL code & errors
It would be much more efficient to not have to use the Data Integration Tool to pass our Dynamics Data Base to our SQL Data Base and instead use Common Data Service, however, in CDS I do not seem to be able to create either:

  • Views

  • Stored Procedures

I understand that CDS is more a way to access data than a data base but we want more control over which data users can access. That is, we want to only show them what we choose to, which is a sub set of what they would be able to see using roles and privileges in Dynamics.

Here an article on how we have set up the Dynamics - CDS integration: https://www.powerobjects.com/blog/2020/05/20/use-sql-to-query-data-from-cds-and-dynamics-365-ce/

1
sql-serverdynamics-crmcommon-data-servicesystem-views
Any follow up questions? – Arun Vinoth - MVP

1 Answers

0
votes

Some facts, you may be aware of these, still sharing for clarity:

  1. Creating objects like views/SPs in CRM database is not supported (not recommended) even in the past on-premise days. Now so far CRM online DB is inaccessible, only replication using Scribe or DES is the approach (I believe your *.database.windows.net is one like that) before this Preview (semi-cooked) of CDS database querying capability introduced on May 6th MBA summit.
  2. CRM views are not same as Database views. CRM views (personal/system views) are just predefined Advanced find fetchxml queries stored in savedquery and userquery entities. These can be executed using CRM Org service or SDK.
  3. In old days, we used to query the filtered views in SSRS reports which maintain the CRM data security model integrity (like user owned, BU Team shared, etc) and SSRS fetchxml reports carried over that expectation. But PowerBI report RLS is not enough and when they are built on top of those replicated databases where the "filtered views" are not there. Atleast you can create objects like SPs/views in those replicated databases but since the CDS direct querying capability is still in preview - we can expect more features like that (I still doubt!? but atleast documentation answer that) when it is GA (99% cooked).

I'm still waiting for CDS database direct querying to go GA with full capability and documentation.