Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Current »

To create slices (charts) and dashboards in Superset, we need to connect Superset to a database and configure a table in that database for analysis. We can then explore the data that we have exposed and add a visualization to a dashboard so that we get a feel for the end-to-end user experience.

Connecting to a new database

The data pool that we have created earlier will be the database we will connect to from the instance on which we are running the Superset.

Under the Sources menu, select the Databases option:

On the resulting page, click on the green plus sign, near the top right:

To configure the database, enter following details:

  1. Name your database

  2. Provide the SQLAlchemy Connection URI and test the connection:

Click the Test Connection button to confirm things work end to end. Once Superset can successfully connect and authenticate, you should see a popup like this:

Moreover, you should also see the list of tables Superset can read from the schema you’re connected to, at the bottom of the page:

If the connection looks good, save the configuration by clicking the Save button at the bottom of the page.

Adding a new table

Now once a database is configured, the next step is to add specific tables to Superset that we would like to query.

Under the Sources menu, select the Tables option:

On the resulting page, click on the green plus sign, near the top left:

To add the table to Superset, we need to enter certain details as follows:

  1. The name of the table

  2. The target database from the Database drop-down menu

  3. Enter the database schema (optional).

  4. Click on the Save button to save the configuration

Edit the table

Click on the edit button next to the table you’ve created:

On the resulting page, click on the List Table Column tab. Here, you’ll define the way you can use specific columns of your table when exploring your data. We’ll run through these options to describe their purpose:

  • If you want users to group metrics by a specific field, mark it as Groupable.

  • If you need to filter on a specific field, mark it as Filterable.

  • Is this field something you’d like to get the distinct count of? Check the Count Distinct box.

  • Is this a metric you want to sum, or get basic summary statistics for? The Sum, Min, and Max columns will help.

  • The is temporal field should be checked for any date or time fields. We’ll cover how this manifests itself in analyses in a moment.

List of Databases Connected to Intelehealth Superset instance

The names & the corresponding SQLAlchemy Connection URI connections are as follows:

  1. Database - AFI

    1. Database Schema - OpenMRS_AFI

    2. SQLAlchemy Connection URI - mysql://dbslave:dbslave_afi@13.229.131.242:3307/OpenMRS_AFI?charset=utf8

  2. Database - VSO

    1. Database Schema - OpenMRS_VSO

    2. SQLAlchemy Connection URI - mysql://dbslave:dbslave_vso@13.229.131.242:3308/OpenMRS_VSO?charset=utf8

List of Tables added to AFI Database

Sr. No.

Object Type

Object Name

Added Columns

Comments

1

Table

person_attribute

Year
Month

The AFI dashboard filter box is based on these newly added columns

2

Table

visit_attribute

Year
Month
MonYear

3

Table

obs

Year
Month
MonYear
Symptoms

4

Table

encounter

Year
Month
Hour

5

Table

patient_identifier

Year
Month

6

Table

visit

Year
Month
MonYear

7

Table

person

Year
Month

8

Table

person_address

Year
Month

9

CSV Upload Table

AFI_Village_Data

This data was uploaded to create a static chart for Village wise Patient Distribution data for AFI

10

SQLlab Query

Doctorwisedistribution

Query -
SELECT obs.person_id as 'Patients',
username,
Concat("Dr.",family_name) as 'Doctor',
last_day(obs.date_created) AS Last_Date,
date_format(obs.date_created,'%Y%m') AS 'MonYear',
date_format(obs.date_created,'%Y') AS 'Year',
date_format(obs.date_created,'%m') AS 'Month'
FROM obs
inner join users on obs.creator = users.user_id
inner join person_name on users.person_id = person_name.person_id
WHERE obs.voided=0
and concept_id = '163219'
order by 3,4,5,2

11

SQLlab Query

PatientExperience

