2.1. Login screen¶
The Query Builder has simple login screen that allows username/password logins. In a single-sign-on some deployments the login screen is not shown.
After login and - depending on the configuration - passing through 2-factor-authentication the “Projects” screen is shown.
2.2. Projects screen¶
The Project Screen gives sortable overview of all projects that the user is authorised for. Before creating or changing queries, the user has to choose a project to work in.
Projects are provisioned through a separate provisioning process. In general a project defines:
- the output destination of queries (e.g. a workspace, a sftp location, or simple download)
- the deidentification project in the deidentification service
The <home> button brings the user back to the Projects screen.
2.3. Queries screen¶
The Queries screen presents the user with an overview of all the queries that are available for the selected project. The screen allows a number of actions:
- Edit a query, by clicking an existing query, the user will be forwarded to the query design screen
- Add a query, by clicking the <Add query> button and putting in a name for the new query
Every query has an “Actions” button that contains the following functions:
- Request approval
The status of the query determines whether a user can export the results of the query to the output destination. Only queries that have the status “Approved” can be executed and exported.
The possible statuses of a query are:
|Not approved:||approval has not yet been requested for the query|
|Pending approval:||approval has been requested but the data steward has not yet approved or denied the request|
|Approved:||the data steward has approved the request|
|Denied:||the data steward has denies the request|
2.4. Query Design screen¶
Queries are built in the Query Design screen.
The screen design screen contains four panes, which are described in the sections below. The upper bar of the screen shows a dropdown list that will allow the user to easily switch between different queries.
The Tables pane gives an overview of all tables that are available in the (pre-configured) source database. Tables are prepended by their schema name (e.g. public.admissions) and when expanded show the attributes of the tables.
2.4.2. Output Columns¶
The columns listed in the Output Columns pane determine the columns that will be in the output of the query. The user can select on or more tables and/or columns from the Tables Pane and drop these into the Output Columns pane.
The pane further offers the following options:
- change the names an output column in the output table by clicking the Title
- change the order an output column in the output table by dragging a column up or down
- add an expression to an output column in the output table by clicking the expression. Every output column defaults to simply select the value of an input column. Expressions like sum, average, variance, minimum, maximum can be added at will.
- delete a column from the query
2.4.3. Filter Conditions¶
The Filter Conditions pane allows a user to apply filters to the data. A query’s filter conditions consist of one or more filter groups. Filter groups contain of a number of conditions of which some should be true (OR filter) or all should be true (AND filter). Filter groups are joined in the same way: some of the filter groups should evaluate to true or all of the filter groups should evaluate to true.
Filter conditions can be added by dropping a column from the Tables pane into one of the Filter groups, selecting the operator (defaults to equal) and fill in the right hand side of equation.
The Result pane shows the results of running the query.
Depending of the configuration of the Query Builder and the role of the user this will show:
- the number of rows selected
- the number of rows selected and per column the number of distinct values (not implemented)
- the number of rows selected and per column a histogram of the values (not implemented)
- the actual output of the query
2.5. Advanced Filtering¶
Beside simple filtering on values, the Query Builder offers the following advanced filter operations:
- Exists in query
- Filter around a date
2.5.1. Exists in query¶
The “exists in” filter operations allow a user to reuse existing (self-built) queries in a filter condition. This makes it easier to create datasets using a step-by-step approach, e.g. by first creating a query that selects a number of patients and subsequently selecting from another table (e.g. medications) for only the patients that exist in the first query.
2.5.2. Filter around a date¶