In this video, we're going to look at how to add a workflow to an existing Toad Data Point Workbook. So in this example here, I've got a Toad Data Point Workbook, which I've named Customer Query Demo. I've got three workflows already within this workbook. I have one that's pulling together customer and contact data, another that's pulling together order data, and a third workflow that's combining my customer and my order data into a final result set.
But let's say let's add one more item to this workflow, or another workflow to this workbook. And let's go through the process. So the first thing I want to do is use this plus sign here, right next to my workbook, to add a new workflow to my workbook. When I do that, I'm going to get the workflow instantiated down here. Now I haven't been naming my workflows within this workbook, but I could name them here. And so I'm going to put Order Detail Info. That's what we'll call this particular workflow, Order Detail.
And you'll see that I'm given one thing that I can do here, which is build a result set. All workflows start with a query, a basic call for data from your data source that then you can then manipulate that data by further transforming it. You can create reports from it, you can do outputs. You can do a lot of things with it, but you're always going to start with Build Your Results. So I'm just going to click on this button here and it's going to ask me, do I want to build this in a visual way from my Query Builder? Or do I want to build it from an editor? The difference being that the Visual Query Builder will give you a visual drag and drop interface, where the editor is going to be a straight SQL Editor screen.
And so if you're really comfortable writing SQL, you might want to go with the editor, which gives you a great amount of flexibility. Or if you're pasting already pre-composed SQL that someone has handed you, you can put that right into the query editor. But if you're building from scratch, you're a little less familiar, or you just don't like typing that much like me, you can use the Query Builder.
We'll go ahead and use the Query Builder here and I'm going to do an Add New. And the first thing it wants to ask me is what's my data source? What is the source that I'm pulling data from? And so pick your primary source, the one where you're going to pull most of the data from. I'm going to pull this SQL Server system here and select that. And now I've got a blank canvas here, which I can start dragging and dropping tables from MySQL server system.
You can see in my Object Explorer panel here it gives me all the databases that I'm currently connected to. And here's MySQL server system. Here is the database that I want to pull from. I'm going to pull the tables that I want. And I'm going to pull my Order table and my Order Item table. Because a relationship already exists between these two items, that relationship shows up immediately within my query. If that relationship didn't exist, I could create it through a drag and drop right here and create that relationship. And if I needed to give more details on that relationship, I can go into my Properties panel and I can define this relationship to a much more highly detailed degree.
In this case, I'm going to only pull the orders from the Order table that have items in the Order Item table, and just leave it as what's called an inner join. If you want more information about how to use the Query Builder, we have specific training on that element, so I'm just going to go through it here and build this query really quickly.
And so the way I like to do it is I add all the columns from the one query, and then I just remove the ones that I don't think I need. So I don't think I need the estimated and actual ship date. I don't need this to address IDs. We'll pull those out. And then for this analysis I'm going to pull the Item ID, the Quantity, the Warehouse ID, and the fill date, just leaving those in there. And so here I can go ahead and just execute the SQL statement really quickly and pull back my data.
Now, you can see I just want to point out that right now I'm fetching all rows. So it pulled back 71,000 rows, a lot of rows. Pulled them back very quickly. And I'm fetching all the rows because that's my default setting for my whole workbook right now. Whenever it runs, it fetches everything. I could, if this was a really difficult query or took a long time, I could choose just to fetch the first 1,000 for this one query just so that I make sure that I have the query correct before I spend the time running the full query.
So I could set that here if I want it to be custom, and you'll notice when I told this one query that I only wanted to fetch the first 1,000 rows, I got a custom sample size setting over here, meaning for most of this workbook it's going to be pulling all rows as it was before, but for this one I've added a customization to it. So it's going to do something slightly different.
So that's how you build a query inside of workbook, or how you add a new workflow to the workbook. Just to show you what you can do from this point forward, now that I have this data here, I can do lots of different things. And I can just access this by right mouse clicking on this Result Set step, and I can add all sorts of Children Step to this results set. I could add a pivot grid at this point in time, a transformation and cleanse, a dimensional view. I can add an output to Excel, CSV file, PDF.
Or I could output all of this query either to local storage so that I can use it in other workflows within this workbook or other workbooks. Or, I could publish it to Intelligence Central, if that's an option in my environment. Very similar to saving it to local storage, except you're saving it on a server so it can be used by others as well. Or I could make a Toad View. And a Toad View is just a saved query that you can use again in other workflows within this environment.
In this case, I'll just do a quick pivot grid just so that we can see how that looks. There you can see the pivot grid is named based on my Result Set name. And you can see my results that name is based on my workflow name. So my Order Detail info and so it immediately called my results an odi query. I could change that if I wanted to, but that's how it named the pivot grid too, odi query pivot. And so again, you have complete control over the naming, but some of the out-of-the-box naming will help you label things within a workflow based on a default.
So just a little bit there on how to create a new workflow within your environment. Hopefully that was helpful.