Webinar Recap: How to get and see Project Information in Power BI

Please find below a transcription of the audio portion of Collin Quiring’s How to get and see Project Information in Power BI 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.

Kyle: Hello, everyone. Welcome to today’s MPUG Webinar, How to Get and See Project Information in Power BI. My name is Kyle and I’ll be the moderator today. Today’s session is eligible for one PMI PDU in the technical category. The MPUG activity code for claiming the session is on the screen now. Like all MPUG webinars, a recording of this session will be posted to MPUG.com shortly after we wrap up the live presentation today. All MPUG members can watch these recordings at any time and still be eligible to earn the PDU credit. All the sessions you watch on demand can be submitted to your webinar history. The live sessions you attend are automatically submitted.

Kyle: Within your history you can print or download your transcripts and certificates of completion, including the one for today’s event. You can access this by logging on to MPUG.com, click on the my account button, and then click on the webinar reports link. If you have any questions during today’s presentation, please send those over at any time using the chat question box on the go to webinar control panel. We do plan to answer those for you throughout this session today.

Kyle: All right, we’ll go ahead get started. We’re very happy to welcome back Collin Quiring today. Collin is the Managing Partner of EPM Strategy and has over 20 years of experience in Project management, resource management, product development, systems administration, reporting, and training. He’s a technical expert in Microsoft Project Online, and Power BI. He has worked with all aspects of the tools, from installation, to configuration, to daily administration. He holds the PMP certification, along with many other certifications, as well as an MBA. So with that said, Collin, welcome back. At this time I’ll hand it over to you to get us started.

Collin Quiring: Okay, great. Thank you. Show screen. I have to pick the right screen. I think it’s … I just have to find the right one. There we go. All right, I have three screens, so I just want to make sure I’m sharing the right one. You should be seeing the header. Is that’s true?

Kyle: Yeah I see your blue and green PowerPoint.

Collin Quiring: All right. Very good.

Kyle: Perfect.

Collin Quiring: All right, so thank you. All right, so I only have a handful of slides here, then I’m going to… It’s almost all demo and introduction, or life work. So just a quick introduction of who we are, who I am. So I always like to know who’s talking to me.

Collin Quiring: So EPO Strategy. We start out a Project management company basically doing one thing, Project management, specifically with Microsoft Project as the tool. One of the big things that’s always asked for is, “Give me my reports. I want to see reports. Then I want to combine it with other things.” So what’s happened is, we’ve just naturally morphed into also being a Power BI organization because we found when Power BI came out, we were one of the first partners to get on board and have really found it extremely informative and effective for reporting on Project tools.

Collin Quiring: So that’s part of why I’m doing this today, is I want to combine the two worlds of Project management, Microsoft Project, and Power BI. So we are now a two trick company. We do Project management and we’re partners with PMI, partners certified in Project with Microsoft. But we also are certified with Microsoft now in Power BI. We do all things Power BI, including non-Project things because everyone wants data from everywhere. So that’s what this is. We do all things Power BI for everyone, including diads, the dashboard in a day. So in all things Microsoft Project and Project management.

Collin Quiring: So who am I? Well Kyle did the intro pretty good there, so we don’t need to spend much time on this. Just wanted to mention who am I, where I come from. I’ve been doing Microsoft Project for a long time. Wrote the book, now it’s already nine years old I guess, but the Mastering Resource Management. So the concepts are still the same. That luckily hasn’t changed much. The screens look totally different now, but it’s the same concepts of resource management. That’s where I’ve hung my hat. So then part of my experience, obviously Project management and business intelligence.

Collin Quiring: I want to… Oops, my quick foot. So what we’re doing today, I just want to give a little highlight on how Power BI is designed. The first thing you do, is you access the data. I think that this is one of the things that a lot of people, consultants, people that help others, and trainers, they gloss over this. That’s really the heart of today’s presentation, is about accessing the data. To me that’s a critical piece. Like I said, that so many people just skip, they go “Here’s how we modify the data.”

Collin Quiring: Well, first I have to get to it. That’s not always quite as intuitive as one might think. Now I will say, Power BI has all sorts of built in connectors, all sorts of ways to get to data. There’s a Collinism, it’s not a Microsoftism, but my statement is, “If you can see it, you can get it.” So, even if you’re seeing it on a website, you can get it. You can get that data. Note what I didn’t say was, “It’s the easiest, too quick thing you’ve ever done.” A lot of it’s easy. A lot of it use the connector Power BI. Some of it, like today, is fairly intuitive if the you know the string. But the other things, sometimes you have to go and scrape a website. But if you can see it, you can get it. But that, to me, is the critical piece and that’s what today’s about.

Collin Quiring: Then what you do when you get the data, is you clean the data. You make it more usable for you. We’re going to touch on that today just to show how we can modify the data a little bit in Power BI so that we can have a little bit more meaningful report. Then just the Power BI mindset. You match up that data. You put it together with other items. We have a lot of clients that do that. I’m going to show a sample of that coming up where we’ve taken Project data and external data, and then you explore that data, see what it looks like, visualize it, and then the key thing is we share it.