Query -
SELECT LAST_DAY(obs_datetime) AS LASTDATE,
date_format(a.obs_datetime, '%Y%m') AS MonYear,
date_format(a.obs_datetime, '%Y') AS Year,
date_format(a.obs_datetime, '%m') AS Month,
0 as Value_Text,
0 as Value_Wt,
(count(distinct person_id) -
count(distinct(case concept_id
when 163343 then person_id
else null
end))) as 'PATIENTS',
0 as 'PAT_WT',
0 as 'Weight'
FROM obs a
WHERE voided = 0
GROUP BY LAST_DAY(obs_datetime),
date_format(obs_datetime, '%Y%m'),
date_format(obs_datetime, '%Y') ,
date_format(obs_datetime, '%m')
UNION
SELECT LAST_DAY(obs_datetime) AS 'LASTDATE',
date_format(obs_datetime, '%Y%m') AS MonYear,
date_format(obs_datetime, '%Y') AS Year,
date_format(obs_datetime, '%m') AS Month,
value_text AS Value_Text,
1 as Value_Wt,
COUNT(DISTINCT person_id) AS PATIENTS,
1COUNT(DISTINCT person_id) AS 'PAT_WT',
value_textCOUNT(DISTINCT person_id) AS 'WEIGHT'
FROM obs
WHERE voided = 0
AND concept_id = 163343
GROUP BY LAST_DAY(obs_datetime),
date_format(obs_datetime, '%Y%m'),
date_format(obs_datetime, '%Y') ,
date_format(obs_datetime, '%m') ,
value_text
order by 1,2,3,4

12

SQLlab Query

ConsultationTimeByHW

Query -
SELECT
Concat(date_format(encounter.date_created,'%Y'),"/",date_format(encounter.date_created,'%m')) AS 'MonYear',
date_format(encounter.date_created,'%Y') AS 'Year',
date_format(encounter.date_created,'%m') AS 'Month',
CASE
WHEN (MINUTE(TIMEDIFF(encounter.date_created,visit.date_started)) <=10 AND MINUTE(TIMEDIFF(encounter.date_created,visit.date_started)) >= 0) THEN "0-10"
WHEN (MINUTE(TIMEDIFF(encounter.date_created,visit.date_started))>=11 AND MINUTE(TIMEDIFF(encounter.date_created,visit.date_started))<=20) THEN "11-20"
WHEN (MINUTE(TIMEDIFF(encounter.date_created,visit.date_started))>=21 AND MINUTE(TIMEDIFF(encounter.date_created,visit.date_started))<=30) THEN "21-30"
ELSE "30+"
END AS 'Time_Group'
FROM visit JOIN encounter
ON(visit.patient_id = encounter.patient_id and visit.visit_id = encounter.visit_id)
WHERE encounter.encounter_type=1
AND (visit.voided=0 and encounter.voided=0)
order by 2,3,4

13

SQLlab Query

PercentagePatientsSeenByDoctor

Query -
SELECT
if((sum(case Diagnosis
when 'TotalVisit' then Patients
else 0
end) -
sum(case Diagnosis
when 'Diagnosis Provided' then Patients
else 0
end))<0,0, (sum(case Diagnosis
when 'TotalVisit' then Patients
else 0
end) -
sum(case Diagnosis
when 'Diagnosis Provided' then Patients
else 0
end)) )as Patients,
'Diagnosis Not Provided' as 'Diagnosis',
LastDay,
MonYear,
Year,
Month
FROM(
SELECT count(distinct(patient_id)) as 'Patients',
'TotalVisit' as Diagnosis,
last_day(date_created) AS 'LastDay',
date_format(date_created,'%Y%m') AS 'MonYear',
date_format(date_created,'%Y') AS 'Year',
date_format(date_created,'%m') AS 'Month'
FROM visit
WHERE voided=0
group by last_day(date_created),
date_format(date_created,'%Y%m') ,
date_format(date_created,'%Y') ,
date_format(date_created,'%m')
UNION
SELECT count(distinct(obs.person_id)) as 'Patients',
'Diagnosis Provided' as Diagnosis,
last_day(date_created) AS 'LastDay',
date_format(obs.date_created,'%Y%m') AS 'MonYear',
date_format(obs.date_created,'%Y') AS 'Year',
date_format(obs.date_created,'%m') AS 'Month'
FROM obs
WHERE obs.voided=0
and concept_id = '163219'
group by last_day(date_created),
date_format(obs.date_created,'%Y%m') ,
date_format(obs.date_created,'%Y') ,
date_format(obs.date_created,'%m')
) as tbl
GROUP BY LastDay,
MonYear,
Year,
Month
UNION
SELECT count(distinct(obs.person_id)) as 'Patients',
'Diagnosis Provided' as Diagnosis,
last_day(date_created) AS 'LastDay',
date_format(obs.date_created,'%Y%m') AS 'MonYear',
date_format(obs.date_created,'%Y') AS 'Year',
date_format(obs.date_created,'%m') AS 'Month'
FROM obs
WHERE obs.voided=0
and concept_id = '163219'
group by last_day(date_created),
date_format(obs.date_created,'%Y%m') ,
date_format(obs.date_created,'%Y') ,
date_format(obs.date_created,'%m')
ORDER BY 3,4,5,1

