DA-100 Microsoft Power BI – Part 4 Section 5 – Manage datasets

1. Analyze in Excel

Hello. And in this section, we’re going to be looking at managing data sets. So again, we will have a video for each of the DA Dash 100 exam requirements. And we’re going to start off with Analyze in Excel. So you’ve got your data sets and also reports up in the Power Bi service, but sometimes you might just want to do some ad hoc analysis from it. Well, you can do so now notice, first of all, we’ve got this download, and we’ve got Analyze in Excel updates. So your version of Excel will probably not have the latest Analyze in Excel update. So, first of all, I’ve closed Excel on my computer. I could click on this Analyze in Excel update, and you can see what it says. Alternatively, I can go to a dataset or to a report, and I can click on the dot dot dot next to it and Analyze in Excel. So I’m going to do this with this data set, which is no role level security. And here analyze in excel. So first of all, you need some Excel updates. To use this. You need to do a one time download on and installation of the latest version of the Excel libraries. So you can click on the hyperlink.

Tell me more about this update. It’s not a huge update. It’s about 70 megabytes, and you can see more information about it. It works out whether you’ve got the 32 bit or the 64 bit of Excel. And it’s just downloading. Now, while it’s downloading, I’ll just tell you some of the requirements. You need at least a Microsoft Excel 2010 Service Pack one or later. You need to have predefined measures in your data set. You need a Power Bi Pro license, because as I say, this is for sharing. Guest users can’t use Analyze in Excel for data sets originating from another tenant.

As we know, that’s another organization. It’s a Power bi service feature not available in power bi report server or power bi embedded. And you need a version of Excel which has got Windows. So I’m afraid if you’ve got a Mac version of Excel, you won’t be able to use Analyze in Excel. They don’t have the Get and Transform or any of these DAX models that we will be using. So now that’s finished installing, I can try and gain and go to Analyze in Excel. So I’m going to now click I’ve already downloaded these updates. You might want to just open up Excel and make sure everything’s fine before that. So let’s click on that. It’s now creating the Excel file. And now there is our file. So I’m going to click on it to open it in Excel. And here it is. So it’s in protected view. Well, any files which go from the Internet by default or in protected view.

So I’m going to click enable editing. So here we have a Pivot table. Inside the Pivot table, we have a model. Now, you’ll notice again another warning. External data connections have been disabled. So external going to Power BIS, so I’m going to enable that. And now we need to sign in. And that’s quite important. We need to make sure you are somebody who is allowed to use this data. Now, if you’ve got multiple accounts and you’re signed in to the wrong accounts, you might see the word forbidden error. So if that happens, then you’ll need to sign in again. So I’ll just put in my password, click sign in and here we go. I’m successfully signed in, so now I can use any of the measures and any of the other fields that are there. So this is a DAX model, but it started as a DAX model in Power Bi.

It’s gone all the way to Power Bi service. It’s now gone back to Excel again as a DAX model, so we can’t manipulate the model. I can’t go into Power Pivot, which is the equivalent of the model in Excel, and be able to see everything there. It’s purely essentially read only, so I can put in things. So let’s put in, say, the product key into the rows, and obviously it’s retrieving data from the internet. So this will take longer than if it was just not on the internet or if it was on local. Maybe I’ll put in instead the standard cost and have a total of standard cost count maybe. Oh, I can’t do that. I need a measure. So let’s see if we can find a measure. And I’ve looked all the way up and down this Pivot table. I’ve not actually introduced any measures apart from two maybe right at the top of those measures. Yes. So here we can see a measure. So I’m going to change it so that it is a count and I can’t do that.

So you can see very little. What we can actually do here, if you haven’t got the measure set up, you need to actually just create them. So it’s basically a use the model that you’ve got. And if the model needs more development, then you’ll just have to develop it further. As said before, you need explicitly created measures. What do I mean by explicitly? Well, quite often in a Pivot table, you’ll be able to drag like sales amount into the values and it will create a new measure count of sales amount. But here you’ve got to actually have made that explicit beforehand. So analyze in excel. It enables you to go from a data set or a report which uses that data set into Excel. It requires Excel 2010 service pack one or later, and requires a downloading of an additional addin. Analyze in Excel updates.

