In Toad Data Point 3.7, we've added a new window called the dimensional viewer. The purpose is to provide a window to quickly explore aggregated data and even change the aggregation type on the fly without having to re-query the database. This is especially useful when you're not fully familiar with the data and need to understand it better for reporting purposes.
To access this dimensional viewer, you would right-click on a table from the Object Explorer and send it to the dimensional view here. Or you can click on the toolbar, the Dimensional View button, and enter your SQL statement. Or any place where you have a result set, you can right-click and send to the dimensional viewer.
The rows are then sent to an in-memory database, and the numerical fields are aggregated. So to begin exploring, you just simply drop over your field to the workspace area. There's a built-in calculated column of row count, which can give you some quick summarization.
You can click on the Show Footer and add some summarization or functions for each column. In this case, I just want to know the total. Now every numerical column has a default Aggregation Type of Sum, but this can be changed. Let's say that I want to know the quantity maximum order for each of these offices. So now, I can kind of sort, and I can see that these offices are ordering larger quantities than some other offices.
Now filtering is pretty quick, too. So you can take a column and drag it over to the filter picture, and you can deselect items that you want to exclude. And while you do that, it will automatically exclude it from the existing containers. If you want to do the opposite, you can click to exclude, which will do the opposite. So anything's that's checked, it will remove it and include those that are unchecked.
You can do additional filters. You just drop them on, and it will be handled the same. And if you want to remove an individual filter, you can click the X here-- right here. Or if you remove all of them, you can just go down here to this summary and click the X here.
Now years are broken down into logical divisions of time. So in this case, I can drag over the Year type, and if I only want to see 2012, '13, and '14, I can filter by that. And any open containers will automatically be filtered.
Now you can have multiple containers in your workspace. Just drag them over. So if I want to look at offices by year, I can do that. I can also show a group panel, so that I can group and explore these individually. I can right-click and add a percentage of total. I can also add to my own calculated column in here.
And each container is just a regular grid, so I can do a quick export to Excel. I can also send it to local storage. And if I would like to pick up this workspace later, I can save this as a file, a .tdi extension. And it will save all of the rows and serialize it to a disk, and I can reopen it and continue working with the data without re-querying the database. Or if I want to refresh it, I could F5, and it will re-query it and refresh my containers. So it's a very handy new window for exploring and seeing how that data is aggregated.