PL-300 – Section 36: Part 4 Section 5 – Manage Datasets

267. Analyze in Excel

Hello. And in this section, we’re going to be looking at managing databases. So again, we will have a video for each of the DA-100 exam requirements. And we’re going to start off with Analyse in Excel. So, you’ve got your datasets and also reports, open the Power of 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 got analysed in Excel updates. So, your version of Excel, will probably not have the latest analyse in Excel update. So first of all, I’ve closed Excel on my computer. I can click on this analyse in Excel updates 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 next to it and analyse in Excel. So, I’m going to do this with this dataset which has no row level security, and analyse 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 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 updates 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 Microsoft Excel 2010 Service Pack one or later, you need to have predefined measures in your datasets, you need the power BI pro licence because as I said this is for sharing. Guest users can’t use analyse in Excel for datasets originating from another tenant. As we know that’s another organisation. It’s a power BI Service features, not available in power BI reports 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 an Alliance in Excel. They don’t have, the get and transform or any of these DAX models that we’ll be using. So, now that’s finished installing, I can try and gain and go to analyse in Excel.

So, I’m going to now click, I fully download these updates. You might want to just open up Excel and make sure everything’s fine, before that. So, lets 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, where any files which go from the internet by default are 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 BI S 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’s 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 and all that. 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 come 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 that it’s purely sensitive read only. So, I can put in things. So, let’s put in, say the product key into the roles, 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 something instead the standard cost and have a total of standard cost count, maybe, Oh, I can’t do that I need to measure. So, let’s see if we can find a measure, and I’ve looked all the way up and down this pivot table, not actually introduced any measures apart from the two, maybe, be right on top those measures, yes. So, here we can see a measure, so I’m going to change it so that’s it is a count don’t count with that. So, you can see very little of 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. I said before you need explicitly created measures. what do I mean about explicitly? Well, quite often in a pivot table, you’ll be able to drag like sales amount into the values and create a new measure, counter sales amounts, but here you’ve got to actually have made that explicit beforehand.

So, analyse in Excel, it enables you to go from a dataset or a report, which uses a dataset into Excel. It requires Excel 2010 service pack one or later, and requires a downloading of an additional add-in the analyse in Excel updates.

268. Promote or Certify (Endorse) a Dataset

Now, some datasets might be better than others. And you might want to say actually, this is the dataset you should be looking at, what you can do is something called endorsing your dataset.

Now, if I go to my workspace and go to datasets, 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 dataset you should be using. So to do this, we go to a particular dataset. So, we’ll go to this category and subcategory dataset. And I’ll click on the dot-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 dataset 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 that dataset. But it requires an expert to say this is really good data. So certified acquires certification from experts in your organisation, and then it will get these certified badge that shows it’s recommended for use by others.

Now, the certified dataset, this is a administration task, rather than a data analysts 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 dataset. So click apply. And we’ll go to my workspace and go to dataset. And now you can see that my category and subcategory this is head now has a little badge to show that it’s promoted. And that’s really all there is with endorsing a dataset is there for you to show that it’s not just draught data or trial data or data, you’re not fully checked. This is real data that office in your organisation 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 exporter, perhaps, but we have this certification disabled for the entire organisation. Well, I’m going to enable it and I’m going to say that this 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 organisation. So click apply. And you can see tenant that’s to say, the organisation settings will be changed within the next 15 minutes and 15 minutes later, the certified button becomes on grade I can now select it. So, I will certify this because I and everybody my organisation 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 dash 100, you do not need to know it. However, you go to the Microsoft free six five Admin Centre. You go to groups, you go to active groups, I’ve already got some groups set up, I add a new group is going to be a security group. Click next giving the names. So new security, group free. And here’s a description. Click next and create the group is 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 tenant 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 dataset 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 administer to and ask them to have a look at this video. So that is all about endorsing test sets.

269. Manually Refreshing Data in the Power BI Service

In this video, we’ll just have a quick look at how to refresh datasets manually. I’ve got this dataset that we’ve had to 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 up I’ve imported it. I’ll go into the dataset 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 A to D and 1 to 4. Then also, 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 2 rows and I’ll just close it just in case that causes any problems. So, I’ll go into the datasets and get data import to go into the menu. And there is this refresh now. Excellent, so I’ll click on refresh now and we’ll have the two extra rows and that will be end of the video, except it hasn’t happened. Okay, let’s just go to home and go back into the report. Maybe, the reports hold, that’s going to 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? Yeah, let’s say we’ve 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 import and notice what it now says after I click import, you already have this file. If you replace the existing file, some tyres might be impacted. So that’s, I want it to be impacted. I wanted 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 awhile for everything to be refreshed. If you find this is happening and you want it to be refresh 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’s it? The data changes like we have done a few videos ago. That’s no problem. You just, well, we publish it on top and then the dataset get changed. And you’ve though polls get changed, but equally any reports which you have created in the power BI service based on the data eventually gets changed as well.

So, this is how to manually refresh your datasets, but manually refreshing them is not necessarily the best use of your time. Especially if you have to do this several time 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 videos.

270. 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 datasets.

For example, we have got this dataset 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 letter set and then update itself? Well, here we are in the Power BI Service, and we’ve got this dataset 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 scheduled 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 on-premises data gateway bracket standard mode. So, what is all of this about gateways? Why do we need a gateway?

Well, there are three basic locations where your dataset might be. Firstly, you must have data sources that reside in the cloud. For example, that might be on the Azure SQL database. And that’s fine. What to can happen is that the Power BI Service, which is on the cloud can then go to the Azure SQL database, which is also in the cloud, and just with the correct credentials, which can be edited in the data source credentials. That was a bit that was greyed 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 and they are in your private network. And you don’t want anybody to just be able to get into your private network. I 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 they are data sources, which exists 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 guessing 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 Brandenburg Gate in Germany in Berlin. So, what’s 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 release 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, there’s this gateway that straddles the public network and the private network. So, you need to install this gateway, but when we sync that there was a personal mode and the standard mode, which previously was called the Enterprise Mode. So, what’s this all about? Well, you could say the personal mode as being mole 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 mode could well be for you. So, the known personal mode supports Power BI. It also supports a lot of over power things, power apps as your logic apps, power ultimate as your analysis services and data flows. 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 mode.

Now, the non-personal mode is required. You acquire to have the computer admin to instal it. The personal mode you don’t need the computer admin. So, you can see that 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 pleural. The personal mode is for windows authentication and configured by you. So, the non-personal mode cannot act as a centralised gateway and data source management design. 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 mode 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 on premises data gateway. So that’s the bit between, Power BI Service and your computer in the personal mode setting. So, you can see that we’ve got this big tempting button called install now, right on the personal baked ways. 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 programme 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’ll open it. Always make sure you have antivirus on downloads. So, getting ready to install the On-premises data, gateway brackets, personal mode. 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 the 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 while 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. Sign into your account, so again exactly the same. Username. And passwords 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 restart the gateway. We’ve also got all the time, so 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 instead, in other words, it’s the web interface as opposed to TCP, which is another way of communicating its transmission control protocol, and nowadays http is recommended. And there we have a way that 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 of brackets, personal mode. We’ve chosen whether we wanted the personal mode or I suppose the standard mode, in the next video, we will create the scheduled refresh for this data set and actually use this gateway.

• Category: Uncategorized