PL-300 – Section 20: Part 2 Level 8 – Get Other Types of Data Part 3

162. Setting up Azure SQL Database in Power BI

Over the next two videos we’ll be connecting to Azure, Azure SQL Database.

Now, what is Azure? Well, Azure is a managed cloud database. So, it is service, which is this final “S”. But it is also software which is this first “S”. “Software as a Service”.

Now, it is something that is not free but you could get a free introduction to it. It is SQL on the web. And you can see how Microsoft is marketing this. For instance, easier to maintain because it’s maintained on the internet. Microsoft can give it the latest patches and so forth. So in this video, let’s get a SQL Azure Database. So, if you’re not interested in actually how you actually get an SQL Azure Database, then skip to next video where we’ll be showing you how to use it. So, I’m going to “Start Now, With A Free Credit”. As this one says, this offer might not be available. But notice, one of the things you can do for free for 12 months is have an SQL Database up to 250 Gigabytes. And that is quite a lot. So, I’m going to “Start for free”. I had to reload it in a different tab, it just came up with a blank page. So, I’ll type in my details. So, this is my Microsoft account that I’ve already got set up.

So, now I’ve done it. It’s loading up the window. And then after quite a long wait, you can see that it’s asking you to put in your personal information. This is the identity verification by phone information. And you can see it either phones you or texts you. And it just texts me a six-digit verification code.

Then, it’s asking you for your credit card information. You won’t be charged unless you upgrade, so it’s just to verify your identity. And you can see the various information it’s asking for you. And then you’ve got a subscription agreement. Lots of legalities but I always suggest working through those, just have a look. See, what the Azure free account credits can be used for. And the privacy statement. So, lots to read but I do suggest reading it, just having a look what’s there. So once you’ve done that, check it. You may wish to receive information, tips and offers from Microsoft or select partners. Well, I’m going to say yes just in case there’ll be something useful. And click “Sign Up”.

Now, there was a lot of waiting, lots of these wheels going around. These bubbles going around. So, it’s just setting up the account and then it says, “Welcome to Azure”. So, we can then create an Azure SQL Database in the Azure portal. So, let’s create an SQL database in the Azure portal. And it does have this quick start but let’s see this in action. So first of all, we need to sign into the Azure portal. So, there was a link in that window to do so but here it is. And you can see there’s a tour you can go on. I’ll say “maybe later”. So, let’s create an SQL database. And you can see that there is additional training if you want as well. So, let’s create a database. So, it’s part of my free trial subscription. And I haven’t got a resource group so I need to create a new resource group. So, I’m going to call this “FirstResourceGroup”. So that’s just a container that holds everything that it needs.

Now, let’s create a database itself, so I’m going to call this “FirstDatabase”, and a server. So, let’s create a server. So “create new” server. So server name, so “myfirstserver”.

Now, bear in mind, it’s got to be all lower case. If it’s in capital letters, then it won’t work. Server login, so the administration. So, I’m just going to call it “azureadminlogin”. And a password. So, you can see, it needs to be a complex password. And the location, well that’s down to where you are. I’m going to say UK West. Except I find that I’m not allowed to with my free subscription so let’s go for U.S. East. And you can see, the specified server name is already in use. So, I need to just add a few letters to the end of it. And I’m going to put “2022”. There we go. So, click “OK”. I’ll save that information. So, do I want to use SQL elastic pool? The answer is no. The computing storage, it’s probably all okay. So, I’m just going to leave it at that. So, there we go. Instantly what’s in there might be a nice clue, how much is this going to cost a month. So, if I go down to “Basic” then you can see it’s costing about 5 U.S. dollars a month. So not that much at all. So, I’m going to go for the basic. And then I’m going to scroll all the way to the top and click on “Additional settings”. And I’m going to use sample data for this and it’s AdventureWorks Light.

That is the standard sample data. And I believe the rest of it will all be fine. So, let’s review and create. So, you can have a look at all of the settings. So, let’s create. And the computer is now creating your database. And there it is. Your deployment is underway. So, nothing more to do and we’ll just pause the video until it’s actually finished. And in about three minutes, the deployment is complete. And the next step, go to the resource.

So, here we are in my first database which has already got data in. And in the next video, we’ll have a look at how we can use this data in Power BI.

163. Using Azure SQL Database in Power BI

