Visualize Clinical Data using Looker

1. Introduction

Last Updated: 2022-9-22

This codelab implements a solution to access and visualize clinical data aggregated in BigQuery using Looker. It illustrates data exploration of large healthcare datasets using Looker. Looker seamlessly integrates with Google Cloud BigQuery, which makes it quick and simple to start building population and patient level dashboards on Google Cloud.

In this code lab you will learn to:

• Explore an existing LookML project in a pre-provisioned Looker instance.
• Create Looks in Looker.
• Create a population level dashboard in Looker.
• Create a patient level dashboard in Looker.
• Link dashboards together.
• Schedule to send Looks and Dashboards via email with a PDF attachment.

What do you need to run this codelab?

• You need access to a Looker instance. A Looker instance is provisioned for this codelab. You can submit a form to request access to the Looker instance. Access is provisioned instantaneously.
• Our Looker instance accesses data from Bigquery. A test dataset (hcls-public-data:fhir_20k_patients_analytics) in BigQuery has been pre-created for this codelab.

Note: This codelab uses Looker, which supports HIPAA compliance. PHI and PII are not persisted in Looker.

2. Request access to Looker

In this section, you will submit a registration form to get access to a Looker instance. Once you are registered with Looker and get access, you can log in and build reports and dashboards in Looker.

User Registration

1. Fill out this form to register for a new Looker account. Once you SUBMIT the form, you will receive an email to activate your account, set a password, and finish the registration.

2. Once you receive the welcome email from Looker, Activate Your Account.

3. Next, you are taken to the Activation form. Complete the Activation form to set the password and activate your account. Then, you are taken to the Home screen of Looker.

Login to Looker

Use this link to log into Looker.

In this codelab, we provide a pre-created connection for you. This connection connects our codelab LookML project (bqlooker_codelab) to our BigQuery dataset hcls-testing-data:fhir_20k_patients_analysis.

If your organization has its own Looker instance, the admin of the Looker instance can create the connection that connects your LookML project to your own BigQuery dataset. See reference link below

Reference: The instructions in this link explain how to set up a connection in Looker to Google BigQuery Legacy SQL or Google BigQuery Standard SQL.

3. Explore existing LookML Project

1. In Looker, navigate to the Develop dropdown. Select the existing bqlooker_codelab LookML project to start exploring. The Look ML Project's Model and View files window is displayed.

2. Click Validate Again to check for errors. A green "No LookML Issues" indicates success!

3. Click the Explore dropdown to see all existing explores. Explores are basic SQL query building blocks for your reports (Looks).

4. Dimensions and Measurements

Add customized dimensions and measures by modifying LookML xxx.view files

1. Click the Develop dropdown.
2. Slide the purple Development Mode button to turn it on.

3. Click the Explore dropdown and select Patients with Conditions and Medications.
4. Click the ‘gear' symbol next to Count and select Go to LookML to open "patients_with_conditions_and_medications.view".

5. This view can also be accessed from Develop>bqlooker_codelab>Expand the view.
6. Include the following code snippet in the "patients_with_conditions_and_medications.view" file to add "number_of_patients" as a new measure.

  measure: number_of_patients {
    type: count
    drill_fields: [lastname]
  }

The following screen shot shows the code snippet in the file: patients_with_conditions_and_medications.view

7. Click Save. Go back to the Explore -> Patients with Conditions and Medications window and refresh the screen to verify a new measure "Number of Patients" was added.

8. Click the Explore dropdown and select the Patient Vital Info.
9. Click the ‘gear' symbol next to the Count field and select "Go to LookML" to open "patient_vital_info.view".
10. Include the following code snippet in the ‘patient_vital_info.view' file to add ‘RoundedValue' as a dimension, and ‘DIA' and ‘SYS' as measures.

dimension: RoundedValue {
    type: number
    sql: round(${value},2);;
  }

  measure: DIA {
    type: average
    sql:
     CASE
       WHEN ${patient_vital_info.measurement} like "%Diastolic%"
       THEN round(${value},0)
     END;;
  }

  measure: SYS {
    type: average
    sql:
     CASE
       WHEN ${patient_vital_info.measurement} like "%Systolic%"
       THEN round(${value},0)
     END;;
  }

The following screen shot shows the code snippet in the file:

11. Click Save. Go back to the Explore -> Patient Vital Info window and refresh the screen to verify the new fields were added.

5. Looks

Create Folder structure to organize Looks