14

SQLlab Query

DoctorAvgTAT

Query -
SELECT LastDay,
MonYear,
Year,
Month,
count(patient_id) as 'Total',
SUM(DiffTime) as 'TotalSec',
floor((floor(SUM(DiffTime)/count(patient_id)) mod 3600)/60)/100 as 'TotalMin',
floor(SUM(DiffTime)/count(patient_id)/3600) AS 'HRS',
((SUM(DiffTime)/count(patient_id)) mod 3600) AS 'MIN',
SEC_TO_TIME(ROUND(SUM(DiffTime)/count(patient_id))) as 'TAT'
FROM(
SELECT
last_day(date_created) as 'LastDay',
date_format(date_created,'%Y%m') as 'MonYear',
date_format(date_created,'%Y') as 'Year',
date_format(date_created,'%m') as 'Month',
patient_id,
min(date_created) as StartTime,
max(date_created) as EndTime,
timediff(max(date_created),min(date_created)) as Diff,
(hour(timediff(max(date_created),min(date_created)))*3600) +
(minute(timediff(max(date_created),min(date_created)))*60) +
(second(timediff(max(date_created),min(date_created))) )as DiffTime
FROM encounter
WHERE voided=0
AND encounter_type in (1, 9)
GROUP BY LAST_DAY(date_created),
date_format(date_created,'%Y%m'),
date_format(date_created,'%Y'),
date_format(date_created,'%m'),
patient_id
HAVING COUNT(patient_id) > 1) AS tbl
GROUP BY LastDay,
MonYear,
Year,
Month

15

SQLlab Query

PatientPersonal

Query -
select (CASE b.Gender
When 'M' then 'Male'
When 'F' then 'Female'
end) as Gender,
b.birthdate,
(b.person_id),
(a.patient_id),
a.date_created,
last_day(a.date_created) as LastDay,
date_format(a.date_created,'%Y') as Year,
date_format(a.date_created,'%m') as Month,
YEAR(curdate())-YEAR(b.birthdate) as AGE,
CASE
WHEN YEAR(curdate())-YEAR(b.birthdate) <=1 THEN "0-1"
WHEN (YEAR(curdate())-YEAR(b.birthdate)>=2 AND YEAR(curdate())-YEAR(b.birthdate)<=11) THEN "02-11"
WHEN (YEAR(curdate())-YEAR(b.birthdate)>=12 AND YEAR(curdate())-YEAR(b.birthdate)<=17) THEN "12-17"
WHEN (YEAR(curdate())-YEAR(b.birthdate)>=18 AND YEAR(curdate())-YEAR(b.birthdate)<=29) THEN "18-29"
WHEN (YEAR(curdate())-YEAR(b.birthdate)>=30 AND YEAR(curdate())-YEAR(b.birthdate)<=44) THEN "30-44"
WHEN (YEAR(curdate())-YEAR(b.birthdate)>=45 AND YEAR(curdate())-YEAR(b.birthdate)<=59) THEN "45-59"
ELSE "60+"
END AS 'AGEGRP',
c.city_village,
d.name,
e.value,
e.person_attribute_type_id
from person b,
patient_identifier a,
person_address c,
location d,
person_attribute e
where e.person_id = a.patient_id
and e.person_attribute_type_id in (11,12,13)
and d.location_id = a.location_id
and c.person_id = a.patient_id
and c.voided = 0
and c.preferred = 1
and b.person_id = a.patient_id
and b.voided = 0
and a.voided = 0