Collin Quiring: So for us, for the focus of today, it’s getting to Project information, then how do we share that? How does it look? We can make an Excel looking table in Power BI. And, it may be part of the sub people, but man, the visualization and being able to share it, is so powerful when you can show where you are with a picture.

Collin Quiring: So all that said, let’s talk about the access. That’s, like I mentioned, that’s the big thing for the day. So there we go. Access. So Project information. We have a few different locations for data. The first one being the desktop. This is not the normal, it’s only showing one Project. It used to be, I’m dating myself a little, but it used to be we could get data directly off of one product on our desktop, relatively easy in Excel. That’s no longer relatively easy.

Collin Quiring: You can, however, on your desktop file, if you just have one, you can export your data preferably to X amount or to XM Excel, and you go through a mapping process. We’re not going to dig into that today, but, then you can use power BI or Excel to report on a single Project. That’s a little below scope and out of scope for today. I will also talk about Project server, which is becoming less and less common because that’s the in house version. There’s fewer and fewer of our clients, and fewer and fewer of new installs, of Project server coming in house.

Collin Quiring: Now when we’re in house, we do have the most data available to us because we have that little table structure sitting right there, and you connect to it directly, either with SQL connector or an OData connector. So again, not as common anymore, but it is a nice way in that. The key point there, and why I put it there, is the most data is available. Basically, if it’s an in Project server, you can totally get to it. It may not be intuitive and we have to understand the database a little bit, but you can get to it. Which is nuts.

Collin Quiring: However, we’re in the world of the cloud now. We can get most of our data. Most of our installs are through Project Online, which is the most common and now just recently is they’ve come out with what they call, Project or Project for Web. That’s the brand new version. It’s not exactly Project Online. It is a new version. In fact there’s a webinar next week, Tim Runcie is doing, I think it’s called, They Finally Did It, A New Version of Project. They’re building it. The good news, bad news is Microsoft is building Project for Web from the ground up. So, the good news, it’s new and fresh in that it doesn’t have all the legacy issues. The downside is it doesn’t have everything in it yet. Right? Like Project Online, or what we’re used to from a Project Server. So, that is the new tool, and I will show you how to connect to it. It’s even less intuitive than most connections.

Collin Quiring: So, the way you connect to Project Online is through the Azure data. You connect to the data with OData and it’s an Azure database in the background. Probably for web, it’s actually common data source data and we use a dynamics connector. Now, I’ll be sharing that here in a few minutes. So, this next screen, this is the one that’s got kind of the goal to it and I’ve put a couple of documents there and like Kyle said, these will be available so you don’t have to furiously write these down at the moment. The recording will be available.

Collin Quiring: But the two that I’m going to talk about today and the one I’m mostly going to talk about is the Project Online, but I do want to share the product for Web Connector as well. So, the way that we connect to Project Online is we use the OData connector and what we have to have to connect is we have to have the PWA site URL, and that’s going to be my demo. And then the key is we have to apin literally the underscore API slash project data to that URL in Power BI.

Collin Quiring: And again that’ll be my first demo here in just one moment. And the project for web is less intuitive in my mind because you have to know and most just straight up report writers, I don’t think just know this, your environment name, your region and then .dynamics.com. There is a template, there is a Project Online template as well. And I’ll be talking about that and there’s the URL for how to connect and it has a link to that template, and it goes through how to find your environment name. Again to the demo. I’ll go through that just a little bit.

Collin Quiring: So, those are the two big ones we’ll be talking about today. So, just a quick, I’m going to go to the demo now, but just a quick one. I mentioned a minute ago about visualization. I just want to talk about this for a second before I do the demo connecting. This is how, I mean visually, and I grab some information here, about one client that I’ll be showing you more detail here in a few minutes, but this is claims by project. So the biggest fish is your biggest claim. So you can eyeball this in a hurry and look at the big green one here and say, “Okay, that’s got the biggest claims against the product.” The way this client does is they have projects that they then continued.

Collin Quiring: We know from the PMI world product has a start and date. Their end date for the work on the project ends, but they do accounting against it for a while after that to see if there’s any claims coming back on parts and manufacturing and all that, and then they track that.

Collin Quiring: You can also claim by location. Quick story: this is a company that only sells in the United States, but if you look, there are some claims in other country, in Canada. And so we did some math for them and said, “Okay, what’s the percentage of claims you’ve got?’ And this means that people in Canada are buying their product, taking it across the border and then doing claims. So, let’s just make life easy, say it’s 10% claims. Well, that means they’re selling. If they have 10 claims, that means they sold a hundred units to people that took them to Canada. Right?

Collin Quiring: So, we were able to talk to their marketing department, sales department, and say, “Hey, what do you want to do about this? You have sales up here in Canada.” And the decision was to beef up the American locations right there rather than go into Canada officially. But that was just data that came from the claims department. So, earlier in that circle, when we talk about visualizing and sharing, I mean this came out of a project, and so we had all the project data and then we had some claims data and then we went, “Oh look at this, we have a new market potentially.” So, I’ll get back to that context information here in a minute.

