Please find below a transcription of the audio portion of Sai Prasad’s Make Project for the Web Smarter webinar being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the live recording of this webinar at your convenience.
Hey, hello everyone. In today’s webinar, we are going to talk about how to make your project for the web smarter. To give you background of it, last year, 2019, Microsoft came up with a new project management vision. It states, “Team work the way they want. Enterprises get the result they need.”
In order to realize this vision, Microsoft came up with the new project that they call as a project for the web. [Pim 00:00:36] has done a webinar on MPUG. I may strongly recommend you to look at that webinar to understand more about its features.
As you can [inaudible 00:00:46] see in this image, the project for the web is having a very simple, intuitive user interface and it is browser based interface. So, you don’t need to have a client and it is quite available in any device.
Though project for the web does have a lot of those intuitive interesting features, there come some challenges. If you have been working with Project Line or Project Online or Project Silver, you know where the data resides. But where does the data reside on the project for the web? How do I access it? How do I customize it as per my organizational needs?
That’s what this webinar is going to focus on. We are going to go behind the scenes to understand where is this data residing. We call that as CDS or common data service. We’ll also look at tools that are available, through which you can get insights of this project for the web data. We’ll also look at automating certain routine tasks that you would often want to do in any project management tool. Finally, we will conclude with how do you build a chat bot using the [inaudible 00:01:59] platform.
So, first, let’s get in the user interface first. I’m just going to go back to my browser. Right now, I’m on the homepage of Office 365. I’ve just clicked in the project tile. The project tile will ideally open the project on the modern homepage. You can notice the recently created projects being listed in recent and any project that I have created is listed under created by me.
How do you create a new, blank project on the project for the web? The options are going to be pretty much direct and simple. Just use this command, the new blank project.
You can just give the name of the project by just clicking on this title. You can just say who the project manager is, you can start, you can provide a project start date. You can also provide some additional information about the project over this interface. I’m not going to touch more on this. I would strongly recommend you to look at the webinar that I called out before.
I’m just going to delete this project and get started. I’m going to look at one of the existing projects that I’m having which is Project 101. Let me select this project. This project is having three task: research, build, and test. I have one or more team members assigned to it. I’m also having some durations, some dates assigned to it.
You can notice that we have a timeline view. It’s going to help us to understand more [inaudible 00:03:49] Gantt chart how are these tasks linked with each other and what is the timeline of this particular work.
But where does this data come from? That’s the big question. This data resides in what we call a CDS or common data service. Without understanding the structure of CDS or where the data’s getting stored, it’s going to be quite difficult for you to get this data out and start building apps, building flows, or building bots on top of it.
For this, we need to understand another term which is called as environment. As for the documentation from Microsoft, they say an environment is a space where you store, you manage, share your organizational business data, absent flows.
The CDS, the common data service, is naturally the data that is residing within this environment. Just to give you one more example on it, as you can see we are having a tenant. Within a tenant, you can have multiple environments. Each and every environment will have its own set of apps, flows, and the common data service.
You can actually port the apps or flows of your entities from one environment to another environment and that’s what environment is. Where is this environment and how do I locate my CDS of project for the web?
So, I’m now into another app of Microsoft which is the Power app. I’m going to use a navigation here which is data. I’m going to look at entities. Okay? The entities are ideally you can try to look at drawing some [inaudible 00:05:41] to it. These are more like tables that you are having in a database. But ideally, we don’t call this a database or we don’t call this a tables. Rather, we call them as a set of entities.
As you can notice, each one of these entities is having a display name. They’re also having an internal name associated with it. All these entities is actually getting stored in what we call as a common data service. This common data service resides right now in the default environment. Okay?
Let’s try to look where does this data come from. Going back to the Project 101, you can see I have a set of tasks, I have some information about the resources, I also have some timelines and some dependencies on it. This information that you see on the project for the web is actually pulled from some selected entities in the CDS.
Let’s search for it. I’m just going to make it all and I’m just going to look at one particular entity that is of interest to us. Let’s start looking at the first entity which is project. The project entity is having these fields, so these fields are either updated or these fields are ideally used, then we are using the project for the web.
To understand its data, I can just click on the data tab where you can see I have two projects, the Project 101 and Project 201. In a similar way, you can move to different entities to look at this data and this is what CDS and this is an example of how to look at the entity that resides within the CDS. Very nice.
Now, let’s move over. Now we understand that the project data is stored in CDS and each and every element that you see on the project screen is actually being pulled or updated from several entities. As a citizen user, like a project manager, it’s going to be difficult for each one of us to understand, to remember, and to look at the relationship between all these entities.
How do I get some insights? How do I build apps on it? How do I get reminders if something is due? This webinar is the answer to it. Microsoft is having a platform that is going to empower you analyze, to act, and to automate on this data. But how does it do it?
With respect to analyze, act, and automate, they have come up with what they call as the power platform. It’s often called as a local platform because it considers that not all would be able to program, would be able to do a lot of complex tasks. All these apps that we are going to look at, right, they are often called as the local platform, through which you can do insights, you can look at building apps, you can automate a lot of stuffs, and you can also build bots in minutes.
All these apps or the components of the Power platform is pulling information from the common data service that we talked earlier. It is also having data connectors where you’re not restricted to just use a common data service. You can also try integrating these platforms to other apps and to other data providers.
First, let’s start with what is going to be the key focus for today? We are going to look at how to look at the project for the web data on three Power platform apps. One is Power BI, the other being the Power Virtual Agent, and the third one is about Power Automate. Earlier, it used to be called as Microsoft Flow.
As we all know, Power BI is a business intelligence tool through which you can get some rich insights on the data through reports and dashboards. You might be wondering, how do I get this data out here?
Microsoft did a good [inaudible 00:10:12] here. They came up with what they call as the project for the web, powered BI template. This particular template is available in Github. You can download it, you can open this template, and it is ready for use. So, let me give you a small example for it.
I’ve already downloaded this template from this Github. Okay, let me have it opened for you. Okay. Once you open this template, it is prompting me to provide the CDS URL. So, as you see, the CDS URL includes the environment name, its region, and [inaudible 00:10:53]. But how do I locate my default CDS URL here?
Here’s a technique. Going back to the project app, the Power app, you can go to the settings? Okay, and you can go to the advanced settings here. When you go to the advanced settings, just closely look at this URL. This is the CDS or the default CDS URL which you need to take in. I’m just going to take this URL. Okay, I’m just going to paste it out here. Okay, and I’m going to say load.
It is going to pull in the data that reside in the entities that we mentioned before and it’s going to bring in some reports, some visuals around it. Let’s give a few seconds for it to bring the data. (silence)
Okay. Here it is. You can see that it has listed B1 project which is Project 101 because I am assigned as the project manager to it. To understand the details about these projects, I can go to the other pages of this report. It talks about who’s assigned to this current project, okay? It gives me who’s working on it, how much our work has been completed, what [inaudible 00:12:43]. A lot of interesting insights has been provided.
Though we are having a lot of visualizations based on the URL that I supplied, here comes a catch. In order to provide the CDS URL, you need to have a project B1 license at least. You need to have a license for the project for the web. If you don’t have a license, you will not have access to this particular data.
How do I solve this particular problem, right? Another common challenge that you may ideally [inaudible 00:13:17] is when you are trying to pull a dashboard or a report like the way I just did, it might be sometimes very slow and it’s going to be very difficult for you to take information or data that comes from different sources of the same project into the same dashboard, because you start opening this template, saying that all my data resides in common data service.
So, the first element that you did notice here, are we right in bringing all that information in CDS directly to Power BI? Let’s find the answers to it. The first challenge that we talked about is common data service itself. A common data service is a transactional database. When I say transactional database, it is where data gets inserted, modified, and read.
Many of us are going to use the same CDS URL every time. It is naturally going to add a lot of performance issues to your transactional database. That’s number one. Number two, there is a lot of relationship that do exist between all these entities. Going back to the Power BI template, if I look at the relationship or the model view here, you can notice that it is having a lot of these complex relationships.
If you are going to be a first time user for the project for the web, it’s going to be quite difficult for you to understand how these relationships work and you may even take the risk of breaking these relationships that is going to affect your reports. So, you need to be very careful about it.
The third challenge that you see here is there might be some data that resides outside a common data service, which you may need to visualize as part of this report, which right now is not possible in this template.
The last challenge is naturally, you should have a license, you should have permission to access that default CDS. What is the answer to this? Let’s look at the solution right now.
The solution comes from Power BI itself, so I’m going to go back and look at Power BI, okay? In Power BI, we can create what they call as a data flow. A data flow is ideally a mechanism where you are trying to pull information from different sources and you can store it in Azure Data Lake which is more like a data [inaudible 00:15:57].
Maybe routinely, you can pull that information and you store it in the data [inaudible 00:16:02] and you can use Power apps, you can use Power BI to pull that information and take some interesting insights out of it. What are some of the prerequisites for it?
Number one, you should have a Power BI license, okay? Number two, your Power BI free license is not sufficient. You should have a Power BI pro license. Why? Because for data flows, you need a pro or a premium license. So, only pro license users and above would be able to create data flows.
As you can see on the left of the navigation, we are having my personal workspace. We are also having an app workspace. So, you need to create an app workspace within which you are going to create these data flows. Okay? I’ve already created an app workspace here, so I’m just going to open it so that you can understand the method.
You can see there are different elements in an app workspace. We can have dashboards, we can have reports, we can have workbooks, data sets and data flows. Let’s get into the data flow here, okay? The first steps is in order to create a new data flow, you use this particular command called create. Okay, and I can just say create a data flow here.
When you’re creating a data flow, it gives me several options. You can say that I can define a new entity or I can link entities from other data flows, I can import a model, or I can attach a common data model folder. Since we are going to port or take a copy of the data which is there in CDS into your Azure Data Lake, I’m going to use add new entities here.
Let’s see add new entities. Here it is giving me, what are the available data sources here. I’m just going to use common data service, okay? It is prompting me what should be the server URL, so this is nothing but your CDS URL is what I’m providing. Okay, and I can just do a sign in as a different user and then I can go to the next, okay? I’m just going to click on next here.
This is displaying all the entities that are there in your common data service. You can ideally search for it, so I can just say project and I can select the entities whose information or data I need to copy to the Azure Data Lake, okay?
This is a typical process that you follow, so I’m just going to cancel this creation so that I can show you what I’ve already completed, okay? I can go to the data flows, so this is a data flow that I created within which you see that I have two entities. The first entity that you see here is project summary but you might recall that there’s no entity called project summary. How did I create it?
Let’s edit these entities. Here, you can see that I am having two entities that I added to this data flow. One is the MSDN project which is having the list of projects in the common data service, okay? The next one, as you can see, this is the project task, so it is going to have information about all the tasks of all the projects.
And the third one is not an entity which was imported, it was something that I created. So, how did I do it? You can ideally merge these entities here because one of the challenges that I stated here is it’s going to be very difficult for you to create relationships, okay? Create relationships between the different entities. It’s going to be difficult for you to understand the relationship that exists between these entities.
If you spent a lot of effort in understanding this model, you can try to look at denormalizing this data so that you can try to look at merging this data and pushing this denormalized data into your Azure Data Lake. Let me show you how I did this. Okay, so I’m just going to use this particular command called merge.
Okay. Just give me a second. Yeah. I’m going to use the combine. Okay, I can use merge, so I can merge the first entity with the second entity. I’m going to say merge, [inaudible 00:21:16] as new. So I can select the first entity which is the project, the second entity being project task. Okay, so here I need to say what is the column that should be used to do a merge, so I’m going to select the project ID.
Let’s look at… Yeah, this is the project ID. On the project task, I would like to have the project ID selected again. So, here it is. Right? What type of join you would like to have? I would like to have everything on the first table to be part of the match but anything that is matching in the project task, okay, I need to pull [inaudible 00:22:04] records and then merge entity.
This is the process that I followed here, so after doing this process, you have a new entity that I created called project summary. Since I’m having only a pro license, you need to ensure that you turn off the enable load for the project as well as the project task. Why? Because if I have this enabled, you will notice that the project summary, you are getting a message that it is treating it as a computed entity and it is available only for premium subscription.
Since I am not quite interested on project and project task, I’m more interested on the merge of the data, so I’m just going to say enable unload. Naturally, these two entities will not be ported, okay? It’s only the merged entity that will get ported into your data lake. Okay? Fine.
I’m going to cancel it for now. The next step is I have created an entity in data flow, but how do I update the data that’s stored in the data lake frequently? In your data flow, we can do a scheduled refresh.
You can ideally say how frequently should this data in the data lake be refreshed. You have several options here. Since we are going to look at more from an insight perspective, you can say maybe everyday or every week, maybe based on this time, this refresh should happen.
During the set day and the set time, data flow is going to be run. It takes the data from the CDS and it puts it into the Azure Data Lake. Okay? Now comes the final step. So, I’m going to go back to my Power BI. I’m just going to say that I need to get data from our Power BI data flow.
Please note I’m not opening a template right now, okay? What naturally it is trying to look at the workspaces I have access to. Okay? You can notice that on the project app, we have a data flow called project within which we are having two entities. I can select this particular entity, okay? I can do a load on transform on it, so I can just do a load for now, so that you can see what is present.
Okay. Let’s give a few seconds. I’m just going to look at the data out here. Okay. You can see that this is my merged entity. It does not have all the columns of the project and the project task. During the merge operation and after the merge operation, I have removed the columns that is not needed, so you just have the relevant information.
Using this information or using this idea, you can try to look at getting the data from a CDS and having it in a data flow. You can give access to the app workspace, either as a viewer or a member so that people are able to read this information.
This concludes the first topic of how we can use the data flows of Power BI to mitigate these challenges. Now, let’s look at another scenario, right? We may come across instances where we would like to get some quick updates about the project. Maybe what are the tasks that I’m assigned to or what are the projects that I am managing or what is the status of my current project?
One of the apps that is available in Power platform is the Power Virtual Agent. You can build project bots which can interact with your CDS and get this necessary information that you need. Let’s see how we can do it. I’m going to go back.
Right now, I’m logging into the Power Virtual Agent. Okay, so here are certain basic steps. The first step is Power Virtual Agent is actually a platform where you can build bots. These bots are citizen friendly bots which means you don’t need to know about programming. You can just create certain simple bots by using this mechanism, okay?
How do you create a bot? The first time when you go to your Power Virtual Agent, it will prompt you that you need to create a bot. But I’ve already created a bot here, it’s just called as the MPUG. Okay? Right now, I’m looking at my MPUG bot.
Let’s do some quick round of testing so that you can understand what comes next. I’m going to test this bot right now. I’m going to give some messages to it, okay? And I’ve logged in with my ID and it is so that I’m able to see how the bot behaves.
I’m just going to use a keyword. Project Bot. Let’s wait. Nice, so you’re able to see a message that comes in and it says, “To continue, please login.” So, let me click on login here. I’m going to login.
Once I have logged in using my ID, you see that there’s a validation code that says I need to type that in the chat window. I’m going to copy this, I’m going to go back, I’m going to paste it, say, “Send.”
Nice, so it says, what are the list of projects that I’m working on [inaudible 00:29:23]? Right? How was I able to do this? I’m going to give it at a very high level so that you are able to get into the details later, okay?
First, as you can see, this bot is able to understand my language and it is able to respond accordingly. Now, the first step is since it is an authentication mechanism as a part of this bot, you need to go to the authentication and you need to ensure that an authentication layer has been set to this particular bot so that it can challenge the user to authenticate and based on that authenticated user, you can personalize and provide the details. Okay?
Here are some basic steps, okay? As you can see, I’ve given a connection name called Bot Connection. I have some template information that has been given here. First, let me show you my blog, which I strongly recommend you to look into to know more about this configuration.
I’m just going to look at my blog post here. Okay. The first step, as you can see, that I’ve given the information that is needed here. I’ve given all the information that is needed to fill this particular bot, okay? Fine.
I’m going to look at giving you an idea for it so that you can understand this. The first is you need to say who is going to do the authentication. Naturally, it is going to be Microsoft Identity, the platform which is connected to Azure already. Okay?
The next step is you need to provide the client ID of the particular app. What do you mean by a client ID? I’m going to show you how I created the client ID [inaudible 00:31:32]. Okay. Let me go back to the homepage.
This is the Azure portal where I’m going to add an app registration. Okay, so the reason why I’m creating an app registration is quite simple. I need to tell that the virtual bot that I’m about to create will have access to the profile information of the user who is logging in. But why should I have the profile information?
The answer is very simple. Number one, though I might be authenticated, I need to know your email address so that I can use that email address to pull the projects for which you are owning. I need to know the projects to which you are having tasks, right? So, [inaudible 00:32:30] you need to give some access to it.
I am using this option which is the app registration. Okay? I already have an app that I’ve written, which I’ve used but you can use a new registration option here to create that app. I’m just creating the app registration. This is something which I’ve already created, okay?
When you’re creating the app registration, naturally it is going to give you the client ID. This client ID is what you see here. You can see the client ID has been given here. The next step is as a part of the authentication and the authorization, it is required you need to provide some basic information like what is the end point for getting the access token? What is the endpoint for authorization? Okay?
If the token is expiring, how do I get a refreshed token? Right? So, you need to get this basic information out here and as a part of this particular app registration, you also need to create your secret key. I want you to take a look at this.
I’ve created a key here, so this is the key that I’ve used here. The purpose of creating this secret key is to help the Microsoft Identity to recognize that whether the right app is authenticating and authorizing, is requesting for an authorization without authentication. Okay?
During this particular process, you need to let Identity know what is the permission access this app can have? Here, I’m telling that I can give the user read permission, which means if I’m logging in [inaudible 00:34:30], which means that the Azure bot would be able to access my profile information which includes my email address also.
Now, with that basic information, going back to the bot, once you’ve configured this, you can start creating a topic. Your bot can support multiple topics, so I just want you to take a look at the first topic here. This topic is nothing but your flow. It’s more like a flow where you are ideally going to have some triggers or some keywords which is going to initiate this particular topic.
As you can see, I’ve used only one trigger phase here which is high project bot. Even if you say project or project bot or high project bot, this particular topic is going to be triggered. I’m going to go to the authoring canvas here, okay?
Within this authoring canvas, I know what my trigger [inaudible 00:35:37] is. I’ve added a message here. The way you can add a message is you can just say show a message or you can say call for action or you can ask a question. Here, I’m just displaying this message. That’s what you saw before, okay? You can just take a look at it. Okay?
Then it is prompting me for a sign-in card. When I click on login, you will have seen I was logging in as an administrator. Once I have been logged in, what encapsulates this, it takes care of using the client key, it takes care of using the authentication and the authorization endpoint to get access to the auth code.
This auth code is going to give this particular bot unique permission to access its email address. If you are reading the email address through the Microsoft graph API, let’s go one level down. Okay? Here you can see that I’ve added some template conditions here.
It says that if the login is equal to true, continue. Okay. Once you have logged in, I’m just using a small action here. This action is nothing but… You can ideally create a flow or you can ideally use some of the existing flows that you’re already having. I just want you to understand how I created this flow in this case. I just want you to take a look at this.
Let’s go to this flow. This is a flow that I’ve already created. The purpose of this flow is it is going to get the project ID and the project name of the person who logged in. Okay? Do not [inaudible 00:37:45]. As a part of the action, I have supplied the auth token here which means this auth code token is very, very unique. It gives unique access to the logged in user, okay?
So, I’m going to have that as an input here. Let’s take a look at that. Okay. You can see that this is the token that I received. The next step is I’m using an action here which is the [HTTP 00:38:29] action. I’m using an HTTP action saying that I need some profile details about this logged in user.
So, I am giving the [inaudible 00:38:38] graph.microsoft.com/310/me. The value that I’m providing here is a token that has been passed to me. The moment this HTTP request has been sent, you get a response back as adjacent response. I need to pass this adjacent response but I’m more interested to understand what is your email address. I’m just using the user principal name which is going to give me access to your email address.
I can also use this particular attribute of [inaudible 00:39:14] which is the given name here, right? Once I have access to the given name and your user principal name, the next step is I can go to CDS, the common data service.
I can say that I want to list all the projects where the project manager’s email address is same as the user principal name. The moment I do it, this particular step is going to only have access, return only those projects that I’m having access to.
The remaining steps are all quite obvious. I just need to [inaudible 00:39:54] all of these projects and return it back to the bot. I’m creating a variable out here called Projects and I’m just looping through the records that I returned by this particular step.
So for each value that has been returned, I’m just going to open it as a string by using a name value [inaudible 00:40:16] like the project name followed by the project ID. Finally, I’m returning this value back to your power agent. Okay?
Moving back to this agent, now you see that the projects that you see here is the output. It is having the text, so if you go back here, after I have logged in, you can see this response. Hello, you are managing so-and-so projects. This text actually comes from the flow that we authored before.
As you can see, it is quite easy for you to author your bot using Power Virtual Agent. [inaudible 00:40:59] it says you need to have some simple fundamentals of how to do your app registration and your portal and you also need to have some experience in writing some basic steps in Microsoft flow. Okay?
I hope that this particular webinar would have helped you to understand how the data in CDS can be expanded beyond the project for the web, where you can look at the data through Power automate, you can look at the data and build some interactive chat bots and finally, how do you build a data warehouse by using the Power BI data flows.
I would strongly recommend you to look at these webinars as well as follow my blogs because my blog is having detailed instructions on each and every topic that I covered today.
Meanwhile, if you do have any questions about what has been covered today, you can just add it as a comment to the recent post that I had in MPUG so that I can get these things answered. Thank you.
