Queries made to the database to obtain more detailed information than in the contents of the multidimensional model are defined from tables or views existing in the DBMS. The views are used in those cases when the query involves fields from more than one table.
Configuration of query elements means:
- Identifying the query with a adequate name
- Associating the name of the table or view over which the query is made.
- Defining the fields of the query that you wish to view as columns in the Drill Through pane.
- Defining the fields you wish to use as filters in the query, allowing the user to restrict its result to the values stated in them.
See Defining Filters
The O3 Browser File menu contains the Drill Through functionality with the options to define a new query and to edit an existing one, besides defining connections to the database.
The following picture shows the Editor for a new Query.
The configuration window displays two panes:
- Definitions Pane (left pane)
- Properties pane (right pane)
The definitions pane contains a first node with the "Drill Through" label whose properties define the query to the database.
Filters are added to the node labeled "Filters" in the definitions pane. Their attributes are defined in the properties pane and are described later in this section.
The following section describes the query configuration properties.
Defining the Query
The properties of the query define the set of records that must be shown at the time of requesting the detailed query in O3 Browser, as well as the fields that you wish to display as its result.
To define a query you must fill up the following information:
- Name: It is a name given to the query.
- Scheme: It is the connection to the database you need to use to access the data. It must be selected from the list of available connections. Available connections are those previously defined. (See Connection to the database)
- Name of the Table: It is the name of the table or view of the database over which the query is made.
- Condition: Defines the WHERE clause for the query
- Cube: It is the cube to which the detailed query will be associated.
It is important to select the cube so that filters associated to the cube dimensions can be defined.
The button to the right lets you select a cube from the file system.
The association reflected in this pane is only made in order to state the existing relationship between the query filters and the cube dimensions. - Available fields: They define the fields you wish to show in the detailed query.
To do so, you must specify the following data:- Name: it is the name of the field defined in the database itself. It cannot be modified.
- Alias: It is the label of the field in the detailed query pane.
- Type: It is the type of data in the field.
- Add Field Button: It lets you add a field manually. To do so, you must define the 3 data described before.
- Remove Field Button: It deletes the selected field. If no field has been selected the last one is deleted.
- Autofill Button: It automatically defines the "Available Fields" from the specified query, that is, it defines as available all those fields present in the previously defined table or view.
- Save Button: It saves the changes made in the configuration of the query, both in the properties and in the filters.
Definitions are saved in a XML file. - Save As Button: It is similar to the Save Button but it lets you enter a different name for the file.
To create a new query you must follow these steps:
- Go to the Tools menu and open the Drill Through option -> New Drill Through.
- Define the query properties. This means completing each one of the fields described above.
- Save the properties with the "Save" or "Save as..." buttons.
If you wish to modify a previously defined query, the connection to the database used by the query must be available.
To modify an existent query proceed as follows:
- Go to the Tools menu and open the Drill Through option -> Edit Drill Through.
- Choose the desired query through the open dialogue
- Modify the desired properties in the query.
- Save changes with the "Save" or "Save as..." buttons.