Now, in the previous video, which you may have skipped, we created an Azure SQL database and we clicked on Go to resource which opened up our database. So, what do we do from here? Well, you’ll notice that there is a Quick start which gives you a few ways to connect. A fairly quick start is to go to the Power BI preview. So, if you click on that, you can create a report from there. Or it downloads something that enables you to create a report. So, if you click on Create a report and it gives you a small file which you can click on, and it opens up Power BI and fills in part of the information.

Now, there are two traps coming up and we just need to guide you through both of them to be able to use Azure SQL connection. So, this is what you get and in fact, the file downloaded is a very simple file. All it really says is the name of your database. Please don’t try to use this database, you won’t be able to connect because the username and password you don’t know. Or at least you don’t know the password.

Now, the thing is, this is the first trap about Azure connection to Azure SQL Server is that it gives you this Windows credentials. Please use the alternative credentials. Well, we know what my credentials are. My credentials are username this and password which I will just type in. So, these are the correct credentials. However, we cannot authenticate with the credentials provided. That’s because we’re trying to use Windows credentials. They don’t work on an Azure account at the moment. No doubt, Microsoft is trying to make it work. Instead, we have to go into the Database tab. So that’s your first trap that you don’t just enter it into the Windows tab, you enter it into the Database tab. So, let’s do that again. The second trap is what happens when you press Connect. One of the errors that you might get is that you cannot get through the firewall and that’s because the Azure firewall may not be set up to allow your particular IP address in.

Now, the good news is this is very simple to correct if you’ve got access to the Azure database or if you know someone who has. So, we go to Overview at the top, set server firewall and you can see that there are no firewall rules configured. So, what we need to do is add a client IP and there’s my IP and you’ll also notice that that address is exactly the same as is on this error message. So, as I say, very easy to set up. So, we’ll just save this and it may take up to five minutes according to this but you saw how quick it actually was.

So, let’s retry that. So, now that we have successfully connected to the Azure SQL database, the rest of it is as per connecting to an on-premise database. So, let’s say I want the product as I did previously. Then, I will click Transform data. Because my connection is using DirectQuery, then it downloads the data when it needs and this is the perfect time for you to manipulate the data so that it can send an SQL query of, however, many columns, however, many rows it needs after you have done all of your manipulation like we did a few videos ago. So, you could say I want the top 50 rows, for instance and what’ll happen is the computer will query fold it into a more efficient SQL statement. So connecting to Azure database is fairly easy once you get past those two initial traps. And once you’ve done that, then you can create whatever visualisations you would like to in exactly the same way as you’ve done before. So, this is I think one of the really good things about connecting to various sources. Once you’ve done that, it all looks the same way.

Now, just to point out that if you do get data through the normal route, so Get Data, Azure, Azure SQL Database, then you’ll have a slightly different box but this looks like the box that we had previously. So, we need for while we’re looking at SQL Server on premises so I’ll connect to my server. We use DirectQuery, we can use any of these additional items. I choose not to, click OK. Then it may ask for username and password. It doesn’t in this case because it already knows it because I’ve used it before and then you can connect. So, just, a slightly different dialogue box. The more standard dialogue box, if you do go Get Data, Azure, Azure SQL Database. So, this is how, in the past two videos, we have seen how to create an Azure database and we’ve seen how to connect to an Azure database and create visualisations from it.

164. Use the Microsoft Dataverse

In this video, I’d like to talk about another connector, and this connector is the Dataverse. So, just like all the others, we go to get data, more dot, dot, dot. And halfway down on the left hand side, we’ve got power platform.

Now, the Dataverse is the new name from November, 2020 onwards of the common data service. So, if you see lots of references to CDS, that is now called the Dataverse. So, what is the Dataverse? Well, it’s a database on the cloud. More specifically, power BI is part of the power platform. This is a series of services that Microsoft has put together. So, we’ve got power BI for business analytics. We’ve got power automate which automates processes or flaws and power virtual agents which allow for chat bots, automated messaging, and it uses artificial intelligence to understand your answers. But in addition, there’s something called power apps.

Now, power apps aren’t the basis of this particular certification, that is more the PL-900. But if we were to look at a sample power app for instance, we would be able to see information that’s on the web in a Detaverse in this particular instance, and here we can edit it and you can see I’ve done conditional formatting with the background and the font size and that sort of thing. So, this is an example of a very simplistic app.

