Intellicus Enterprise Reporting and Business Insights 18.1

Query Object Properties

0 views June 28, 2020 0

Connection

Query Object at the top level has two properties:

Property Values Comments
Name Type yourself Name of the Query Object
Connection Select from list (Default) = Use default connection available at run time based on priority of user selection or server configuration

Connection Name = Always run using this named connection, irrespective of user selection

Access Rights

Access rights on a Query Object can be granted to everyone, selected organizations or select users. Access rights given at Query Object level supersede folder level access rights.

Access rights applicable to Query Object (as specified under Navigate > administration > Manage Users > Access Rights) are:

Access Right Comments
Read User can see the definition of the Query Object

User cannot edit, delete the Query Object

User cannot run reports using this Query Object

Write User can delete, edit the Query Object.

This right when given at folder level, user can create new Query Object in that folder, provided user has Data Administrator system privilege

Execute User can execute reports using this Query Object.

User can create new reports using this Query Object

Advanced Properties

Advanced properties can be set at each Query Object level to control the behavior of the Query Object and reports generated using this Query Object. Click Advanced icon advanced icon on the main menu to open the list of properties as discussed below.

Advanced Properties
Figure 18: Advanced Properties

Property Values Comments
Audit Log (Default)

Enable

Disable

You can switch ON or OFF audit logging for reports generated using this Query Object, irrespective of global audit logging setting
Run Priority (Default)

Low

Medium

High

Decides the priority in the request queue of Report Server
Database Connection Timeout Type Yourself Overrides the same property value at connection or global level
Data Source Fetch Size Type Yourself Overrides the same property value at connection or global level
Max. Rows Type Yourself Maximum rows restriction from this Query Object.

Report level Max. Rows value can further downsize, but that cannot upsize this value

Query Execution (Default)

Synchronous

Asynchronous

 

Asynchronous = Useful to free rendering thread when database is taking long time to process the data before it starts sending data in.

Example: Heavy sorting at database, Complex procedures processing data before sending data

 

Synchronous = thread waits after sending database request till data returns

Restrict To Background (None)

Enable

Disable

Enable = Reports using this Query Object shall be allowed by submitting to Run in back ground only.

Useful for long time taking Query Objects.

Disable = Both Run and Run in background options will be available.

This facility is dependent on scheduler licensing

Restrict To Formats (None)

List of available formats

(None) = reports using this Query Object can run in all supported formats

Selected Values = Reports using this Query Object will be allowed to run only mentioned formats.

 

For example, a report with few million rows in the output may be ok only in XLS and Raw text formats

Default Memory Usage Per Exec Type Yourself Overrides the same property value at connection or global level
Report Server Chunk Timeout Type Yourself Overrides the same property value at connection or global level
Sort Area Size Per Exec Type Yourself Decides the number of records that can reside in memory for all sort threads of a report

 

Overrides the same property value at connection or global level

Sort Threads Per Exec Type Yourself Decides thread limitations set for in-memory sorting of rows.

Overrides the same property value at connection or global level

Data Caching Enable

Disable

Enable= To create a copy of data in local data store retrieved by a Query Object for in-view and post-view operations of a report up to a specific time
Update Fields At Runtime Enable

Disable

Enable = If database query returns new fields at run time, this Query Object exposes all the fields to the user on Ad hoc Wizard or Power Viewer
Data Cache

Data Cache creates a copy of data in local data store retrieved by a Query Object using a specific set of business parameter values for a specific user. This can be re-used for in-view and post view operations of a report up to a specific time.

The cache is created from result set of Query Object and re-used for in-view and post-view purposes that are automatically identified to work from this result set.

The operations that would re-use cached data are:

  1. Add/Modify/Remove filter
  2. Add/Modify/Remove highlighting
  3. Add/Modify/Remove grouping
  4. Add/Modify/Remove sorting
  5. Add/Modify/Remove Charts, Matrix
  6. Load lookup values derived from main result set

The Data Cache life is determined by DATA_CACHE_PURGE_TIME (in minutes) property in Server Properties. This is set to 30 minutes by default.

Caution
  1. When Query Object returns large data set with business parameters but filtered to small set with ad hoc filters, then enabling Data Cache is not recommended. The first run of reports could be too slow.
  2. Dynamically constructed Query Object SQLs may cause cache to be used even when new fields are added in in-view process resulting in incorrect result set. Enabling Data Cache is recommended only for static fields Query Objects.
  3. Data Cache can be re-used across report run requests depending on matching of business parameters and Cache Purge Time.
  4. Local data store may not support some field nomenclature; you may have to rename Query Object fields by aliasing or other means to comply with local store, in such case.
Update fields at Run time

Adding newly returned fields from a SELECT * type of SQL or a dynamic result set returning procedure to Query Object at run time is achieved by this property.

The newly added fields do not get saved in the definition of the Query Object permanently. The newly added fields are available in transient at each run time.

Caution

When Query Object is marked for Update fields at run time, then the Query Object may have to be executed more than once to find newer fields, causing slowness.