Collin Quiring: So, here we go. I want to go to the first spot here, the first thing I said in that slide about going to Project Online, with my Power BI is you need to know your URL. So, this is one of my URLs. And I’ll go to the project center, just to show you that there’s some data sitting there, but this is what I’m intentionally going to show this, is, this is by site. So, this one is PWA. I have one tenant, but I have multiple sites. This one is demo. That’s the one I want to use. So, and I’ll just show you some product information here and some… this is important for here in a few minutes. Notice I have cost indicator, sponsor indicator and location and a few other custom fields. All right, so those are unique to this instance in my tenant.

Collin Quiring: So, all we have to do to get this data from this project site, this site called demo into Power BI is … and I’ve got, this is the new ribbon look, this is the new December update. So, if your Power BI doesn’t quite look like mine, I have the latest and greatest, the Power BI, just as an aside for those that might not know. Power BI is updated monthly and the service is updated weekly, and Microsoft does a great job on powerbi.com on their blog of explaining all the changes every month. That’s just a little aside in case you’re looking at my screen and seeing it doesn’t look like yours. That’s all right. I’m going to move the filters out of the way here.

Collin Quiring: So, first thing, in our diagram: access. First thing I want to do is I want to use “get data.” So we’re going to go here to get data. And again, this is part of the thing that so many people don’t … it’s like, “Okay, what do I do now?” Right? Well this listing, like it says, is what Microsoft considers the most commonly used … and we’ll use the more buttons later … but most commonly used data sources, right? So Excel, other Power BI, SQL, CSV files, text files, the web. I mentioned that earlier. If you need this to do some [inaudible 00:16:53], and OData, and this is a case where we’re going to use OData.

Collin Quiring: So, I’m going to select OData and this is where you kind of have to know, right? It doesn’t tell you really what … it gives you an example here. Oops, it went away. There it is. So, that’s an example that is not applicable to us. This is why I said a second ago. You have to know your instance name, right? So my instance name is up there with the demo in it. So, I’m going to paste that here. And then as it say in my slide, you have to know your API slash project data. All right?

Collin Quiring: And so now the system will know what to do, and it will [inaudible 00:17:43]. So, this is kind of the golden nugget here that you have to know, right? That just isn’t intuitive in any way. You just have to know that. And, I mean there’s plenty of documentation, but sometimes you don’t know what to search for, so this is part of, that’s part of why I want to do this. We have so many clients say, “Well great. How did you get to this? How do I get to this? How do I modify this?” And this is where, I mean this is the golden string for Project Online.

Collin Quiring: And then when you say, “Okay, we’ll get the connecting,” hopefully I’m signed in to the right user here. And that’s one thing to notice up here in the top right, make sure you’re logged in with the right user ID that has permissions to that site. Now, probably most of you only have one site and maybe two, PWA and test. But as a consultant, of course we have the VP of strategy, I don’t know, top of my head how many sites. And then of course for clients I have to have the user ID and sign in for them. So, that’s a critical thing because when this opened it checked my permission. So, then this is the list of all the tables and if you’re not familiar, that’s okay. With the table structure, that’s okay.

Collin Quiring: There are no diagrams out there. You just kind of have to know because Microsoft reserves the right to change things at their will. So, for those of us who’ve been in Project for a long time, we used to have the data diagram and so we kind of remember it. But the big thing, the big common thing is we’re going to get projects so we can get our project names. They’re alphabetical. And when you click on one here, you’ll see the little preview here. It’s just showing a short preview. I’ve got more products than just three in that, in that database.

Collin Quiring: Then what we’re going to do is I’m going to grab tasks, I’m going to grab assignments, and I’m going to grab resources. So, I’m just going to go back and get four, four tails for right now. Nice and simple, and I’m going to say load. And so now, I’ve gotten out there, I’ve gotten my data and it’s going to put it together. Now there’s one thing I purposely didn’t grab, which I’m going to grab in a second. But I wanted to show a way to easily go back.

Collin Quiring: You don’t have to go back to that whole process and get data again, because I’ve already done it. This just takes a moment to load in the files from my site. And then it detects the relationships automatically, which I’m going to talk about in a moment. And now, on the far right I have my four tables or queries. And if I drill down on any one of these, I’ll see all the fields available. And this is where it’s slightly different than if you had this in house, not every, every, every single field is here that you might expect to be here. And not every table. So, but 95% of the fields you need are here.

Collin Quiring: So what I want to show now is what the system automatically did for us. It went in and it made relationships. Because this is the other part about getting and adding data, right? You make your own report, is you have to have an understanding of what keys are. And so the system, because this is a common thing and we did that string, API project data. The system, the Power BI, understood what we were doing and it built some relationships already. In case you’ve not seen that, I’ll describe it personally here. So it’s saying, “Assignments project ID is equal to projects project ID many to one.” Makes sense, right? In one project with a hundred tasks we’ll have multiple instances of that, multiple roles of that in the assignments table. Because that’s just one that I grabbed now.