2. Promote or Certify (Endorse) a Dataset

Now, some data sets might be better than others, and you might want to say, actually, this is the data set you should be looking at, and what you can do is something called endorsing your Data Set. Now, if I go to my workspace and go to data sets here, we have a potential place for a badge. So it’s not actually saying that this data is necessarily completely perfect, what data is, but it is saying that this is the data set you should be using. So to do this, we go to a particular data set. So we’ll go to this category and subcategory dataset, and we’ll click on the dot dot, and we’ll go to Settings and we’ll go right to the bottom to Endorsement. So there are three levels. So the default level is just saying this is a dataset, and that is the standard.

The data set can be searched for and used by others, obviously, if they have got certain permissions. Promoted gives you a little badge and it shows it’s ready to be used by others, according to you, certified, well, anybody can just promote their data set, but it requires an expert to say, this is really good data. So Certified requires certification from experts in your organization, and then it will get the certified badge that shows it’s recommended for use by others. Now, the certified data set, this is an administration task rather than a data analyst role, and so we don’t need to go into much more detail about that. So it’s really for your data administrator to do this rather than for you.

But just know that once you’re an expert, you don’t need to know too much more about it. I’m going to promote this particular data set, so click Apply and we’ll go to my workspace and go to Dataset. And now you can see that my category and subcategory dataset now has a little badge to show that it’s promoted. And that’s really all there is with Endorsing a dataset. It’s there for you to show that it’s not just draft data or trial data or data you’re not fully checked. This is real data that others in your organization should use. Now, if you are an administrator, let’s see what you have to do. So let’s again go into Settings and Admin Portal and we go to your tenant settings.

So if we scroll down to export and Sharing Settings, we have first of all, we have an export to Excel, so you can block that if you so wish. And that might be useful for certain security groups who shouldn’t have the data exported, perhaps, but we have this certification disabled for the entire organization. Well, I’m going to enable it, and I’m going to say that these specific security groups are allowed to certify, except we haven’t got any security groups actually set up. So I’m going to apply this to the entire organization. So click Apply and you can see tenant. That’s to say the organization settings will be changed within the next 15 minutes, and 15 minutes later the certified button becomes ungrade. I can now select it. So I will certify this because I and everybody in my organization happens to be an expert. So now if we go to the datasets, you can see one is promoted, one is certified.

Now, if you did want to know how to get certain security groups, again, this is not part of the DA 100. You do not need to know it. However, you go to the Microsoft 365 Admin Center, you go to groups, you go to active groups. I’ve already got some groups set up. I add a new group, it’s going to be a security group. Click next, give me the name. So, new security group three. And here’s a description. Click next and create the group. It’s going to take five minutes for it to be set up. So in the group, once it’s arrived, you click on the group, you add a group owner.

So click at the top, add owner and this is me. And then you can add members. So view and view all and manage members. So I’ll add members and Jane is good enough to certify, but nobody else is on me. Let’s put me in here as well. And there is your security group. So it’s called new group two. So I could, if I wanted to go back into settings, into tenants settings, go down to certification and say not everybody, but I want specific security groups and I want new group number two. They’re the only ones who are allowed to certify. And again, tenant settings will be applied in 15 minutes. And then you can go to your workspace or your data set in your workspace and say I want to endorse it, promote it, I want to certify it, but most of what I’ve just said is just not needed for this exam. However, if you need to certify, you know how to do it, contact your administrator and ask them to have a look at this video. So that is all about endorsing data sets.

3. Manually Refreshing data in the Power BI Service

