Project for the web data is stored as entities within Common Data Service. Besides Project entities, CDS stores standard and custom entities of other apps. Storing in CDS platform gives opportunity to customize, extend using Power Platform (Power Apps, Power Automate, Power Virtual Agent, Power BI)
Let say we, the project managers, want to turn this information into actionable insights. How do we do it?
Power BI Common Data Service connector allows us to connect directly to CDS entities, and build reports and dashboards on Power BI desktop and Power BI service. Yet, this work requires we need go beyond the data stored in Project entities. The good news is this information is available either on cloud (in CDS, other data sources) or on premise (SQL Server, Files, Folders, etc) and we have connectors for those too.
Are we correct in bringing all information directly to Power BI?
Will this approach offer a standard, easy and fast insights?
Let’s start with Common Data Service. CDS is a transactional database and not a data warehouse. It has plenty of “deliberate” constraints. These constraints are imposed to prevent any one action having too detrimental an impact on the rest of the system and, therefore, on users. Along with these constraints, increase in data volume will impact the performance of CDS, its apps and reports.
No Common Data Service data operations, either internal or part of code customizations, work completely in isolation.
All Common Data Service data operations interact with the same database resources, either at a data level or an infrastructure level such as processor, memory, or I/O usage.
To protect against conflicting changes, each request takes locks on resources to be viewed or changed.
Those locks are taken within a transaction and not released until the transaction is committed or aborted.Source: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/scalable-customization-design/database-transactions
The second challenge is Project data is stored in many entities with relationships. Citizen developers, like project managers, will need support and time to understand these relationships. If not, we may apply a wrong filter and miss creating relationships resulting in incorrect data in visuals.
The third challenge is data from other data sources could be fragmented, incomplete and complex making data preparation difficult.
Final challenge is project managers should provide the environment URL for the CDS for Apps environment and have permission to access that environment.
In this post, I offer a solution these challenges. Read on and let me know your thoughts.
Dataflows runs in Power BI service. It imports the business data, and will have the data ready for modeling and creation of BI insights. Behind the scenes, dataflows can load data to Common Data Service or Azure Data Lake Gen 2. Data imported to Azure Data Lake Gen2 is stored in Common Data Model (CDM) folders. It can configured to do the data fresh on a real-time basis.
To create a dataflow, launch the Power BI service in a browser then select a workspace from Workspaces or create a new workspace. Now, to create a dataflow select the + Create button, then select Dataflow from the drop-down list.
In our requirements, let’s look at combining two entities : msdyn_projects, msdyn_projecttasks to a new dataflow entity. This new entity should have project id, project name, project start, project finish, task name, flagged critical, task start and task finish.
Create a dataflow
First, click Define new entities > select Common Data Service connector > provide environment URL for the CDS for Apps. Select
Next, in the dataflow editor transform the data and combine the two entities. This dataflow editor is similar to Power Query editor in Power BI desktop.
This dataflow can be refreshed periodically (or) incrementally (if the workspace is in Premium capacity).
Connect to dataflow
Final step is connecting to the dataflow from within Power BI desktop or Power BI service. To access the dataflow, the user should have at least View permission with Pro license. Even if they don’t have a Power BI Pro license, they can view and interact if the workspace is in Premium capacity.
To connect to the dataflow, in Power BI Desktop select Get Data > Power BI > Power BI dataflows as shown below
Did we find the solution?
Yes. Dataflows stores the data in a standardized, centralized, easy-to-use, reusable format. With right permission set to workspaces, it can create wonders. So, what do you think?