However, what is driving this information? This information is being driven by Detaverse in my environment. And the Detaverse consists of a series of tables which used to be called entities which naturally have got columns but these used to be called fields. But basically it’s tables, columns, roles nowadays. And you can see the sorts of things that we’ve got. So, what I’m going to do is connect to this particular Detaverse. I don’t expect you to be able to do this unless you’ve actually got a power apps account but it might just be of interest of how to actually connect. So, I go to the staff here and I click on session details and this gives me a URL to which to connect to, but it’s not the entirety of this. I don’t want anything either side of the slashes, so I just want everything except https colon slash slash and then a slash at the end. So, I’m going to copy that. And I’m going to go back into power BI. So, I’ll select Dataverse, and you should always select Dataverse unless you’ve got a reason for going for the common data service. One possible reason is you’re importing a huge amount of data at the moment Detaverse isn’t quite set up for that. So, I’m going to connect and it’s asking me for my environment domain, this is it. And then click okay.

Now, what will probably happen next is you’ll get a dialogue box like this. We encountered an error while trying to connect. A TDS protocol endpoint is disabled for this organisation. It’s disabled by default. So, let’s enable it. So, I go to my power apps. I go to the admin centre. I go to my particular environment; my environment is PO 900. I go to settings, go to product, go to features, and then enable TDS end point. So, I’ll just click that on and save. So, now when I connect. Here is my account, and if I go down to my contacts which is what we were looking at earlier. We will be able to see it and be able to load and transform data and all the rest of it. Of course, you’ll also need to make sure that you’ve got proper permissions to be able to access the data. So, it is actually quite a big job to get it set up initially. And this particular certification, DN 100, you don’t need to know any of that. All you need to know is that the Dataverse is a database which is used in the rest of the power platform. So, it can be used in power apps, it can be used in Power Ultimate. And you can use it in power BI by going to get data, more dot-dot-dot (…), going down to the power platform, it is in the all as well, but if I go down to the power platform, there it is, the Detaverse.

165. Configure Data Loading

In this video, we’re going to have a look at configuring data loading, and you can imagine data loading being right at the outset.

So, when we have a query, the first thing we’ve got to do is to load it from somewhere; however, the configuring data loading is not actually part of the Power Query Editor. Instead, it’s in file, options and settings, and options. So, there are two separate sections, data load global and data load the current file, and we’re going to take each in turn. First of all, for the global, so this is all files rather than just the one particular file. We’ve got something about unstructured sources, so these could be, for instance, from Azure Cosmos DB. So, we’ve got something that’s not a relational database. It could just be a bunch of documents, each of which could have a different schema, as it’s called, different column type, different headers. So, you can see what’s happens, you can always detect column types and headers, detected according to each file settings or never detect. We’ve also got an auto date/time, so what’s this all about? Well, when you have a date, we’ve seen in part one of this course that you have a hierarchy automatically created. So, we have year, we have quarter, we have month and we have day. Auto date/time can be quite useful because it allows for this hierarchy to be created. However, this hierarchy is only useful if you agree with its contents, for instance, we have a year.

Now, a year is a calendar year and it might be that you want a year to be a different form of year, an academic year might start in April, might start in September. So, this date/time is useful, but only insofar as you find it useful. If you don’t want to use it, then de-check it.

Also, make sure that your date/time is reasonable, the actual values you’re using. For example, there was one source data which had an unknown date type, and rather than using nulls, it used the year 1999. Well, this date/time table had to go all the way to the year 1999, and it just took a huge amount of memory. You can imagine literally hundreds of megabytes, which had a performance impact, so not good. We also have cache settings that you probably won’t need to use. So, whether the query preview results are stored on your hard drive and you can see we’re nowhere near the requirements, nowhere near needing to expand this, but if we ever did, this is where it is.

Now, for your current file, there are some additional settings, so we start off with detecting column types and headers for unstructured sources, so that’s inherited from the global, whether relationships are detected when it’s first loaded or when you are refreshing the data, and whether the computer should try and discover additional data types after the data has been loaded.

Again, we’ve got the question of for this particular file, do you want a date/time table? So that again is inherited from the global, but you can adjust it for an individual file, whether you want data to be downloaded in the background so data preview could work, if you have got a lot of tables, whether the table should be loaded all at once. So, instead of it being sequential, I load table one, then I load table two, then I load table three, whether we say I want tables one, two, and three loaded all at once. So, that might improve performance, but then you’re asking the source, assuming it’s one source to do three things at once. So, it might degrade the performance or reliability of that server. And then, whether you want Q&A, questions and answers to be turned on.

So, these are the data loading configurations, and you’ve got two separate sections, one for all files as a default, and then one for your current file.

• Category: Uncategorized