Predefined views through CDS (Dynamics CRM - PowerBI)

[Anonymous]

Hello there forum!

You have helped me inmensly in the past but I am stuck again. 

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. Ofcourse, I can just get whole Dynamics Entities through the CDS connector in PowerBI but I cannot select the views I have prepared. 

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 refesh 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)

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 priviledges in Dynamics. 

Edit:
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/

End Edit

This is not completely a PowerBI question but the dynamics and SQL user forums are neither as nice nor as easy to ask things. 

With kind regards,

Thomas 

[Mariusz]

Hi @Anonymous 

Maby a silly question but, did you set the right level of permissions in SQL db so the users are able to see your views?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hello @Mariusz,

Very good question, our team has deduced that the administrator account of our CRM is not the Server administrator / owner / god user, however, I am not trying to do anything else but read from de CDS server. 

The error I mention above is run in the CDS but this error right here as well: 

As you can see our CDS does not even have a name. Which means we can also not use the Add linked server command to access it from our other more straight forward Data base engine SQL server, as we do not have a name to link to. 

USE master;
GO
EXEC sp_addlinkedserver
N'TestServer',
N'SQL Server';
GO