Toad Data Studio 1.1 - User Guide

Dimensional Viewer

The Dimensional Viewer allows you to build a custom, multi-dimensional view of data. Drag columns from the Field List to the work area to create a view. You can apply aggregate fields and use rollup columns to view aggregated data, such as sum and max values. Use grouping and filtering to further modify the view. You can export a view to Excel or Local Storage or save your work as a Dimensional View document (.tdm). You can also publish view data to Intelligence Central as a dataset.

You can create a dimensional view from a table, a view, a data grid, a query, or a cross-connection query. You can also create a view from a Toad Intelligence Central object.

The advantage of the Dimensional Viewer is that it allows you to easily create and modify aggregates and groups without modifying the underlying query. In addition, you can easily display multiple views of the same data all within one window.

Note: This feature is available in the Toad Data Studio Professional Edition only.

Note: Publishing to Intelligence Central requires access to a Toad Intelligence Central server.

This topic covers the following:

• Dimensional Viewer
• Exporting Data
• Creating Filters
• Working with Date Columns
• Using Aggregate Types

To create a Dimensional View

1. Use one of the following methods to select data and open the Dimensional View window:
   • In the Object Explorer, right-click a table or view and select Dimensional View.
   • Right-click a data grid and select Send To | Dimensional View.
   • Select Tools | Dimensional View and select a source for data. Select Query to enter a query. See The Pick a Source Dialog for more information.
2. In the Dimensional View window, drag a column from the Field List to the work area in the right pane to create a view. To add additional columns, drag them to the existing view.
   • For date columns, expand the node to display individual date components (year, quarter, month, etc.) which you can use like columns. Drag these to a view or filter.

   • To change a column's aggregate type, right-click the column in the Field List and select Aggregate Type. To change the aggregate type for a column in a view, right-click the column in the view. See Using Aggregate Types.

     Note: The default aggregate type for a numeric column is Sum.

   • To change a column's display format (date and numeric columns), right-click the column in the Field List and select Display Format. To change the format for individual columns in a view, right-click the column in the view and select Display Format.
3. To create a second view, drag a column to the work area outside the existing view.
4. To group rows in a view, click in the view toolbar to display the Group By box. Then drag a column header from the grid to the Group By box to group by that column.
5. Calculated field. To add a calculated field to the Field list, right-click a column in the Field List and select Add Calculated Field.
   1. If the selected column is numeric, click Custom in the Quick Calculation dialog to open the advanced editor.
   2. Build an expression in the editor and enter a name.
   3. To save your expression to reuse in other calculated fields, click Add As Saved Expression. The expression is added to the Saved Expressions list.
   4. Click Apply. The calculated field is added to the Field List.
   5. To add the calculated field to a view, drag the calculated field from the Field List to the view.
   6. To change the aggregate type, right-click within the calculated field in a view and select Aggregate Type.
   7. To edit the calculated field in the Field List, right-click the field in the Field List and select Edit Calculated Field.
6. (Numeric columns only) To add a Quick Calculation, right-click the column you want to calculate in the Field List and select Add Calculated Field.
   1. In the Quick Calculation dialog, select a calculation type and a second column, or modify the expression in the expression field.
   2. Click OK to add the calculated field to the Field List.
7. Calculated column. To create a calculated column in a view, click in the view's toolbar and build the calculated field from one or more existing columns in the view. To edit an existing calculated column in a view, click again.
8. Rollup. To use a Rollup column to group data into categories or ranges, right-click a column in the Field List or view and select Create Rollup.
   1. If the column is a numeric or date column, create a rule for the first range of data and enter a label. Then click + to add another range. Or click Create N equal rollup to instruct Toad to automatically create a specified number of ranges of equal size.
   2. If the column is not a numeric or date column, select the items to include in the first category. Then click Add Rollup. Repeat to add additional categories.
   3. Click OK to add the rollup column to the Field List or view.

9. To add a Percent of Total column based on an existing column in a view, right-click the column and select Percent of Total. The column is added to the view and displays the percentage of the sum for each value in the original column.

   Additionally, you can right-click the column and select Percent of Total Graph to add the corresponding graph.

10. To add a column total, minimum, maximum, etc. to the Summary panel at the bottom of a view or group, right-click the Summary panel below the column and select an option. See Add Column Totals and Subtotals for more information.
11. To create a column alias, right-click a column in the Field List or a view and select Column Alias.
12. Select File | Save File to save your work as a Toad Dimensional View document.

13. Use the Wizard bar to export data, to publish the file or data, to arrange the views within the work space, or to add the file to the Project Manager.

    	Organize views within the work area.
    	Export all views to Excel.
    	Export all views to Local Storage. See Save Data to Local Storage.
    	Save your work as a Dimensional View document (.tdm) and add it to the currently-open project in the Project Manager. See Project Manager.
    	Publish the Dimensional View document to Intelligence Central.
    	Publish data to Intelligence Central.

To refresh the view

• After opening a saved Toad Dimensional View document, press F5 to refresh the dimensional view and reflect the current data in the source database.

To show data details for an aggregate value

• When a value in a view is based on aggregated data, you can drill down to view the data details. Right-click the value in the view and select Show Details. This action opens a Detail Grid displaying the rows on which the aggregate value is based.

To edit the query

1. To edit the query, click Edit Query. Then modify the query in the editor pane. Click to preview the data.
2. Click OK to save your changes, execute the query, and close the editor window. Your changes are automatically applied to the dimensional view/views.
3. To enter a value for a bind parameter or variable in the query, press F5. Toad prompts you for a value.