16

SQLlab Query

TATPerDoctor

Query -
SELECT LastDay,
MonYear,
Year,
Month,
DoctorId,
username,
Concat("Dr.",family_name) as 'Doctor',
count(patient_id) as 'Total',
SUM(DiffTime) as 'TotalSec',
floor((floor(SUM(DiffTime)/count(patient_id)) mod 3600)/60)/100 as 'TotalMin',
floor(SUM(DiffTime)/count(patient_id)/3600) AS 'HRS',
((SUM(DiffTime)/count(patient_id)) mod 3600) AS 'MIN',
SEC_TO_TIME(ROUND(SUM(DiffTime)/count(patient_id))) as 'TAT'
FROM(
SELECT
last_day(date_created) as 'LastDay',
date_format(date_created,'%Y%m') as 'MonYear',
date_format(date_created,'%Y') as 'Year',
date_format(date_created,'%m') as 'Month',
patient_id,
max(case encounter_type
when 9 then creator
else null end) as "DoctorId",
min(date_created) as StartTime,
max(date_created) as EndTime,
timediff(max(date_created),min(date_created)) as Diff,
(hour(timediff(max(date_created),min(date_created)))*3600) +
(minute(timediff(max(date_created),min(date_created)))*60) +
(second(timediff(max(date_created),min(date_created))) )as DiffTime
FROM encounter
WHERE voided=0
AND encounter_type in (1, 9)
GROUP BY LAST_DAY(date_created),
date_format(date_created,'%Y%m'),
date_format(date_created,'%Y'),
date_format(date_created,'%m'),
patient_id
HAVING COUNT(patient_id) > 1) AS tbl
inner join users on DoctorId = users.user_id
inner join person_name on users.person_id = person_name.person_id
GROUP BY LastDay,
MonYear,
Year,
Month,
DoctorId,
username,
Concat("Dr.",family_name)
order by 3,4;

17

SQLlab Query

DoctorPatientInteraction

SELECT
count(distinct t1.visit_id) as tot,
date_format(t1.date_created,"%Y") as Year,
date_format(t1.date_created,"%m") as Month,
concat(date_format(t1.date_created,"%Y") ,"/", date_format(t1.date_created,"%m")) as MonYear,
last_day(t1.date_created) as "LastDay",
'Not Provided' as Value
FROM
visit t1
LEFT JOIN
visit_attribute t2 USING (visit_id)
WHERE t2.visit_id IS NULL
and t1.voided = 0
group by date_format(t1.date_created,"%Y"),
date_format(t1.date_created,"%m"),
concat(date_format(t1.date_created,"%Y") ,"/", date_format(t1.date_created,"%m")),
last_day(t1.date_created)
union
select count(distinct visit_id) as tot,
date_format(date_created,"%Y") as Year,
date_format(date_created,"%m") as Month,
concat(date_format(date_created,"%Y") ,"/", date_format(date_created,"%m")) as MonYear,
last_day(date_created) as "LastDay",
value_reference
from visit_attribute
group by date_format(date_created,"%Y"),
date_format(date_created,"%m"),
concat(date_format(date_created,"%Y") ,"/", date_format(date_created,"%m")),
last_day(date_created) ,value_reference
order by 2,3

List of Tables added to VSO Database

Sr. No.

Object Type

Object Name

Added Columns

Comments

1

DB View