Collin Quiring: So now what I wanted to show, and we’ll come back to making quick little table here. Just a simple table. So what I wanted to show now is now we realized that what I didn’t grab as to, “Oh no, I don’t have a time component”. This is one of the biggest things in all reporting and all power BI, is the time component. Because you may say, “Well, I want my projects. I got my resources, I got my tasks, assignments. I got all that information. I combine all that information”. And then the question always comes up, “Hey, show me how much work is by this month. Show me how much work by last month”. Right? And it goes on and on. And if you don’t have a time component, it’s the system doesn’t quite work right. So we always, always, always do a date time given, a date time table, date table, whatever you want to call it.

Collin Quiring: And there’s plenty. I mean, if you go out there and Bing “Power BI date table”, there’s all sorts. There’s not a standard in the world. And that’s, people always ask us, “Why not?” And that’s because dates aren’t standard. As much as you might think they are, you have… Right, okay. We have a calendar. Well, in the calendar then you have a fiscal year. What’s your fiscal year? Right. And then there’s your client’s fiscal year. Right. And that might be different too. But we also have just in dates, I mean you kind of have to go to your accountant. Are you 454? Are you 13 four week periods in a year, right? Are you just days? Day one through 31 of this month, and one 30 next month. Right. So there’s not a simple standard date. You kind of have to figure out what your date table is.

Collin Quiring: So all that said, what I just did a minute ago is I want to get data. I went to a data feed, right? And then I had to paste in my URL again and I’d have to put API project data. You’ll get that one table. But I want to show you this little trick, that a lot of folks don’t really know about, is right here “recent sources”. So I don’t have to know all that because it’s already in here. So, I just click on that and I’ll get that same list of tables. And this is where Microsoft has been friendly to us and they built a time set date table for us already. And you load that in. So, I just wanted to do that for a couple of things. One, to show you how you can use recent sources. And two, we always need a date table. Kyle, any questions I need to handle real quick before I move on?

Kyle: Nothing on what you’ve covered so far. But just a reminder to anybody that may have joined us late. You can chat questions over to us and we’ll take those live during the session.

Collin Quiring: All right, great. So, this just takes a moment to come in. I’ve got a standard date time set on my own that I use that I’ve created over time. And so all kinds of functionality. But you always seem to need that to do what I said, figure out your sales. Or in this case, how many hours of work do I have coming up next month for certain people? So now I just wanted to show a little bit, right? We said we wanted to get to it. So now what I want to do is I’m just going to make a very simple visual. This is not, this is about getting to data and modifying it. This is not about making it pretty visual, which it’d be nice to do, but so I’m just going to grab some fields, right? And what I wanted to show is I’m going to grab the project name out of projects. And these are alphabetical in here. So it’s project name. Once I get there, there it is.

Collin Quiring: But then almost like I had a plan, you remember when I was back here I talked about these custom fields, right? Cost indicator, sponsor indicator. Well, okay the field is renamed your product, but custom fields are usually at the bottom and there they are. Sponsor indicator, sponsor, which I know the indicators not showing it that way, but technology owner, right? I can have, now I can see my custom fields. This is a real simple way to whip out a quick report, right? It’s not necessarily just sitting this way somewhere else. Size of the project, medium, large. And obviously when you look at this, you can see these are not required fields because I’ve got blinds. That’s another thing. You’ll look at this and think, “Oh no, I’ve got a data problem”. No, I’m pretty sure that that’s actually not filled out on the other side. So, maybe I made it a required field after the fact. Here’s my variance to deadline percentage. All right, so there’s also some other information.

Collin Quiring: I’m going to grab… I grabbed sponsor, manager. I want to do manager, project manager. Just manager. Okay. All right. So one of the things I was talking about, people who always want to know. So I’m going to go up here to Tasks and I say, “Okay, show me the finish date”. Right now all I’ve got is project level. So once I’m going to add task name, I’m going to blow this report up significantly. Right? It’s going to give every task and all. So that product, those are my tasks. But then I want it to say putting in finish date. This is another thing I just want to say. When you bring in a field like a date field, it breaks it up into the hierarchy. Which is great if you want to use it that way, but I’m just going to use it as the date. All right. And then I’m going to do task duration, so that’s my hours, right? 40 hours, 280 hours. That’s the duration of that task.

Collin Quiring: Now I’m going to say, “Okay, who was assigned?” Right. I’m going to say resource name from the assignments table. And there we go. This is the other big thing about accessing data in Power BI for project is this just seemingly developed, but not really. It’s correct because, this is what I showed you a minute ago, when the system automatically did this, this relationship building, it didn’t build it wrong. It just didn’t necessarily use the hierarchy, or the connection strings that I would have preferred. In fact, they’ve built one here. But if you look, I have assignments to resources, task to project. If I link assignments to tasks, then I will be able to have my full link. But watch what happens.

