Understanding workbooks and workflows in Toad Data Point

In this video, we're going to look at Toad Data Point Workbook, and we're going to look at all the elements that are involved with Toad Data Point Workbook so you can get familiar with how it works. So we're starting off here with a workbook that's in progress. And I'm just going to talk about what we're seeing on the screen so that you can get familiar. So over here on the left-hand side, you can see a tree view of all of the different elements that someone has put into this workbook. So let me just go through them. The first is they've named the workbook Customer Query Demo. Again, to name a workbook, you just click on the workbook name. And then you can edit that name or add a new name. And that's the same for whether it's workbooks or workflows. And then within this workbook, the user has created three workflows-- Workflow 1, 2, and 3, in this case. They haven't renamed those workflows, although you certainly could do it. So when you think about workbooks and workflows, I like to think of it as like an Excel workbook concept, where you have the workbook itself is the file. But inside that file of Excel, for example, you have sheets, right. So you have different tabs of data. Same kind of thing for Toad Data Point Workbook. But over on the left, we show it in a tree hierarchy. All workflows, by the way, start off with a result set-- so a basic database or data source query. In this case, Workflow 1 is one that's customer and contact data. And if I just click on that, we can immediately go through and see this is what that query consists of. Workflow 2 is order data. And Workflow 3 is customer and order data brought together. So in this example, someone has put together customer data in Workflow 1, has put together order data in Workflow 2, and then has brought those two data sets together as part of Workflow 3. OK. And so, again, just to click through so that you can see, if I click on Order Data, here is the query that's making up that order data. And I click on this last one, Workflow Data, here is the query that's making up that workflow data. OK. So let's look at some of the other elements involved with a workflow. So going back up here to customer contact data, I can see that I have a few children items, right, a few sub-items off of this main query. The first one that I have is customer cleanup. And so clicking on that will expose what that is. And so this is a transformation and cleanse screen, where the user is doing a few things-- in this case, three things-- to the data to kind of clean up that data and get it ready for analysis. So starting with what came out of the customer and contact data query, they've now added a transformation and cleanse cleaning up that data. And then, off of the transformation and cleanse, meaning that as children in this tree structure of transformation and cleanse, they're doing two things. One, they are saving this data into local storage. So they've got an output here where they're taking this cleaned customer data and creating a table of that data in their local storage, which is like their local sandbox that's attached to Toad Data Point. And two, they've created a pivot table of that clean customer data. And they haven't done too much here, just maybe a summarize. It looks like a distinct count of customers by age. OK. So that's the first workflow. Second workflow, very similar as well. They've done another cleanup of the data here. And they've sent that out to local storage here. And then the third piece, they've actually combined these two saved items-- these two outputs, the first one being here and the second one being here. They're now accessing those outputs here. And I can tell that from the names. So the name of this data is Clean Customer Information. The name of this one is Clean Order Information. And then when I look at my third query here, I can see that that's what they're joining in this third query-- Clean Customer Information and Clean Order Information. And then off of here, they're making a pivot so they can see exactly how many orders they're having per month and the sum of those orders. So what we're showing here is that, off of every level of a hierarchy, you can add a sub-item. So let's go ahead and actually do that now in Workflow 2 so you can see how that works. So here, we've got order data. And, again, we've added some cleanup on this order data. But let's not go to that cleanup. Let's start with this basic order data. Click here. And then let's add, for example, a report-- an Excel report-- right off of this order data. And so what this will produce right here is, before it even gets to the cleanup, it's going to output this data to Excel. And so we'll call it Excel Report of Orders. OK. And exactly how that Excel report will show up, I can go into the Settings of this item. And I can set up the settings. I can say exactly where it's going to end up. Here I'm going to put it my Documents folder. If a file already exists, do I want to overwrite it? You bet I do. And what do I want to call this sheet that it's actually creating in this workbook? We'll call it Order Detail. OK. And then there's other options here-- which column I'm going to start at, which row, how I want the header row formatted-- all of that, we're just going to leave as is. And so what this means is now I can come out here, and I can actually generate this report. It will actually run this one step and generate this Excel file. And you can see up here, this is telling me that I'm fetching all of the rows throughout the query process. So it's going to actually pull in all of these order rows. So it's just going to take a quick moment here. OK. And now that that's done, we're going to see it actually opens up that report for me, and I can see all of that data. So I've got my order detail tab. OK. So that's just one example of what you can do. If you click on this dropdown right here on your order data item-- or I'm sorry, you right-mouse click on this element-- when you go to Add a Step, you can see all the different steps that you can do. So let's just take a moment here and look at our different steps. We have Work Steps, which will produce screens within Toad Data Point, like a pivot grid screen, a transformation and cleanse screen, or a dimensional view within this Toad Data Point Workbook. And we kind of looked at a couple of those pivots and transformation and cleanses earlier. You can produce a report, which means an output out to a file system. So an example would be an Excel report of the data, or a comma-separated value file of the data, or a PDF. Or you can take the results of what you've put together and save them into local storage as a table so that you can use it in other workflows within this workbook or within other workbooks. You can publish it to Intelligence Central. So if you have Toad Intelligence Central in your environment, that's a server-based software that works hand-in-hand with Toad Data Point, you can publish it into that server environment and, again, share that with outside users who don't have Toad or share it with other Toad Data Point users. Or you can turn it into a Toad View. A Toad View is sort of a saved query. And so we'll have another video where we can talk about exactly when to use a Toad View. But just keep in mind that with each step of your workflow, you can go ahead and have access to what you could do from the next step forward. So I was doing that off of this order data query. But if I go to my Customer Cleanup, my Transformation and Cleanse, and right-mouse click, it's going to give me what I can do from a transformation and cleanse perspective as a next step. I have the same here. I could do a pivot, another transformation and cleanse, a dimensional view. The same kind of outputs-- or reportings, excuse me-- that you can do. And then from Outputs, because it's transformation and cleanse, it limits you to just local storage or publishing. You no longer can do a view. And so each level of the hierarchy will give you an option that's appropriate for that level. So from a pivot grid perspective, if I want to look at putting something on a pivot grid or doing a next step from that point, I could create another pivot grid or a transformation and cleanse dimensional view. From Reporting, I have a little bit more options here. I can do an Excel report or a pivot Excel report, CSV and PDF, or, again, local storage or publish. So hopefully that gives you just a brief overview of the interface here and what your options are from the workbook left-hand navigation-- this tree navigation here-- and it helps you put together your own workbooks. Thanks much.