Working with the query properties

We can also manage query properties by navigating to the query options located on the Query Panel.

Getting ready

We want to edit the query properties as well as discover the available options.

How to do it...

In the main Query Panel toolbar, we will click on the query properties button located to the right of the combined query icon, as shown in the following screenshot:

The Query Properties panel will appear, enabling us to change the query properties, as shown:

We can find the following options in this panel:

• Name: This is similar to the previous recipe's discussion.
• Universe This is the query source name.
• Limits: Using this, the number of rows in the query and the maximum retrieval time(s) can be limited; these limits can't exceed the universe definitions.

  This feature is mainly good for QA purposes and when we want to quickly apply formatting on a small amount of data while keeping the running time as short as possible. This option can't override the universe limitations or the database limitation, as it's not possible nor would it be logical to increase the number of return rows to 2 billion rows.

  Also note that query results can't be limited for real reporting as it will require the entire dataset to be fetched.

• Sample: This is a function that enables us to fetch fixed random results. As opposed to the Limits option that will always bring the same result, the Sample option will use random logic and fetch different rows each time (applied, of course, by the query result objects and filters).

  This feature is great for QA purposes and for supporting pilot requests from the business, such as sampling different client groups for the marketing department.

• Retrieve duplicate rows: When we are retrieving results, the same data can be repeated. This option eliminates duplicate values as it is checked by default. Unchecking this option will create a Select Distinct statement.

  Note that by default Web Intelligence doesn't present a duplicate row, which is the same row having the exact same values in all its columns.

  Usually, duplicated rows are eliminated when using measures that created aggregations as per the dimension values.

• Retrieve empty rows: This is an option that is relevant to OLAP data sources.
• Allows other users to edit all queries: This locks or enables the Edit icon in the report layer. By doing so, we prevent/allow other users to edit the query.
• Query stripping: This is a great option to improve the query performance. The Query stripping option indicates those objects that are included in the query but not used in the report. These objects should ideally be removed from the query.

  Note

  The Query stripping option is available for OLAP-based universes (UNX) and relational universes, including HANA from version 4.1.

• Prompt Order: Since prompts are arranged alphabetically by the prompt text, a custom prompt order can be defined to match the user preferences when the query is run.
• Reset Contexts on refresh: There are cases where the user is required to pick up a context before he or she runs the query, as shown in the following screenshot:
  

  Unchecking this option will select the context that was chosen without prompting the Context screen anymore, as shown here:

  

How it works...

As we have seen, the query properties are a set of options that the user can use to adjust the query for different purposes: better QA, prompt and context control, as well as performance improvements by using the Query stripping option.

See also

• For further information on how to quality-assure the results of a query, see Appendix, Applying Best Practices, QA, and Tips and Tricks to Our Reports