Collin Quiring: When I link this it says, ‘Wait a minute. You can’t do that. You’re creating ambiguity”. That’s okay. I’m going to remove this one, and add this one. So I kind of did that fast. Let me just explain real quick. What I did is I’ve got a link now to link projects to tasks to assignments to resources. So I’ve got projects, project ID to task ID. I’ve got task ID here to assignments, and assignments resource ID to resources. So, this is part of the art and understanding the data. So getting it is a big key. And then there’s this piece, right? So, now I’m going to close that. And, there we have it fixed.

Collin Quiring: Right? It fits as we expected. Now I’ve got all my information right here, and so I just wanted to show that, that we’ve got all this information, and I wanted to show you another thing before I show the other demos, the other screens. Is the next big question we always get is, okay, we’ve got this access, and show me the API project data, and now I want to do this different. I, for whatever reason… Just play along because it’s a quick demo. I don’t want full names here. I want just the first names. That doesn’t exist right now. Right? We don’t have that in our assignment table, and this is where part of the power of Power BI comes in, where we can actually go into the query and add this, where we haven’t affected our data coming out of projects.

Collin Quiring: We are only modifying how it looks in here. And that’s what’s wonderful. So what I did is I clicked on transform data, which if you’re on the version that’s way old… it’s got to be two weeks old now, since I’m on the latest and greatest. Obviously, I’m joking… Two weeks old, or more, it will say edit queries. And so I’ll go to assignments here, and it’s not necessarily a session on how to edit a query. I just want to show. This is a common question people have ask, “Okay, now I want to take what’s there and I want to modify it.” This is where we would suggest you go to a dyad event, a dashboard in a day event, or you get some training. There’s plenty of online training, there’s Guy in a cube, has all kinds of stuff on YouTube. That’s a Microsoft guy.

Collin Quiring: So I’m going to go to my resource named column. I’m just going to jump there. And so I’ve got the resource named column. And then all I’m going to do is I’m going to add a new column, and I’m going to say extract everything before the delimiter, in this case a space. So I’m going to actually hit space bar. Say okay. It’s going to create my new field away on the far right, and I’ll just rename that as first name. Okay, and just like that, I’ve created new data if you will. A new column that doesn’t exist anywhere else except in my report, which might be the way you want to share this, right? So I’m just going to say close and apply. And what this is going to do is it’s going to update that table. And now I have a brand new field. And again, as I said earlier, these will be alphabetical. So I need to go to the F. I think I called it first name, right there.

Collin Quiring: And if I add… Make sure that… In there, and I add first name. There we go. Okay. So I just wanted to show this cycle. I’m going to get to some other more advanced demos. But the other nice thing is every step we just did is recording, so we can come back and figure out how we got this. Or if you’re like us, you have multiple people writing different reports and sometimes we cover for each other. I’ll go look and see, “Well, what happened here? How did you get this?” Right? And I can go look. In fact, all those steps are listed. I just happened to be working on this for something else this morning. Here’s one with quite a few steps, where we’re changing things, and merging and adding. So you can get pretty complex in how much data you put together.

Collin Quiring: And I’m going to get to time allowing. I’m going to get to another demo here in a few minutes. And I’m also going to show… So this, I’m going to do the project for Web Max, and then I’m going to show you in the project for web, the templates that Microsoft has prebuilt for us. So this is just a simple, I’ve grabbed some stuff, I’ve put it together in a table. I added some fields, right? I’m going to show you another version where we can combine other information. So I’m just going to create a new power BI for a moment. Oh actually, now I’m going to use the template.

Collin Quiring: Well no, I am going to use a new one. All right. Okay, so now, the other item, the new greater, better than ever project for web. So let me show you my project for web. Grab that screen and move it over. This one. So this is the new project for web, or project, and it combines things. Now it combines everything to the web for you. Now, I’ll be honest with you, I have an open ticket right now with Microsoft because this appears to me to be designed for one and only one instance. But for folks like me, when I have multiple instances, I want to be able to separate by instance. My thinking is that Microsoft wants it one per tenant, which is okay. That might be their answer when they get back to me, but I’ve had this taken over for over a week now. I think they’re struggling with it too, exactly how it works.

Collin Quiring: So, in part is, because a minute ago, I was all concerned about making sure you had the right, when you’re [inaudible 00:35:47] online, the right site. Well here, there’s no site information. So that’s interesting to me, and if it is by tenant, that would make sense. But I don’t always want to share everything across my tenants because I have multiple sites, so I’m working through that with them. And I think they’re working through that with them, because they’re having a hard time answering those questions.

Collin Quiring: So that said, the way that you connect here, and I will go back to the slide deck very quickly for this one, is you have to know your environment name and the region that you’re in. Basically what server set are you using for dynamics, and for North America it’s… Well, to be honest, I don’t remember now. I think it’s CRM. But this website here gives you all the standards. But if you’re like us, we have multiple stuff, so we have to go find where this was. And to do that, I had to go to the admin for us, and I had to actually find out, what is our link, and I have that sitting right here to show this to you. Oops, I’ll do new.

Collin Quiring: Okay, a new window, because this is really not intuitive. So this is ours, right? You have to have the name and where it is. So if you’re using project for web, you have to know that little tidbit and that might not be easy to find out. You may have to go to old 365 admin for them to tell you. And that link in the PowerPoint slide tells you exactly where they’re going to find that, so I won’t get into that. But so, same idea, you go to get data.

