Construct a Basic Data Object Explorer Query
Once you’ve defined the scope of your exploration session, you’re ready to construct in basic mode the data object query (SQL SELECT) used to extract relevant data. By default, all information from the data object is extracted (SELECT *). The data object explorers simplify building your SELECT statement by allowing you to:
- Select column query options from the Attributes menu.
- Enter query text directly and take advantage of the UI type-ahead functionality
You progressively build queries against data objects by selecting attributes from the selection panel and mapping them to syntactical components of the query: SELECT list, WHERE clause filter, GROUP BY, and ORDER BY.
The SELECT statement you construct is a limited function query optimized to run against the selected data object and extract the most useful data with minimal complexity. The full range of SQL SELECT statement operations will not be compatible with the data object explorers and will generate an error when run. The UI guides you through SELECT statement creation.
Select Attribute Columns from the Attributes List
The data object you select determines what selectable columns appear in the attributes list.
This list is subdivided into the following categories:
- Pinned: Frequently used attributes can be “pinned” into the top section of the attribute panel for ease of re-selection to avoid repeated scrolling through long attribute lists
- Computed: Measures from a data object can be aggregated using common SQL aggregates like SUM, MAX, MIN, and AVG or combined mathematically using formulas to derive meaningful computed measures. These can be named and re-used which is especially useful for complex or verbose computations.
- Measures: Attribute columns that provide numerical data you want to aggregate.
- Dimensions: Attribute columns (such as key columns) that characterize the data. These are typically used to group data (GROUP BY operations).
Add an attribute from the list to the SQL query.
To add a column from the attributes list to your SQL query, click on the desired attribute column name (under Measures or Dimensions) in the attributes list. Click on the vertical ellipses to the right of the list attribute to display the Actions menu. Select Add to query and then where to add the column. For Dimension, you can add the attribute column to SELECT, WHERE, GROUPBY, or ORDERBY. For Measures, you can add the attribute column to SELECT or ORDER BY.
When adding a dimension to the WHERE clause, the Add to Where dialog queries the dimensions and then displays a list of values in the dialog. You can conveniently select the desired values to add them to the WHERE clause.
You can use the attribute list Search field to find specific columns.
Pin frequently used attribute columns.
As discussed earlier, you can pin frequently used attributes to the top of the attributes list for easy access. To pin an attribute, left-click on an attribute column in the list to display the Actions menu and select Pin. The attribute appears under the Pinned category at the top of the attributes list. You can select Unpin from the Actions menu to remove the attribute column from the Pinned category.
Create Computed Measures.
You can aggregate data using common SQL aggregates such as SUM, MAX, MIN, and AVG or combine mathematically using formulas to derive meaningful computed measures. To create a computed measure, click on the vertical ellipses of an attribute column from the Measures category to display the Actions menu. Select Add computation to display the Add computation dialog.
Enter the required computation details. Setting substitution variables simplifies defining the Computation. Enter a unique Computed data name and click Add. The Computed data name appears under the Computed category in the attributes list.
Once a computed measure has been added to the attributes list, you can use its Actions menu to perform Delete, Add to query (SELECT or ORDER BY), or Pin/Unpin operations.
Enter query text directly and take advantage of a data object explorer’s type-ahead functionality.
You can also enter SQL directly into the query text entry fields. The following SQL Explorer example illustrates how the type-ahead function works when entering a SQL query.
The type-ahead feature lets you enter SQL while simultaneously providing an in-context list of attribute columns from which to select. You can also use the hot key (CTRL+SPACE) to display the list of attribute columns. You can collapse the text entry area into a read-only single line statement to free up screen real estate.
SQL sub queries are not allowed. For example, embedding a SELECT statement within a WHERE clause or JOIN or UNIION operator.
Select * query will only return one page regardless of the Pages setting.