Hello,
I'm trying to connect Power BI to Project Operations to report on an individual Project's summary, including its status/health & its tasks/work breakdown structure. I'm coming across several issues.
Prerequisites:
1. We have a single Tenant, with multiple different Environments including multiple productions environments. For Projects and project-related work/data, we've created and are using a new sandbox dev environment with the Project Operations app installed.
2. We are using a lite deployment of Project Operations
3. I am a system admin for our sandbox dev environment & I have a Power BI Pro license. I do not have tenant admin permissions.
What I've tried:
1. Power BI Project Templates
I downloaded the Microsoft project templates from here (https://github.com/OfficeDev/Project-Power-BI-Templates). Regardless if I'm signed into my Microsoft account or not, I try to use the sandbox URL (https://<devenvironment>.crm.dynamics.com/ or <devenvironment>.crm.dynamics.com) and none of the templates can connect to our Dataverse.
If I use "Microsoft Project for the Web Power BI Template", I get this error for all of the tables/entities/queries.
Loading blocked by failures with other queries.
When I use "Microsoft Project Power BI Template" it asks for a Sharepoint PWA url. Please correct me if I'm wrong but Project Operations doesn't have a Sharepoint PWA url, so I can't seem to use this template.
2. Dataverse Connector
I tried connecting to our sandbox environment using the Power BI Dataverse connector. I see our environment but I get the following error.
Microsoft SQL: A connection was successfully established with the server,
but then an error occurred during the pre-login handshake.
(provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
None of the tables load.
3. Power Query
I referenced this article (https://crmchartguy.com/2019/07/23/comparing-the-three-main-ways-to-get-dynamics-365-data-into-power-bi/) and used Power Query to connect D365/Project Operations to Power BI. I was able to successfully pull in the Project table with my custom fields, but it doesn't show the Project tasks/wbs.
I think I have to pull in the Projects tasks table (msdyn_projecttasks), but will I have to rebuild the data model/establish the relationships? I'm trying to avoid that. Ideally I just want to embed the existing Project tasks/wbs as a widget/visualization into the Power BI report, and if possible, filter upon that with Power BI. How can I accomplish this?
Conclusion
Where does Project Operations projects & project tasks actually live in the Dataverse?
Why can't I connect to the Dataverse with Power BI?
How can I get options #1 or #2 to work?
How can I filter on project tasks with Power BI?
Is there an easier method to report on an individual Project's summary, including its status/health & its tasks/work breakdown structure using Power BI?
Thanks,
Adil
