Using Dimensional Views in Toad Data Point Workbook

In this video, we're going to cover how to use the dimensional viewer within [INAUDIBLE] workbook. OK. So I've got an example here. I've got a customer workbook. It's got customer information, order information, and then I've joined those two queries into a third workflow called orders with customers query. Very simple workbook. I haven't done anything-- I haven't added any pivot tables or anything to this workbook yet. We're going to focus on this third workflow. Let me name this customers and orders. Let's deal with this third workflow. So I'm in this third workflow. I've joined my customer and my order data together, and I want to do some basic analysis of this information. So let me start. First, one of the ways that I can do a basic analysis, I can do a pivot grid. I can do an output to Excel. But I can also do this item called dimensional view, and this is where we're going to focus today. So when I send up a dimensional view, it's going to open up a dimensional view with all of these fields in it. And so what a dimensional view is, if you think about a star schema-- if you're familiar with that at all, it's going to seem very logical to you. It gives me a field list of all my fields available here. It's got them organized by type. So customer ideas, a numeric field whereas customer name is an alpha field. And by dragging these fields onto this workspace like customer name, I can get a distinct list of the values within this field. So here is all of my distinct list of customer names. And then I could do something like, let's look at the order ID here. I can right mouse click on this order ID, and I can choose an aggregate type-- how I want it to look at this field. And I'm going to want to do a distinct count. And by dragging this order ID into this same workspace here within this same container, I now have a list, or a count, of orders by customer. So this is my count of orders by customer. I can rename this column by choosing to rename the column alias. I can go count, or let me say count of orders right here. So I've got my customer name and my count of orders. I can choose to reformat this column. I can choose to just right mouse click here and the display format. I don't need the two decimal places. I can just live with one-- or no decimal place, excuse me, here. And I can sort. This is great. So an easy way to sort of create that kind of summary But what else can I do? Well, the workspace here you can add many containers just like this first one. So let's do orders by date. So I'm going to take this date field, and you can see it's already kind of broken it out into its segments. And I'm just going to say by year so our data only contains 2013, and I'm going to count the number of orders in 2013. I had 131. And you know what, every time I drop this in here I don't want to change the format, and I don't want to change the column alias. So I'm going to take this out, and I'm going to make these changes over here. The column name is going to be count of orders, and the column formatting is going to be zero. And so I'm making the change over here in the field list. I don't need to change it every time I drag it into a box. It's going to remember that. So here's the count of orders. So let's pull another item into this box here. Let's do the count of items. So here I've got my order item number. Let's do the same thing. First of all, let's go ahead and give it a different formatting. And let's give it a column alias, and we'll call it count of items. And one last thing, let's make sure we're aggregating this the right way. You want it as a sum. OK, and we're going to drag this in here. OK. So I can see with my order date-- in 2013, I had 131 orders that spanned over 131,472 items. And you know what, I think I do need that-- change that format here so that I can use 1,000 separator, make it easier to read, and drop that back in. So now I have two boxes out here, and so let me just give these boxes some labels so I can tell them apart. This is count of orders by customer. And this is count of orders and items by year here. And I can continue to add these containers into this environment. So for example, let's do it by country. So here's my countries, my distinct list of countries. We'll grab my order ID and drop that in here. Count of orders, there they are. And we'll grab my item ID and count that in here. And now I can see that I'm selling mostly to the US. And so I could say count of orders and items by country. OK. So I can continue to drag and drop and pull all of these items into my dimensional viewer. So that's a nice way to very quickly summarize my data. Let's arrange my boxes here so I can see them. And we'll go ahead and even make it a little bit easier by splitting this up here and here. And we'll go ahead and underpin this so I get a little more space. This is great. Now what if I wanted to apply a filter across all of this? I only wanted to look at a specific warehouse ID, right. So from warehouse IDs here. From warehouse, let's say 36,