In this video, we’ll just have a quick look at how to refresh data sets manually. I’ve got this data set that we’ve had a look at a few videos ago, and it goes from A to D and one to four. So if I just go into Power Bi and get data, I can go into files. I can get my local file. That’s what I’m importing. Because it’s a table, I can import it rather than just upload it as a workbook. So I can import, which means I can create reports on it and use it in dashboards. Whereas if I was uploading it, I can only pin a range to a dashboard. So here I’ve imported it. I’ll go into the data set and I will just get the first heading and second heading and blow it up a bit so we can see it.

So it goes from 80 D and one to four. So we’ll save this report. So again, I’ll call this get data import. But what happens if I update this? So I now add an extra two rows and I’ll just close it just in case that causes any problems. So I’ll go into data sets and get data imported going to the menu, and there is this Refresh Now. Excellent. So I’ll click on Refresh Now and we’ll have the two extra roles and that’ll be the end of the video, except it hasn’t happened. Okay, let’s just go to Home and go back into the report, or maybe the report’s old. Let’s go into our dataset because we said refresh and just regenerated and no, it still goes from one to four. Now, the phrase refresh doesn’t actually do that if it can’t get access to the data. So how can we refresh this? Let’s say we got a report set up and then we’ve pinned it to several dashboards.

We don’t want to have to recreate it each and every time the data changes. Well, with Excel, I can just get the data again. So I’ll go into files and local files and get data imports and notice what it now says. After I click import, you already have this file. If you replace the existing file, some tiles might be impacted. So I want it to be impacted. I want it to have the latest data. So let’s click replace it. Okay, my file has been updated. Let’s go into our report.

And it still says one to four. Why is that? Well, it might take a while for everything to be refreshed. If you find this is happening and you want it to be refreshed now, then you can click on the Refresh button at the top. And now we get one to six. Now, what if a Power Bi desktop model changes? What if the data changes like we have done a few videos ago? That’s no problem. You just republish it on top and then the data set gets changed. Any of the reports get changed. But equally, any reports which you have created in the Power Bi service based on the data set eventually gets changed as well. So this is how to manually refresh your data sets. But manually refreshing them is not necessarily the best use of your time, especially if you have to do this several times a day. What if you wanted it to be more automatic? Well, we’ll have a look at how we can use schedule refresh in the next few video deals.

4. Data Gateways; Providing Access to Datasets

In this video we’re going to have a look at how we can get Power Bi Service to automatically refresh any data sets. For example, we have got this data set in Power Bi desktop which we doubled in one of our previous videos. So we just appended it to itself just to simulate what might happen. So how can we get the computer to go onto my computer, get the later set and then update itself? Well, here we are in the Power Bi service and we’ve got this data set category and subcategory, and we saw that there was a refresh now and a scheduled refresh. Let’s have a look at the schedule refresh. And we have got four different sets of options gateway connection, data source credentials, parameters and scheduled refresh. So you can see at the moment schedule refresh is not enabled, parameters we don’t necessarily need for this data source credentials, definitely not online. So it’s got to be something to do with the gateway connection.

And you can see user gateway connection is off and it says you have no personal gateways installed. And there’s also something about an onpremises data gateway bracket, standard mod. So what is all of this about gateways? Why do we need a gateway? Well, there are three basic locations where your data set might be. Firstly, you might have data sources that reside in the cloud. For example, they might be on the Azure SQL database and that’s fine. What can happen is that the Power Bi service which is on the cloud, can then go to the Azure SQL database which is also on the cloud, and just with the correct credentials which can be edited in the data source credentials. That was the bit that was grid out. It can automatically and easily get the latest data. However, there are two other sources of data, data sources that reside on premises.

So what that means they reside on your network, your local computer, they’re basically this side of the dividing line, they are in your private network and you don’t want anybody to just be able to get into your private network. Mean, if they could, I’ll try. Well, not me personally, but I might try getting into your computer and stealing your bank details. Not a good idea just to allow everybody into your private network. And just to clarify, I won’t be doing that. The other alternative is that there are data sources which exist on both on premises and cloud. So that involves an element of the private network.