Collin Quiring: Now this time, this isn’t the most common, right? Common data source is not yet the most common. It’s getting more popular. And I can see Microsoft putting that into the common list here at some point, but not today. So we’re going to go to more, and this is all those connectors I was mentioning earlier, where that access on that slide, so you can access data. Well these are all the built in connectors.

Collin Quiring: So if you were to go to Salesforce objects, you can hit this and it will have a preset look and a preset customization, where it already knows the relationships, already knows how to get information, same as Facebook, et cetera, et cetera. And this is how we have clients that have Microsoft project information combined with sales information, combined with claims information, right? All this different information, suddenly is put together, and you can see the life of a project with other data. And so we did this project and we’re selling [inaudible 00:38:58], and how much did we advertise?

Collin Quiring: Right? So you have the product information, but as we know there’s always more, and I’m going to show an accounting screen here in a few minutes. So in this case, I know it’s common data service. And so I connect to that instead of old data. And then I know this is my region, so that’s mine. So yours won’t be this number. So let me just try to just copy and paste that. And then what happens though, is I do get once again, permissions based, and make sure you’re signed in to the right user, and then this is a different table structure. If they call it entities here, which is the common data service language. And it is similar in that they have something called projects. So just like before, if you look at it, you’ll see… Now in this case, I don’t know, I didn’t spend much time on it.

Collin Quiring: In this case, I really only have one project with two tasks. And, hit refresh. Oh, wow. It’s going to make me do it all over again. Okay. Shouldn’t have done that. Should have left it alone. So, give it a moment. I just want to show, I’ve only got one project in here, called Column Project Web Project, and I’ve only got two tasks, and I’ve got two resources. So, I just wanted you to see what it looks like, because in this case, there it is … I mean, I’ve got a real simple … I designed this exact report, showing you this. So, in this case, when it’s saying, the samples are at one row, that is correct. So, it’s the same idea. I’m going to grab project, I’m going to grab the resource assignment, I think it is.

Collin Quiring: Like I said, this is just like I said a minute ago with the other version. You kind of have to know the table names. And to be honest, I don’t fully know them yet. So, I’m learning them as well. I don’t think this is the tasks. Nope. I want MSDN task. I’m not going to spend much time trying to go through this. There it is, project tasks. So, there’s my two tasks. And then when I say load, I’m going to get the same thing. It’s going to put those in here. So, it’s a little bit different naming structure. I’m going to be honest, I haven’t dug into it a lot myself yet, because I’ve got higher system questions. But, same idea, right? I can come in here, I can grab from my project, I can grab project name … Oops, not that. That’s modified date.

Collin Quiring: Well, there’s the project managers like I had before. And your customer information will be down here, just like before. So, like I said, I don’t really have a lot of knowledge on it, I just know how to get to it. However, here’s the thing that I want to show you that Microsoft has done, knowing there’s people like me who don’t necessarily know the table structure yet, is they have built a template for us. And that’s on that webpage, that’s the same … It’s got the link to it. But here is a template that they have built for us, so we can go ourselves and look at that information. So, you can see, it’s got my one project, it’s got a link to it, got the start date, got the effort, what’s overdue already, what’s late. I’ve got two tasks and they’re both in trouble already.

Collin Quiring: So, this is not my point here. This view is not to show you how awesome this visual is, because I don’t have much information, but to show you that there’s a template. So, if you’re using Project for web, and you’ve got 30 projects, you can suddenly come here, and you can see all sorts of great information. They have the timeline, they have milestones. And again, I don’t have much data here, so it’s not too informative. But there’s a task overview, some other information. But here’s the bit, and this is where I’ve helped clients say, “Okay, start with this, go to the transform data, the edit query on perhaps your version. And go look at exactly what did they do. And how did they change it?” So, if you look at the project names, here’s all the stuff they did. The stuff they did and how they released it.

Collin Quiring: And this is where we could do the exact same thing we did earlier. I’ll go to assignments, the exact same thing. Say okay, this is nice, but I want to go and I want to break up my resource name. Give it a name. Again, the field names are slightly different. I have to figure it out. So, oh, they removed it, see. So, this is the case where I’d say, “Oh, nope, I don’t want to remove that. I want it back in there.” I guess it’s that one, that one. So anyway, you can mess around in here and get to the spot you want.

Collin Quiring: So, that’s the Project for web. I know it looks like I’m stalling a little bit because it’s brand new, right? It’s less than a month old and I don’t have this learned, all this stuff memorized yet. I think resource needs [inaudible 00:44:40]. So they’re using the new words and stuff. So it’s a little harder for me, personally. Some people probably have this all down already. It’s new to me and I’m learning. But the key was, how do you get there from here?