VSO_PatientVisit

EHRComplete

2

DB View

VSO_EncObs

BHWFormType

MidwifeFormType

3

SQLlab Query

VSO_Visitswithoutforms

Query -

select g.visit_id, sum(case g.concept_id
when 163213 then 1 else 0 end) as 'PhyEx',
sum(case g.concept_id when 163212 then 1
else 0 end) as 'CurrCom',
sum(case g.concept_id
when 163211 then 1
else 0 end) as 'FamilyHis',
sum(case g.concept_id
when 163210 then 1
else 0 end) as 'MedHis'
from VSO_EncObs g
where visit_voided = 0
and encounter_voided = 0
and obs_voided = 0
group by visit_id

4

SQLlab Query

VSO_Patient_Visit_Experience

Query -

select concat(Year,"/",Month) as YearMon,
last_day(date_started) as Date_Started,
0 as Value_Text,
0 as 'Val_WT',
0 as 'PAT_WT',
0 as 'Weight',
(count(distinct visit_id) -
count(distinct(case concept_id
when 163343 then visit_id
else null
end))) as Visits
from VSO_EncObs
where visit_voided = 0
and encounter_voided = 0
and obs_voided = 0
and location <> 'Remote'
group by Year,
Month,
last_day(date_started)
union
select concat(Year,"/",Month) as YearMon,
last_day(date_started) as Date_Started,
value_text,
1 as Value_WT,
1count(distinct(visit_id)) as 'PAT_WT',
value_textcount(distinct(visit_id)) as 'Weight',
count(distinct(visit_id)) as Visits
from VSO_EncObs
where visit_voided = 0
and encounter_voided = 0
and obs_voided = 0
and location <> 'Remote'
and concept_id = 163343
group by Year,
Month,
last_day(date_started),
value_text
order by 1,2,3,4;

Connecting to Google Sheets as Database

Superset has a feature to connect to Google Sheets as a database. There are certain setups that are necessary to be done in order to make this feature available.

Step1:

  1. To implement Google authentication, create a service account as follows:

    1. Login to Google Console Admin using your intelehealth email id (https://console.cloud.google.com/iam-admin/serviceaccounts)

    3. Ensure you select “Enable G-suite domain wide delegation” checkbox

    4. Download the key created by google for your service account as json file

  2. Manage API client access at https://admin.google.com/${DOMAIN}/AdminHome?chromeless=1#OGX:ManageOauthClients

    1. Add the "Unique ID" from step 1 as the "Client Name"

    2. Add the scope as “https://www.googleapis.com/auth/spreadsheets.readonly”

  3. Connect to superset server 13.233.116.180 (user: ubuntu)

  4. Run following commands in the sequence mentioned below:

    1. cd superset/my_viz

    2. . bin/activate

    3. pip install gsheetsdb

    4. pip install gsheetsdb[cli]

    5. pip install gsheetsdb[sqlalchemy]

  5. Go to path /home/ubuntu/superset/my_viz/lib/python3.6/site-packages/gsheetsdb

    1. Access file auth.py under this folder

    2. Add the following lines at the start of function “get_credentials_from_auth”:

      1. if not subject:
        subject='priya@intelehealth.io'

      2. if not service_account_file:
        service_account_file='/home/ubuntu/superset/my_viz/intelehealthsuperset-4001d197be72.json/intelehealthsuperset-4001d197be72.json'

  6. stop the superset python process running on the server & restart the same

  7. Connect to Superset application in browser as admin using https://dashboards.intelehealth.io

  8. Go to menu Sources → Databases

  9. Add a new database with below details:

    1. Database Name: Google_Sheets

    2. SQLAlchemy URI : gsheets://

    3. Click the button “Test Connections”

    4. Once the connection is established successfully, save the new database by hitting the save button

  10. Now you can create a table based on google sheets as follows:

    1. Access the menu Sources → Tables

    2. Create a new table where database is “Google_Sheets” and Table Name is the shareable link of the concerned google sheet.

  • No labels