And what the computer would like to do is for your personal system to get that from the cloud, use the on premises data and then go to Power Bi Service. So Power Bi Service isn’t getting part from the cloud and part from the on premises. Instead it wants your computer to do all that calculation and then upload it. But how are you going to get in between the public network and the private network. Well, you need something called a gateway. And this is my best impression of a gateway. I think it’s the Bradensburg Gate. In Germany. In Berlin?

So what happens is that the cloud goes to the gateway. The gateway says whether the cloud can access anything from your computer and only if it can. Then the gateway gets the information from your computer, from your network and then relays it back through the gateway to the Power Bi service. So the Power Bi service is not talking directly to your network, it’s talking to the gateway and the gateway talks to your network. So it’s this gateway that straddles the public network and the private network. So you need to install this gateway.

But we’re seeing that there was a Personal Mod and a standard mod which previously was called the Enterprise Mod. So what’s this all about? Well, you could see the Personal mod as being more for you as a user rather than for the data. So if you have got one user refreshing the data, then the on premises data gateway Personal mod could well be for you. So the non personal Mod supports power bi. It also supports a lot of other power things power apps as your logic apps, power Automate as your analysis services and data flaws. If you’ve not heard of any of those, then that might not influence your decision as to whether you go for the Personal Mode or for the non Personal mod. Now, the non Personal mod is required, you’re required to have the computer admin to install it. The Personal mod, you don’t need computer admin.

So you can see there is a little bit of a difference in terms of how it runs. The non Personal mode is configured by users who have access to the gateway. Users plural. The Personal Mod is for Windows authentication and configured by you. So the non Personal Mod cannot act as a centralized gateway and data source management. It doesn’t have direct query support and it doesn’t have support for analysis services. However, both of them do allow for importing data and for schedule refresh. And interestingly, only the personal mod allows for R and python. So if you’re in a well, I don’t really know which one to go for, then you might just want to go for the Personal Mode simply because you do not need to be computer admin to install it.

So let’s now have a look at installing the onpremises Data gateway. So that’s the bit between Power Bi service and your computer in the Personal Mod setting. So you can see that we’ve got this big tempting button called Install now right on the Personal gateways. You can press that or you can go to Download and go to Data gateway. That takes you to a website where you can download it, gives you exactly the same program and gives you basically the information that we had on the previous slide so I’ll just pause the video and wait for it to be downloaded. Right, so it’s downloaded. So I will open it. Always make sure you have antivirus on any downloads.

So, getting ready to install the on premises data gateway, brackets, personal mod. So I’m going to install it to this please accept terms of use and privacy statement. Again, always useful to have a read before you agree to it. And in my particular case it says please upgrade to latest available net version. So I’m going to click install and it’s going to install. Well, that didn’t take long at all, did it? So now we need an email address to use with this gateway. Well, I’m going to use, just to make things easier, the email address that I’ve got up there, the one I use to log into Power Bi service. So there we go. So next you need to sign in to register your gateway. So sign into your account. So again, exactly the same username and password that you use to go into the Power Bi gateway. So it’s now being installed and it says gateway was not found. Please try to reconfigure it. Okay, so click on configure at the bottom and we go back into exactly the same thing that we’ve done previously. There we go. So now the gateway is online and ready to be used. So you can see you can restart the gateway. Well, every time you make changes to the configuration files, it’s good to restart.

So it shuts it and then restarts the gateway. We’ve also got other tabs which I don’t think we need to worry too much about. Diagnostics network. So you can see that it is communicating through the gateway using Https. So in other words, it’s the web interface as opposed to TCP, which is another way of communicating it’s transmission control protocol. And nowadays http is recommended and there we have where the custom connectors are stored. So let’s click close and let’s just refresh this page. So now it’s saying we have a personal gateway running on my particular computer, so it’s there. So in this video we have installed the on premises data gateway, brackets, personal mode. We’ve chosen whether we wanted the personal mode or I suppose a standard mode. In the next video we will create this scheduled refresh for this data set and actually use this gateway.