SQL DB in Fabric Integrations | Data Exposed

Key Takeaways

one of the value props of fabric all up is how integrated all the different services are together learn how SQL database in fabric is no exception to this this week on data [Music] exposed hi I'm Anna Hoffman and welcome to this episode of DayDay Expos today I'm joined by Dave Levi thanks so much for joining us today Dave hi thanks for having me it's great to have you on the show and Dave is a uh product manager on the SQL team uh focusing a lot these days on SQL database and fabric uh but I'll I'll let you talk Dave tell us a little bit about what you do sure so yeah I've been focusing a lot on fabric specifically uh Integrations so everywhere where fabric SQL touches the rest of fabric um you know with fabric it's really the story there is an integrated platform so there's a ton of Integrations that we have to do to make sure we really like we into everything else that's already out there okay cool awesome yeah and I know there's a lot that we're doing and a lot that we're planning to do um what are some of the kind of top of Mind Integrations that that come to you today so some of my favorites are like data flows um I've got a cool demo I'm excited to show you like being able to take Json data and use data flows to put that into a squel database and then use powerbi to show uh actually we're going to show all of the Azure Reg on a map is a really cool scenario something I've always struggled to be able to do and now it's like super easy it took me like 20 minutes to do it very okay awesome cool well it seems like you have a lot planned to show us so maybe we'll just jump right into the demo you want to do that sure let's do that here we are in our brand new Koso apps workspace as you can see we don't have any items in it we're going to click new item and we're going to create a SQL database we'll click SQL database and we'll give it a name we're going to call this database Koso sales database create now our database is creating now What's Happening Here is we're creating not just a database but also a default semantic model and a SQL analytics endpoint to allow us to read data directly from the one late copy okay we're in our database let's click on Sample data to get some sample data loaded what we can also do is we can go up to the get data menu and we can say new data flow Gen 2 and we can use that to load some data in here too now that we're in our data flow we're going to need some data to add let's pull up a command window here we're going to use an a command AZ account List locations and we're going to pipe that to a file uh in Json format there we go let's look in the file here's our Json file and it has a list of all of our Adder locations most importantly it has the latitude and longitude of each so now let's work on creating our data flow we're going to click get data and then we're going to drag our Json file in and drop it in the upload file area just take a second to read in the file there we go let's click next now that our data is loaded into our data flow we can see here in the power query view that we've got all of our our data read in and we can start to get it in a format that we like what we'll do first is select the columns that we'd like to keep once we have the columns that we'd like to keep selected we're going to right click and select remove other columns all right now we've got it down to just the data that we want now we need to change the format of the latitude and longitude to be just simple text there we go next up we need to rename our query and because we're accepting all the defaults renaming our query means that we're setting the table name that the data is going to be inserted into here you can see that all of the destination information has already been filled in because we use the get data menu in the SQL database to create our new data flow okay let's name our data flow and now we can publish it we're back in our workspace let's go back into our database you can see our data flow is finished successfully now too let's go look at our dbo schema let's look at tables looks like we have to refresh so we'll refresh real quick here and there it is location so let's click on that this automatically selects the top 1,000 rows next let's go look at the sample data that we loaded we'll go down here to sales LT we'll expand tables let's just click on address we can see that our sample data all loaded successfully so let's go see if it's replicated to one Lake we're going to click the replication tab at the top and then monitor replication here we can see a list of our tables as well as a replication status we can see all of our sample data plus the locations table that we just loaded are all replicated to one link let's flip over to the SQL analytics endpoint we're going to click the menu up here in the corner select SQL analytics endpoint and here we are it's a data warehouse based view but basically the same thing so we're going to expand schemas let's expand dbo and tables there's our locations table we'll click on that there's all our location data let's go down to sales LT and tables again let's look at address again here's our data so let's say we wanted to work with this data in a notebook what we can do here is we can say new SQL query and new SQL quer and notebook and create a notebook for us we'll skip the tour for now and you can see it's a very similar experience so we can click on locations we can say select top 100 and it's automatically going to put the code into a cell for us that we can then run okay perfect next let's go back to our SQL analytics endpoint let's go to the top and click the reporting Tab and we're going to click new report all the tables are fine so let's go ahead and click continue he here we are in our new report let's go ahead and add a map to it now we need that locations table we created earlier and we'll pull the latitude and longitude into our report okay now that we've got all our locations plotted let's make the map bigger so it's easier to see okay perfect next let's go back to our database it's probably a good idea to save our report so we'll hit save give it a name here real quick and we'll hit save and here we have our finished report now I'm super excited about this because this is something I always wanted to do ever since I saw this map first demoed could never get it to work now with fabric I was able to do it in 20 minutes let's get back to our workspace we've been building a lot of stuff and it's probably time we did some documentation we're going to select a new task flow as you can see we've got a lot of choices we're just going to use the basic data analytics okay we've got our new task flow let's work left to right so we'll start with collect data we'll click the paperclip and we're going to say load locations for collect data so we'll check that we'll click select now we see we've got the collect data tag next to our load locations let's do store data now and we can select our database okay let's go to create visualizations now here we could say new item and just create a brand new report um we don't need to do that so we'll close out of here let's do attach and we'll attach the report we just did and then finally we've got some flexibility if we don't have a track data step we can just delete this there we go all gone and now if we click on each of the items we can filter to just the artifacts that we're interested in and then finally to remove the filter we just click anywhere in the white space and it'll show all items again let's head back to our database here we're going to use some of our sample data to create a new graphql API so we're going to click on address and create API for graphql we'll give it a name we'll click create okay our table's already selected we can click load and here we are in the graphql editor we're going to type our query here while holding down the control key I press the space bar to engage the intelligence let's do a little formatting here okay and then we'll do Control Plus space bar again now we'll pick addresses and then items and we'll just pick a few columns all right let's run our query and there's our data so as we scroll through here we can see that we've got all the same data that we saw over in the database window now this is cool but how do I make this work in my application well here at the top if we click on generate code there's sample code in either python or nodejs that you can copy and paste into your own application and use it as your own all right let's head back to the workspace just take a look at all we're able to get done because everything in fabric is integrated you can take days of work and do it in a few minutes wow that was a lot and it seems like you're able to do it so easily like I feel like I could go in and do it just myself so thanks for sharing that with us I'm sure viewers learned a lot and I know I did as well uh one question for you that I have and I'm sure others are thinking about as well is when you think about road map or like things people are asking for or things we want to do next uh is there anything you can share yeah um you know we get lot lots and lots of questions about notebooks uh we do have notebook support going against the analytics endpoint but that's readon um there is a workaround you can do with code to you know update your SQL database live but we're actually working with the team to um fix on a timeline to have full support for writing tosql database within notebooks um we're also looking to finish off the rest of the operators in data pipelines and we've got some really cool work that we're doing with the RTI team looking at how we can have real-time events both going into and coming out of SQL database that should be really exciting awesome cool yeah so I guess the the takeaway for folks is like we're just going to continue investing in these Integrations I think that's part of the kind of the power and The Sweet Spot of fabric right yeah every new thing in fabric we want to be a part of so um anything we can do to integrate and just make that a seamless experience is really our goal awesome great well folks uh thanks Dave for for joining us folks thanks for tuning in if you like this episode go ahead give us a like leave us a comment and let us know what integration you want to use today and what integration you're looking for tomorrow uh we'll put some links in the description for you to learn more and we hope to see you next time on data exposed [Music]