Exploring Query Properties

Objectives

After completing this lesson, you will be able to:

  • Work with query properties

The Query Panel

The default Query Panel display consists of the following panels:

  • Toolbar
  • Data Outline
  • Result Objects
  • Query Filters
  • Data Preview

The Query Panel: Toolbar

The toolbar buttons for the panels are found in the left corner of the Query Panel.

The Toolbar contains the following options:

OptionDescription
Add QueryAllows users to add a new query to a document.
Data Outline panelAllows users to show or hide the data outline, which contains the Master Perspective and Display by Navigation Path views.
Filters panelAllows users to show or hide the Query Filters panel.
Data Preview panelAllows users to show or hide the Data Preview panel.
Scope of Analysis panelAllows users to show or hide the Scope of Analysis panel.
Add a combined queryAllows users to combine data retrieved from more than one query, using one of the following operators: Minus, Union, Intersection.
Query PropertiesAllows users to display the query properties.
View ScriptAllows users to display the Query Script viewer dialog box.
RunAllows users to run the query you built and retrieve the data from the database. The results of the query appears in a report in the Web Intelligence.
Apply Changes and CloseAllows users to close the Query Panel.

The Query Panel: Results Objects

The Results Objects Panel contains the following options:

OptionDescription
Add Quick FilterIt allows you to apply a filter on a selected object in the Result Objects panel.
RemoveIt allows you to remove a selected object from the Result Objects panel.
Remove AllIt allows you to remove all objects from the Result Objects panel.

The Query Panel: Query Filters

The Query Filters Panel contains the following options:

OptionDescription
Add a subqueryAllows users to run a subquery to restrict the data that the query returns.
Add a database rankingAllows users to use the database ranking feature to retrieve only the top or bottom values from the database. (NOTE: Only if supported by the database driver).
RemoveAllows users to remove the selected object from the Query Filters panel.
Remove AllAllows users to remove all objects from the Query Filters panel.
Add nested filterNested query filters allow you to create more complex filter conditions than is possible when you combine filters at the same level. When you nest filters, you set the order in which they are evaluated.

The Query Panel: Query Properties

The Query Properties contains the following options:

OptionSelect to
Limits - Max rows retrievedSelect a maximum numbers of rows for retrieval. If you use this option, you also need to set a maximum number of rows to be retrieved.
Limits - Max retrieval timeSet a time limit, in seconds, after which the data retrieval process stops.
Sample - Sample result setReturn a sample result set. To use fixed sampling, click Fixed. Sampling is not available in BEx queries. The Fixed option is visible, but not activated if this option is not supported by your data source.
Data - RefreshableAllow the refresh a specific query.
Data - Retrieve duplicate rowsInclude duplicate rows. In a database, the same data may be repeated over many rows. You can choose to have these repeated rows returned in a query, or to have only unique rows returned. This option is not available in BEx queries, or if it is not supported by the underlying database.
Data - Retrieve empty rowsInclude empty rows in the result.
Data - Delete trailing blanksDelete trailing blanks from data providers' values.
Enable query strippingAllow the application to remove objects from queries if they're not used in the report.
Context - Reset contexts on refreshReset the contexts when refreshing the document or query. If you use this option, you need to select a context when you refresh a query.
Security - Allow other users to edit all queriesAllow users with query editing rights to edit queries you have created.

Contexts in a Query

A context is a defined group of objects that share a common business purpose.

This business purpose is usually the type of information that these related objects represent. For example, a sales context is a grouping of all the objects that can be used to create sales queries. A reservations context is a grouping of all the objects that can be used in reservation queries. Contexts are defined in the universe by the universe designer.

You can combine any objects within the same context to create a query. You can also combine objects in different contexts. If you use an object that is common to multiple contexts in a query and it is not possible to determine the best context that fits all the other objects in the query, you are prompted to choose the context to apply.

When you create a query or refresh a document, you may be asked to choose a context before the query can run. Contexts are set up in a universe to avoid ambiguous queries.

You need to choose a context each time you run the query if the Reset contexts on refresh query property is selected in the query properties. You will also need to choose a contexts if you select the Clear contexts option in the query properties.

If prompts have been set for the document, then the Prompts dialog box appears. Otherwise the document content reflects the context you have selected.

You can refresh contexts every time you refresh a query in the query panel.

The Script Viewer

When you build a query, Web Intelligence automatically generates appropriate script (such as SQL) to retrieve the data from the relevant database. You can view and edit this SQL, and copy it to another application if you have the rights to do so.

If in the query panel you modify the script and you remove an object and add a different object to the Results Object pane, the script is regenerated.

Create a Query and Choose a Context

Log in to track your progress & complete quizzes