Exporting Data

To export a single view (container)

• Right-click the view (within its data grid) and select one of the following options to export a single view:
  • Export | Export Wizard. See Export Data with the Export Wizard.
  • Quick Export (Excel). See Export Data with One Click Export.
  • Send to | Local Storage. See Save Data to Local Storage.
  • Send to | Publish Container. Publish as a dataset. See Publish to Toad Intelligence Central.

To export all views (containers)

Select one of the following from the Wizard bar:

• Export All—Export to Excel. Export multiple or all views to individual worksheets.
  • In the Export dialog, each tab represents a view. Specify a worksheet name and export options for each view. See Specify Excel Export Options for more information.
  • A view's header is used as the default worksheet name. The field name/names are used when the header field is empty.
• Local Storage—Save as multiple tables in Local Storage. In the Save to Local Storage dialog, each tab represents a view. See Save Data to Local Storage for more information.
• Publish Data—Publish to Toad Intelligence Central. In the Publishing dialog, click Publish Detail to select which views to publish and to specify the object names. See Publish to Toad Intelligence Central for more information about the Publishing dialog.

Tip: Specify a name for each view (container) in its header field prior to exporting. The name is used in the exported object.

Creating Filters

Toad allows you to create two types of filters: global and local. A global filter is applied to all views in the Dimensional View window. A local filter is applied to only one view.

A global filter is created in the Filters pane. A local filter is created in a view.

To create a global filter

1. Drag a column from the Field List to the filter icon in the right pane.
2. When the Filters pane displays, select the values to include in the filter. The filter is automatically applied to all views in the right pane.
   • To exclude the selected values, select Exclude selected from the drop-down list.
   • To search for a value in a column, enter the value in the Search box.
3. To add an additional column to the filter, drag the column from the Field List to the Filters pane.

4. To remove a column from the filter, select the column's tab and then click the column's close button (to the right of the Include selected field).

   Caution: If you close the Filters pane, the filter is deleted.

5. After building a filter, you can view the text of the filter in a box at the bottom of the Dimensional Viewer window.

   Caution: If you click the close button for the filter text box, the filter is deleted.

To create a local filter

1. In a view, right-click the column to use in the filter and select Column Filter.
2. In the Filter Editor, build the filter condition by selecting a column, an operator, and a value. Click to add another condition to the filter.
3. Click Apply to preview the data. Click OK to add the filter to the view.
4. After adding the filter to the view, the filter text displays in a box at the bottom of the view.
   • To modify the filter, right-click a column in the view and select Column Filter.
   • To delete the filter, click the close button for the filter text box in the view.

To quickly filter a view (local filter)

1. The quickest way to create a local filter is to use the Auto Filter Row.
   1. Right-click any column header in a view and select Show Auto Filter Row. The Auto Filter Row opens in the top row.
   2. Enter a search string to use for filtering in a column or columns in this row. The filter is applied as you enter values and the filter text displays in the footer of the view.
   3. Click Edit Filter in the footer to further modify the filter.

   Tip: Use the Auto Filter Row to quickly create the framework for your filter. Then refine it in the Filter Editor.

Working with Date Columns

For each date column, Toad automatically provides individual date components (year, quarter, month, day, hour, minute, second). Expand the date column node to display the components. Drag date components to a new or existing view or to a filter, to use like a column.

To use a date component in a view

1. In the Field List, expand the node for the selected date column to display the date components.
2. Drag a date component to a new or existing view. Toad automatically adds all date components higher in the hierarchy to the view. For example, if you drag the month component to a view, Toad automatically adds the quarter and year components to the same view.

To use a date component in a filter

1. In the Field List, expand the node for the selected date column to display the date components.

2. Drag a date component to the filter icon to create a new filter or to the Filters pane to add it to an existing filter.

   You cannot add two or more date components from the same date column to a filter. You can only add multiple date components to a filter if each component is from a different date column.

Using Aggregate Types

Apply an aggregate type to a column to display aggregate values in that column. You can apply an aggregate type to a column in the Field List or change the aggregate type for a column in a view. When you drag and drop a column to a Dimensional View by default no aggregate is applied.

To apply an aggregate type

1. In the Field List, expand a column to select the desired aggregate value. Review the following for additional information:

   Aggregate Type	Description
   Min	Minimum value. In a VARCHAR column, the value that appears first when values are listed alphabetically is displayed.
   Count	Number of values.
   Count Distinct	Number of distinct values.
   Max	Maximum value. In a VARCHAR column, the value that appears last when values are listed alphabetically is displayed.
   Sum	Sum of all values.

2. Drag the column to a new or existing view.
3. Left-click the lower area of a view to display a summed total. You can change this aggregate value by right-clicking in the area.
4. To use the column a second time with a different aggregate type, change the aggregate type on the column in the Field List and then drag it to a view (or change the type after dragging it to a view). For example, you might want to display both the Max value and the Sum for a sales amount column.

Automate Dimensional View

You can automate the task of opening an existing Dimensional View file, executing the query, and exporting the data to Excel, Local Storage, or Intelligence Central. See the Dimensional Viewer activity description in Use Database Automation Activities for more information.

Note:

• The Dimensional Viewer uses the default display formats specified in Tools | Options | Environment | Grid for data types.
• The name you enter in the view Header is used as the worksheet name when you export to Excel.
• To see a histogram of data, right-click data in a view and select Histogram Tool. See Create Histograms.

Related Topics

Create Google Analytics Queries

Create Queries for SQL Server Analysis Services