Dynamics CRM经CDS连PowerBI无法拉取视图，求CDS权限管控方案

Hey Thomas, I’ve worked through similar scenarios with CDS (now Dataverse) and Power BI, so let’s walk through how to solve your key challenges: replacing the SQL mirror, pulling CRM views into Power BI, and implementing fine-grained data access control.

The reason you couldn’t pull system/personal views directly is that Power BI’s default CDS connection doesn’t expose them natively—but you can work around this using FetchXML, which is the underlying query language for all CRM views:

• In Dynamics CRM, open the view you want to use, click the ellipsis (...) in the top-right corner, and select Export FetchXML.
• In Power BI, when connecting to CDS, go to the Advanced options section and paste the exported FetchXML into the FetchXML query field.
• This will pull exactly the dataset your CRM view uses, keeping it aligned with any future changes to the view in Dynamics.

CDS doesn’t support traditional SQL views or stored procedures, but these alternatives will cover your use case:

• Saved FetchXML Queries: Create shared system views in Dynamics (with your required filters/joins) and use their FetchXML in Power BI as described above. This acts as your "CDS view" equivalent.
• Calculated/Rollup Fields: For simple aggregations or computed values (like total sales per account), use CDS’s built-in calculated or rollup fields instead of writing stored procedure logic. These fields update automatically in CDS.
• Power Query Transformations: For complex logic (like multi-table joins, custom calculations, or data cleansing), build the transformation directly in Power BI’s Power Query Editor. Save these transformed queries as reusable datasets for your reports.
• Dataverse Virtual Tables: If you need to combine CDS data with external sources without mirroring, virtual tables let you connect directly to external databases—though this might be overkill for your current goal of replacing the SQL mirror.

To restrict users to specific data subsets instead of relying on default Dynamics roles, use a combination of these tools:

• Power BI Row-Level Security (RLS):
  1. In Power BI Desktop, go to the Modeling tab > Manage Roles.
  2. Create a new role, then define filter rules (e.g., [Region] = USERPRINCIPALNAME() to match a user’s region, or join to a user permissions table to restrict access).
  3. When publishing to the Power BI service, assign users to these roles to enforce the data restrictions.
• CDS Field Security Profiles: If you need to lock down specific fields (not just rows), create a field security profile in CDS, set read/write permissions for sensitive fields, and assign the profile to relevant users. This ensures users can’t access fields they shouldn’t see, even if they have access to the row.
• Filtered Shared Views: Create CDS system views that only include the data subset you want users to access, then use Dynamics security roles to restrict users to only these views (instead of granting full entity access). Combine this with RLS for extra layers of control.

If you still want to use SQL for some queries (like your reference mentions), you can query Dataverse directly using SQL:

• Use the logical entity names (e.g., account instead of the display name Account) in your SELECT statements.
• For more complex SQL logic, you can set up a linked server to Dataverse in Azure SQL or on-prem SQL, then create SQL views on top of the linked server. This gives you SQL-like access without maintaining a full mirror.

Let me know if you need step-by-step details for any of these approaches!

内容的提问来源于stack exchange，提问作者ThomasBart