Collin Quiring: So, I’m going to close that one and I want to show you the other. Nope, nope, nope. I don’t want to show that. What I want to show is the other template that they have for project on one. This one might be a little more intuitive. I have more data in my Project online sample, but this is the same, and this template is available on that document URL on the PowerPoint. And, this is a case where we’ve had clients that do this, they download the templates and then they modify it a few pages. They’ll say, “Okay, I want it to look like this.” Or, “I want … This is just one where all they did was change how the Gantt charts look for resource availability, and we’ve just got some different things we’ve done here where we’ve added. This is also where you can do this to add other information from other locations.

Collin Quiring: So I want to show this next sample here for what we’ve done. There it is. This isn’t a high visual, but I want to show this is a case where we have project information coming from Project online, and we have accounting information… Change my dates here, that should help me with some data over here. But, some of this data is actuals… Let me go to the cost step, there we go. This is better. Some of this is actuals coming out of Project, and some of this is coming out of their accounting system.

Collin Quiring: Because, like in this organization’s… Again, this is all sample data so don’t get too excited. But, the way they do it is, the project managers and the projects manage their project and they manage the cost that they know about, but there’s invoicing going on. And, there’s payments going on, and accounting is taking care of that, not the project manager. The project manager knows we have X number of hours, and we’ve hit this milestone. So please bill the customer for our work.

Collin Quiring: And so, accounting knows that. But we also know in the projects they might finish something November 10, assuming that’s a work day, and turn that into accounting. Well, if accounting only does stuff on, let’s say, the 1st and the 15th, well then, accounting only will do it on the 15th, and if it takes them a week to do the invoicing, then it will be the 21st until that goes out. And if it’s a 30 day payment, now it’s middle, late December before the payment comes in.

Collin Quiring: So, there’s actually two sets of information going on. We’re getting income in one time period for costs incurred in a different time period. And for the Project manager, for lack of a better word, doesn’t care when the money comes, right? They care about where their project is. But accounting cares about where the money is. And so we combine that information because management cares about both.

Collin Quiring: And then the same thing… But this organization we’ve got, I mentioned it earlier, I show okay, we have claims. So, what’s the claim by model? That’s kind of up to the claims folks. But then this is where we get to that claims by project. Okay now, that screenshot I showed a minute ago, we can say, “Okay, which projects have the most claims against them later? Whatever the claim might be, this case, it’s monetary. Or, if it’s by parts, which parts, which projects? Give me summary data.

Collin Quiring: So, I just want to show that there’s ways now we’re combining the project information with other information. And when it’s visual like this, that’s real easy to see. There’s our big one. Hover over it, it’ll give us a number. So, now you can see… What you can see is little guys way back up in here. And I’ll give a quick story. We did this visual one time for a customer that he was saying, “Show me which projects are my most profitable.” And so we did this visual after a while because we were working with Excel and Power BI was new then. And so we thought, “We’ll just slap this visual on top of the Excel data.” It was all brand new back then. And we did. And bam, we saw these big fish, and he was able to hover and knew right away which one of his customers was more profitable.

Collin Quiring: But the other thing that happened, and this is part of the power of Power BI and visuals, is some of these fish were floating and upside down. And we thought, “Oh no, the visual is broken,” or “This new tool Power BI is not working right.” But that’s not true. What happened, what we all discovered, is the fish that were upside down and floated to the top, they were dead fish because those were clients he to losing money on. So this is the case where the visual was more powerful than the data because that data had always been there. But we as the data people were kept looking for, show me you’re your most profitable.

Collin Quiring: And yet there was another thing that none of us had thought about, and that was losing money. We just assumed that we never lost money, right? That the client never lost money on jobs. But they had a number of jobs they lost money on and they were able to figure out why. And suddenly they were able to improve their business processes. So, that was the sample we did. All right. So, I think I’ve done everything on the demo that I wanted to show. Oops. There is my contact information. Feel free to reach out to me. And like I said, this was about how do you get to the data and then how do you clean it a little bit. And use those templates and modify them as you need to. That’s probably the best starting point. So Kyle, was there any questions at this time?

Kyle: Oh yeah, we do have a couple of questions. More general, it looks like.

Collin Quiring: Okay.

Kyle: This one came in from Patrick and he was asking, can you publish data or make power BI reports available to people that don’t have access or licenses to project online, as an example?

Collin Quiring: Yes, yes. So that’s where the licensing switches, right? If I make a report that’s a power BI report and I publish it to my service. And it’s a little out of scope for today, but yeah that’s the follow through part, right? You publish it. Now they will have to have a power BI license, which is at market rates, it’s $10 a month. That’s again the market rate. Everyone’s got their own licensing out there. And, if you have power… Shoot, I forget the exact name. There’s one license… Premium! If you have the premium license, you can share with anybody anywhere.

Collin Quiring: Or of course, I say of course, you can actually embed information directly to a website. Now in this case… Whoops, let me click back. When you’re sharing it directly to a website, you are sharing it with the world. And people don’t need a license in that case. But I’ll just put an example on our EPOstrategy.com website we have an embedded power BI report right here. And it’s interactive. So obviously a hundred percent demo data sitting here when it pops up. There it is. And totally interactive. This will be a map when it comes up. There you go. So I can click on Europe. But if you watch over here, these lines will change. There you go, right? So yes, you can share it internally, I mean it’s very common.