1. Click Browse -> Select your Personal Folder (e.g. Codelab User's folder)
2. Click the New button and select Folder

3. Provide the folder name: ‘Population Management' and click the Create folder button

4. Repeat steps 1, 2 and 3 to create another folder by name ‘Patient Level Visualization' under your personal folder.
5. You should have two folders under your personal folder.

Build Looks for Population Management

In Looker, click the Explore dropdown, then select Patients with Conditions and Medications to build reports and views (also known as Looks).

Build a Patients list by conditions and number of medications Look with the following steps:

1. Click the FILTER button next to "Condition Desc" and "Med Count" dimensions to add filtering criteria. In this codelab, we filter data by the following two criteria:

• Condition Desc is equal to Diabetes, Diabetes_Hypertension, Hypertension.
• Med Count is greater than or equal 13.

2. Select the "Patient ID", "Med Count", and "Condition Desc" dimensions by highlighting each field to add "Patient ID", "Med Count" and "Condition Desc" data elements in the RESULTS section.

3. Click the "Table" icon in the Visualization tab to add "Patient ID", "Med Count" and "Condition Desc" data elements to the view.

4. Click Run to get the results.

5. Click the EDIT "down arrow" in the Visualization tab to add and update information in the view. Place cursor in each textbox and type the desired text to overwrite the default text.

6. Click the "gear" symbol next to the Run button and select "Save as a Look".

7. Select the ‘Population Management' folder.
8. Provide the Title and Description.

9. Click the Save & View Look or Save button to save the Look.

• If the Save & View Look button is clicked, the Look view is displayed.

• If the Save button is clicked, a green banner appears at the top of the Explore view. Click the "Patients list by conditions and number of medications" link to open the Look (displayed as if "Save & View Look" button was clicked). Click the "Population Management" link to open the folder. From there, you can select the Look to open it.

Build the Total number of patients by conditions Look with the following steps:

1. Click the FILTER button next to the "Condition Desc" and "Med Count" dimensions to add filtering criteria. In this codelab, we filter data by following two criteria:

• Condition Desc is equal to Diabetes, Diabetes_Hypertension, Hypertension.
• Med Count is greater than or equal 13.

1. Select the "Number of Patients" measure by highlighting the field to add the ‘number of patients' data element in the RESULTS section.
2. Click the "Single Value" icon in the Visualization tab to add the ‘Number of patients' data element to the view.
3. Click Run to get the results.

4. Click the EDIT "down arrow" in the Visualization tab to add and update information in the view. Place cursor in each textbox and type the desired text to overwrite the default text.
5. Click the "gear" symbol next to the Run button and select "Save as a Look".
6. Select the ‘Population Management' folder. Provide the name and description.
7. Click the Save & View Look or Save button to save the Look.

• If the Save & View Look button is clicked, the Look view is displayed.
• If the Save button is clicked, a green banner appears at the top of the Explore view. Click the "Total number of patients by conditions" link to open the Look (displayed as if "Save & View Look" button was clicked). Click the "Population Management" link to open the folder. From there, you can select the Look to open it.

Build Patient count by number of medications Look with the following steps:

1. Click the FILTER button next to the "Condition Desc" and "Med Count" dimensions to add filtering criteria. In this codelab, we filter data by following two criteria:

• Condition Desc is equal to Diabetes, Diabetes_Hypertension, Hypertension.
• Med Count is greater than or equal 13.

2. Select the "Med Count" dimension and the "Number of Patients" measure by highlighting each field to add "Med Count" and "Number of Patients" data elements in the RESULTS section.
3. Click the "Column" icon in the Visualization tab to add "Med Count" and "Number of Patients" data elements to the view.
4. Click Run to get the results.

5. Click the EDIT "down arrow" in the Visualization tab to add and update information in the view. Place cursor in each textbox and type the desired text to overwrite the default text.
6. Click the "gear" symbol next to the Run button, and select "Save as a Look".
7. Select ‘Population Management' folder. Provide name and description.
8. Click Save & View Look or Save button to save the Look.

• If the Save & View Look button is clicked, the Look view is displayed.
• If the Save button is clicked, a green banner appears at the top of the Explore view. Click the "Patient count by number of medications" link to open the Look (displayed as if "Save & View Look" button was clicked). Click the "Population Management" link to open the folder. From there you can select the Look to open it.

Build Patient count by conditions Look with the following steps:

1. Click the FILTER button next to the "Condition Desc" and "Med Count" dimensions to add filtering criteria. In this codelab, we filter data by following two criteria:

• Condition Desc is equal to Diabetes, Diabetes_Hypertension, Hypertension.
• Med Count is greater than or equal 13.

2. Select the "Condition Desc" dimension and the "Number of Patients" measure by highlighting each field to add "Condition Desc" and "Number of Patients" data elements in the RESULTS section.
3. Click the "Pie" icon in the Visualization tab to add "Condition Desc" and "Number of Patients" data elements to the view.
4. Click Run to get the results.

5. Click the EDIT "down arrow" in the Visualization tab to add and update information in the view.
6. Click the "gear" symbol next to the Run button, and select "Save as a Look".
7. Select ‘Population Management' folder. Provide Title and Description.
8. Click Save & View Look or Save button to save the Look.

• If the Save & View Look button is clicked, the Look view is displayed.
• If the Save button is clicked, a green banner appears at the top of the Explore view. Click the "Patient count by conditions" link to open the Look (displayed as if "Save & View Look" button was clicked). Click the "Population Management" link to open the folder. From there you can select the Look to open it.

Patients population reports summary

In Looker, click the Browse dropdown, select <<Personal Folder>>, then select "Population Management" folder to verify that four reports (also known as Looks) were created.

Build Looks to show Patient's Vital Information

In Looker, click the Explore dropdown, then select Patient Vital Info to build reports and views (also known as Looks).

Build Patient's Blood Pressure measurement Look with the following steps:

1. Click the FILTER button next to the "Dateofmeasurement Date", "Measurement" and "Patient ID" dimensions to add the filtering criteria. In this codelab, we filter data by following three criteria:

• Dateofmeasurement Date is in the past 10 years.
• Measurement contains Blood Pressure.
• Patient ID is equal to 287b4723-9c8d-495a-9915-54386d8dcc31.

2. Select the "Dateofmeasurement Date" dimension, and "Dia" (Diastolic) and "Sys" (Systolic) measures by highlighting each field to add the "Dateofmeasurement Date", "Dia" (Diastolic) and "Sys" (Systolic) data elements in the RESULTS section.

3. Click the "Line" icon in the Visualization tab to add the "Dateofmeasurement Date", "Dia" (Diastolic) and "Sys" (Systolic) data elements to the view.

4. Click Run to get the results.

5. Click the EDIT "down arrow" in the Visualization tab to add or update information in the view. Place the cursor in each textbox and type the desired text to overwrite the default text.

6. Change the filter criteria Dateofmeasurement Date so it is in the range of 2015-04-02 until (before) 2015-08-02 to expand the cluster of Blood Pressure measurements.

7. Click the "gear" symbol next to the Run button and select "Save as a Look".
8. Select the ‘Patient Level Visualization' folder. Provide the Title and Description.

9. Click the Save & View Look or Save button to save the Look.

• If the Save & View Look button is clicked, the Look view is displayed.

• If the Save button is clicked, a green banner appears at the top of the Explore view. Click the "Blood Pressure" link to open the Look (displayed as if "Save & View Look" button was clicked). Click the "Patient Level Visualization" link to open the folder. From there you can select the Look to open it.

Build Patient's Body Mass Index measurement Look with the following steps:

1. Click the FILTER button next to the "Dateofmeasurement Date", "Measurement" and "Patient ID" dimensions to add filtering criteria. In this codelab, we filter data by following three criteria:

• Dateofmeasurement Date is in the past 10 years.
• Measurement contains Body Mass Index.
• Patient ID is equal to 287b4723-9c8d-495a-9915-54386d8dcc31.

2. Select the "Roundedvalue", "Dateofmeasurement Date", and "Measurement" dimensions by highlighting each field to add "Roundedvalue", "Dateofmeasurement Date", and "Measurement" data elements in the RESULTS section.
3. Click the "Line" icon in the Visualization tab to add "Roundedvalue", "Dateofmeasurement Date", and "Measurement" data elements to the view.
4. Click Run to get the results.

5. Click the EDIT "down arrow" in the Visualization tab to add and update information in the view.
6. Click the "gear" symbol next to the Run button, and select "Save as a Look".
7. Select ‘Patient Level Visualization' folder. Provide Title and Description.
8. Click Save & View Look or Save button to save the Look.

• If the Save & View Look button is clicked, the Look view is displayed.
• If the Save button is clicked, a green banner appears at the top of the Explore view. Click the "Body Mass Index" link to open the Look (displayed as if "Save & View Look" button was clicked). Click the "Patient Level Visualization" link to open the folder. From there you can select the Look to open it.

Build Patient's Hemoglobin A1c measurement Look with the following steps:

1. Click the FILTER button next to "Dateofmeasurement Date", "Measurement" and "Patient ID" dimensions to add filtering criteria. In this codelab, we filter data by following three criteria:

• Dateofmeasurement Date is in the past 10 years.
• Measurement contains Hemoglobin A1c.
• Patient ID is equal to 287b4723-9c8d-495a-9915-54386d8dcc31.

2. Select the "Roundedvalue", "Dateofmeasurement Date", and "Measurement" dimensions by highlighting each field to add "Roundedvalue", "Dateofmeasurement Date", and "Measurement" data elements in the RESULTS section.
3. Click the "Line" icon in the Visualization tab to add "Roundedvalue", "Dateofmeasurement Date", and "Measurement" data elements to the view.
4. Click Run to get the results.

5. Click the EDIT "down arrow" in the Visualization tab to add or update information in the view.
6. Click the "gear" symbol next to the Run button, and select "Save as a Look".
7. Select ‘Patient Level Visualization' folder. Provide Title and Description.
8. Click Save & View Look or Save button to save the Look.

• If the Save & View Look button is clicked, the Look view is displayed.
• If the Save button is clicked, a green banner appears at the top of the Explore view. Click the "Hemoglobin A1c" link to open the Look (displayed as if "Save & View Look" button was clicked). Click the "Patient Level Visualization" link to open the folder. From there you can select the Look to open it.

Build Patient's Measurements Table Look with the following steps:

1. Click the FILTER button next to "Dateofmeasurement Date" and "Patient ID" dimensions to add filtering criteria. In this codelab, we filter data by following two criteria:

• Dateofmeasurement Date is in the past 10 years.
• Patient ID is equal to 287b4723-9c8d-495a-9915-54386d8dcc31.

2. Select the "Dateofmeasurement Date", "Measurement", "Roundedvalue", and "Unit" dimensions by highlighting each field to add "Dateofmeasurement Date", "Measurement", "Roundedvalue", and "Unit" data elements in the RESULTS section.
3. Click the "Table" icon in the Visualization tab to add "Dateofmeasurement Date", "Measurement", "Roundedvalue", and "Unit" data elements to the view.
4. Click Run to get the results.

5. Click the EDIT "down arrow" in the Visualization tab to add and update information in the view.
6. Click the "gear" symbol next to the Run button, and select "Save as a Look".
7. Select ‘Patient Level Visualization' folder. Provide Title and Description.
8. Click Save & View Look or Save button to save the Look.

• If the Save & View Look button is clicked, the Look view is displayed.
• If the Save button is clicked, a green banner appears at the top of the Explore view. Click the "Measurements Table" link to open the Look (displayed as if "Save & View Look" button was clicked). Click the "Patient Level Visualization" link to open the folder. From there you can select the Look to open it.

Patient's Vital Information reports summary

In Looker, click the Browse dropdown, select <<Personal Folder>>, then select "Patient Level Visualization" folder to verify that four reports (also known as Looks) were created.

6. Dashboards

There are several ways to create a dashboard in Looker:

• From scratch
• From a saved Look
• From a query on the Explore page.

This codelab will show you how to create a Dashboard from a saved Look.

Reference: https://docs.looker.com/dashboards/creating-user-dashboards

Build Population Dashboard

1. Browse to your personal folder or subfolder (for example, Population Management) where the Looks are located.
2. Select the Look to open (for example, Patients list by conditions and number of medications).
3. Click the "gear" symbol, select "Save to Dashboard" or click "Add To Dashboard" link to add the Look to a dashboard.

4. Click the New Dashboard button. Provide a dashboard name, then click OK. Or select an existing dashboard.
5. Click the Add Look to Dashboard or Save to Dashboard button. Either choice will add the Look into the dashboard.
6. If the Add Look to Dashboard button is clicked, the Dashboard link will appear in the "On Dashboards" section of the Look view. This symbol also appears in the Folder view.

2. If the Save to Dashboard button is clicked, ‘No' will appear in the "On Dashboards" section of the Look view.

6. Repeat steps 1 through 5 to add more Looks onto the dashboard.
7. Click Browse dropdown, select <<Personal Folder>>, then select "Population Management" folder. Verify all four Looks were added to the new "Population Dashboard".

8. Click "Population Dashboard" to open it.
9. Click the Edit button to rearrange the tiles as desired.
10. Add Filters to the Population Dashboard by clicking the Filters menu.
11. Click the New Filter button.
12. Provide a filter name (for example, Numbers of Medications Taken), then select the type (for example, Field).
13. In the ‘Select a field to link to this filter' section, choose Model (for example, Bqlooker Codelab), choose Explore (for example, Patients with Conditions and Medications), and choose Field (for example, Med Count).
14. Set the default value (For example, Med Count >= 13).
15. Select the dashboard tiles to update when this filter changes by clicking All button or slide the purple button for each tile.
16. Click Save.

11. Click Done Editing, then Run the dashboard again to ensure all filters are working as expected.

Build Patient Dashboard

1. Browse to your personal folder or subfolder (for example, Patient Level Visualization) where the Looks are located.
2. At the upper right corner, click the New button, select Dashboard, and provide a name (for example, Patient Dashboard).

3. Click the Create Dashboard button.

1. Option 1: Click New Tile, select an Explore, and start building a Tile, which is the same as a Look.
2. Option 2: Click the Add Looks link, click +Add button to add a Look (or Remove to remove a look), and click Update Dashboard button. This Codelab used Option 2.

4. The Patient Dashboard is displayed in the Edit mode. Rearrange the tiles as desired.
5. Add the Patient and Date filters to the Patient Dashboard by clicking the Filters menu.
6. Click the New Filter button.
7. Provide a filter name (for example, Patient), then select the type (for example, Field).
8. In the ‘Select a field to link to this filter' section, choose Model (for example, Bqlooker Codelab), choose Explore (for example, Patient Vital Info), and choose Field (for example, Patient ID).
9. Set the default value (for example, Patient ID = 287b4723-9c8d-495a-9915-54386d8dcc31).
10. Select the dashboard tiles to update when this filter changes by clicking All button or slide the purple button for each tile.
11. Click Save.
12. Repeat step 5 to add the Date filter (with choose Field = Dateofmeasurement Date, default value is in the past 10 years).

7. Click Done Editing, then Run the dashboard again to ensure all filters are working as expected.

8. Change the Date filter so that it is in the range 2015-04-02 until (before) 2015-08-02 to expand the ‘cluster' of blood pressure measurements.
9. Click Done Editing, then Run the dashboard again to ensure all filters are working as expected.

10. Click the Browse dropdown, select <<Personal Folder>>, then select "Patient Level Visualization" folder, verify all 4 Looks were added to the new "Patient Dashboard".

7. Link Dashboards

In this codelab, we create a link from the Population Dashboard to the Patient Dashboard by following these steps:

1. Click the Browse dropdown, select <<Personal Folder>>, then select "Patient Level Visualization" folder.
2. Open the ‘Patient Dashboard', and copy the URL (for example, https://googlecodelabs.gw1.cloud.looker.com/dashboards/9?Patient).

3. Click the Develop dropdown, slide the purple button to turn on Development Mode, then select bqlooker_codelab LookML Project.

4. Select to open "patients_with_conditions_and_medications.view" in your personal branch and add the following code snippet inside the dimension: patient_id{} to create a link from the Population Dashboard to the Patient Dashboard.

 link: {
     label: "View key vitals"
     url: "https://googlecodelabs.gw1.cloud.looker.com/dashboards/9?Patient={{value}}"
 }

Note:

Must use the exact number (9 in this example) from the copied URL of your ‘Patient Dashboard'

" https://googlecodelabs.gw1.cloud.looker.com/dashboards/9?Patient= {{value}}"

{{value}} is variable for PatientID.

The following screen shot shows the code snippet in the file:

5. Click the Save button at the upper right corner.
6. Browse and open the Population Dashboard.
7. Click on the "..." symbol next to a Patient ID.
8. Select "View key vitals" to open the Patient Dashboard.

8. Schedule delivery of Looks and Dashboards via email

1. Navigate to the Develop dropdown and slide the purple button to turn off Development Mode.
2. Browse to your dashboards and select a dashboard.
3. Click the "3 vertical dots" symbol and select Add Schedule.

4. Fill out all the information.
5. Select options as desired.
6. Add email addresses (click Add).
7. Click Save All.
8. Click the Send Test button to test the delivery of the dashboards. In this example, an email is sent every 5 minutes from 6 AM-6 PM.

9. Check your mailbox for emails sent from Looker with the dashboard attached as a PDF file.

10. Click the "3 vertical dots" symbol and select Edit Schedule at anytime to edit schedule.

11. Apply a similar mechanism (step 1 through 10) for scheduling Looks to be sent out.

9. Cleanup

Looker account

Your account in Looker may get deleted in about 10 days.

10. Congratulations

Congratulations, you've successfully completed the code lab to access, query, and visualize FHIR formatted clinical data using Looker.

You accessed a public BigQuery dataset in GCP.

You explored an existing LookML Project in a pre-provisioned Looker instance.

You created Looks and Dashboards in Looker.

You linked dashboards together.

You scheduled to send Looks and Dashboards via email with PDF attachment.

You now know the key steps required to start your Healthcare Data Analytics journey with BigQuery and Looker.

©Google, Inc. or its affiliates. All rights reserved. Do not distribute.