Create cohort with Cohort Builder (from Catalogue)

[ 1 Access to Cohort Builder ] [ 2 Steps ] [ 3 Cache ] [ 4 Cumulative Total ] [ 5 SQL Query behind the scene ] [ 6 When something goes wrong... ] [ 7 Collation issue ]

Cohort builder is a very intuitive view which simplify the way cohort can be defined using catalogues and filters. SQL code which runs when executed is still available if there is any doubt on what is executed

Access to Cohort Builder

Accessing the cohort builder can be done in two ways. Either from the Home page and select your configuration and ' Open'

Or by selecting 'Cohort Builder' from the menu. This will open the cohort builder in the left hand pane and the configuration can be selected from there. For step 1 we have chosen this method.

Steps

Step 1- Double click your chosen configuration and this will open in the central pane.

Step 2 – Right click the catalogue that requires a filter and from the menu presented select 'Add Filter Container'

Step 3 -The filter container added is defaulted to 'AND' but this can be changed to 'OR' depending on your filter requirements by right clicking on the container.

Step 4 – to add a filter has two options, this catalogue has no previous filters published to it therefore the option to 'Create New Filter FROM Catalogue' is 'greyed' out and the only option available is to 'Create New Filter'

This will create a new tab which has a check box 'IsMandatory'
Is Mandatory – allows the filter to always appear every time this catalogue is added to the configuration and cuts down on errors and repetition.

Step 6 – For this example I have gone with filtering the Admissions catalogue for admissions after '2012'. In the blank filter shown above (step 5) create your title, type a description then type the code required for the filter. Then select the 'Save' button.

You will note that on the left of the example below I have expanded the Catalogue Items to enable me to see the item name I want to filter on.

If you would like the filter to be available for future use with this catalogue. From the 3 lines in the top left expand the menu and select 'Publish Filter'.

This will only be available if you have populated the description field of the filter.

Step 7 - Close the Filter once complete and then check it runs by selecting the 'Execute' button to the right. This enables you to run the catalogue in isolation to the whole configuration.

Cache

At first execution, RDMP will memories the results for each step of the execution into a cache. This cache will allow next execution to run quickly. It is easy then to follow in which order the cohort executed with the numbers appearing in Cached column. The final figure will be the one with the highest number.

The execution of the result for a parameter is possible with “Execute” button on each line.

The count column indicate figures for individual sections on the cohort

It is possible to clear the cache (if data behind the scene have changed for instance): https://hicservices.atlassian.net/wiki/spaces/RDMPDOCUME/pages/1149236

Cumulative Total

In order to obtain the cumulative Total (reduced count along the different steps of execution), make sure the tick is on by clicking on Cumulative Totals before running the cohort with the main button Execute.

SQL Query behind the scene

When the cohort built using RDMP builder is complex, it can be helpful to make sure the SQL code which runs behind the scene is correct. The code is available before and after execution. For that, click right on cohort in the tree menu on he left then View SQL

By default, if the code has already been executed (not commit), the SQL code with displayed the access to cached tables. The original code with access to source tables is available from SQL code page, by clicking on the button Use Cache (if available) in order to have it disabled.

When something goes wrong...

If there has been a problem with the filter you will see a message in the 'working' column that says crashed. By right clicking on 'crashed' you will expand a menu and by selecting 'View crash message' it will display the reason. For example

Return to the filter to amend the code. When successful the 'working' column will display 'Finished' You will also get the number of records available with that filter applied.

Collation issue

By including catalogues from various sources it can cause Collation issues and crash the built in particular with index table. In order to resolve this issue, include the collation command within the built this way:

RDMP