Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

7-3

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
As well as being able to connect to, for example, SQL Azure through the Power BI services we've seen, you can also connect directly to databases within the Power BI desktop. So you start in the same way as importing data by going to the Get Data dialogue. and then you can choose where you want to pick up your data from. And we actually support this against a few different data sources. So currently SQL server, Oracle, Teradata, and Azure SQL database as well. I'm going to use the Azure one as an example here, and at this point, put in the server name and the database as you would do if you were importing data. But you can choose this direct query option. So the difference here is that if you're importing the data, you're taking a copy of the data out of that database and you're caching it within Power BI desktop. For direct query mode, you're just saying leave the data in that database and instead fire queries over to that database whenever I start building visuals. Your trade off here is you're either moving the data from that database. So you're importing it, taking a snapshot into Power BI desktop. Or in the direct query mode, you might take a performance hit because you're sending that query back over to the database, and it's got to be calculated there. So obviously there's some network traffic going on as well, it does require you to be connected to that database. So once you've chosen direct query, you'll be presented with the navigator options just as you would do if you were importing it. So here you can choose which tables you want to expose through Power BI desktop. You'd also be prompted for your credentials, if you haven't already entered those. I've already looked at this database before, so it's just going to send me straight through. So I can choose the tables I'm interested in. So let's take our fact table, agenda, date, a few other ones, and I can click load to finish that. I'm not actually loading that data. You'll see here it says creating live connection. What we're doing is just creating a connection back up to that server. And I can browse these tables just as I can do as if I was working with the data locally, and let's do something like take our employee ID, and just do a count of that employee ID into a card. It'll also take a few seconds because I'm sending the query over to that database. It's running in SQL Azure, but there is a fairly big table in here. So it's just doing a distinct count of all of those IDs. And it will give us the result back— we've got about a million employees in there. The great thing about this direct query mode is that I can actually carry on working with it almost as if I was just working with the data locally. So I can do things like creating calculations, new columns, or new measures on top of this as well. So I've got a measure that I had already created, so it will save me retyping. It just goes and looks for employees who have been around for a little while. So we're just looking for people who have had a tenure of over 12 months. We're just counting the number of rows in our employee table to do that. Now again, this measure is going to be calculated on the fly whenever I start using it in a visual, so again, let's drop a card on there. And it's sending that same calculation back over to the SQL database. It's translating it into a query that that database can understand, and it's going to give us the result back. So a pretty quick way to just start exploring that data live. I don't need to go and import the data into Power BI desktop. I can leave it where it is in that database, but I can carry on building calculations and measures on top of the data and building visuals just as if I was working with it all locally. I can even go and build, or even view the relationships that come down from that database, too, and edit these if I need to. So you can see I've got that relationship here between our employees and our gender table. So I could, if I wanted to say, let's take our gender from that gender table. Now let's look at our tenured employees by gender. So again, it's going to translate that into a SQL statement that that database can run, send it over to that SQL Azure database, and retrieve the result. There we go, we can see our tenure is slightly longer for male employees. We've got slightly more male employees with tenure over a year. So I can work with all of that data just as if it was working locally. Very, very simple, but using that direct query mode.

Video Details

Duration: 4 minutes and 39 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 23
Posted by: csintl on Aug 10, 2016

----- (Please provide translations for these languages: Chinese (Simplified) (chi_hans), Chinese (Traditional) (chi_hant), English (eng), French (France) (fre_fr), German (ger), Italian (ita), Japanese (jpn), Korean (kor), Portuguese (Brazil) (por_br), Russian (rus), Spanish (spa).)

Caption and Translate

    Sign In/Register for Dotsub to translate this video.