Collin Quiring: And then, part B of that question that always comes up is, what about security? It’s Microsoft security. You can make it where a person can see one tab on one dashboard, and they can interact with anything. You can make it where people can drill down and build their own version based on what you built. So, there is security attached to it depending on what you want to do. But yeah, great question. Yeah, sharing it and publishing it. There is licensing issues to discuss, but it’s not that you need power BI licensing. All right, what else Kyle?

Kyle: Thanks Colin. Have another one here from Jacob. And it looks like he is experiencing a big hit on his PC performance, particularly with memory, when he’s merging two different large datasets. And, he was just curious if you had any recommendations there? And I guess what specs are typically needed to do this.

Collin Quiring: Well, there’s not a specific set of specs for a system because it really boils down to what you are doing. And I know I heard, you’re merging, I get that. But are you merging 700 comms at a terabyte of data with 3,000 columns of data, right? That takes longer than two columns and two columns, right? How many custom fields, how many measures, etc. Now, what they do have is they have the query analyzer. Here we are here. Oh yeah, I’m in the new version. I haven’t been in the new version. There is an analyzer. I just don’t know where it is in the new view. Anyway, maybe I have to turn it on. But, that’s why I haven’t used the new view. I just learn this, I just realized that.

Collin Quiring: But there is a query analyzer that will tell you why it’s taking so long. Well, not why. It’ll tell you where it’s taking so long to run. And so on that one then you can see what happened. There is a Ram hit, there just is. So, we do a lot of things with clients that deal with Walmart and Target and Kroger. And I mean when you’re talking Walmart data, you’re talking petabytes. They, I mean terabytes is nothing. And sometimes they even get cash registered level data for a day. And that’s kazillions of lines of data. And so, part of it is thinking about what you’re doing with it. You can also modify the query on the inbound of how much you’re going to grab. So using the query analyzer would be the first thing.

Collin Quiring: But yeah, if you know you’re using really big data, like sometimes like I said for the Walmart type stuff, we actually have a machine set up. It’s a server in our case, with 128 gig of Ram or something. Because yeah, if you’re trying to do big stuff on a laptop it’s going to crunch you because Power BI is very fast, it’s very efficient. But, even if it’s a half a millisecond per operation, if you’ve got a petabyte, a half a millisecond times a petabyte is still 10 minutes. Right? So there is something to Ram, he more Ram the better.

Collin Quiring: And then of course then you get into the whole network. If it’s going real slow, maybe your network isn’t working well. We had one client with a really bad response, horrible. And they kept blaming Power BI and we kept saying, “I don’t think so”. And we finally got Microsoft involved because we couldn’t figure out. And sure enough they had a firewall port closed inside their company that affected network time. Because it bounced off and went a different way or something. So once they fixed that, Power BI was doing great. So, there’s not a simple answer to that, but use the query analyzer. Make sure you got enough Ram, and see if your queries efficient. All right, what else Kyle? In the few minutes we got left here.

Kyle: Thanks Colin. Oh, I think that takes us pretty close to the end point here. So, I guess now I’d like to thank you for your time today and for presenting to the MPUG community. And, anything else before we close out today?

Collin Quiring: Not for me. I agree. I appreciate everyone joining in and your time. Feel free to reach out to me.

Kyle: Great. Thanks Colin. And Colin did imposed on the screen here if you’d like to reach out. You can hit that screenshot icon at the top of the viewer window and that’ll save a screenshot of this to your computer. For those of you that are claiming the PDU credit for today’s session, I will put that back on the screen for you now. All right. And today’s session is eligible for the one PMI Technical PDU. And if you missed any of today’s session, or would like to go back and review anything that Collin shared with us, a recording will be available in just a couple of hours. And you’ll get an email with a link to view that on-demand on mpug.com.

Kyle: We also have one more great session to wrap up 2019. And that’ll be on December 18th from 12 to one Eastern. Tim Runcie will join us to provide an overview of the latest project for the web release from Microsoft. I know a lot of people have been asking to see that and dive into that, so be sure to register for that session. I chatted over a link to register. It’s going to be a good one and one not to miss. So we hope to see you there and that does doesn’t for today. So once again, I’d like to thank you Colin and thank everyone that joined us live or is watching this on demand. We hope you have a great rest of your day and we’ll see you back in two weeks for our next live session. Thanks.

Collin Quiring: All right, thanks. Bye.


Watch the On-demand Recording


Avatar photo
Written by Collin Quiring
Collin Quiring is the Managing Partner of EPM Strategy and has over 20 years experience in project management, resource management, product development, systems administration, reporting and training. Mr. Quiring is a technical expert in Microsoft Project, Project Server, Project Online and Power BI. He has worked with all aspects of the tools from installation to configuration to daily administration, and holds the PMP, OPM3, MCTS, MCT, MCP, and CIRM certifications, along with an MBA. Contact Collin at cquiring@EPMStrategy.com.
Share This Post

Leave a Reply