Statistics with Python part 2 (AURA)
Hello! After some time (almost one year man 🙄) I’m back!
Last post I presented a notebook with simple examples in a jupyter notebook to perform some statistical hypothesis testing!
In Part 3, ESAPI will be used to extract DVH information from a list of patients plans.
In this post (Part 2), I’m going to show how to use AURA Reporting to extract the patient list.
It can be done using ESAPI, ARIA database queries or ARIA Web Services, but I won’t in the moment, I preferred AURA Reporting.
Lets Go!
Open the Reporting Tab and press Create new Report, this command will open the Microsoft SQL Report Builder:
Press the Blank Report option:
In the end of this post we’ll create a table to query the treatments for Prostate SABR (36.25 Gy in 5 fx) after 2018.
First of all let’s add a Data Source (varian dw).
Rename the data source to VarianDW and click in the Browse button:
Open the VarianTemplate folder:
Then the Data Sources folder:
Double Click the variandw source:
Once the connection is set, click OK:
We’ll use the data source to create a custom data set.
Of course, you can use the pre-built models from AURA.
But I want to show the power of the Query designer.
Press add DataSet:
In the dataset properties tab, Select use dataset embedded in my report, choose Variandw as data source.
Press Query Designer, since I’m in CITRIX, I’m using my user to sign in:
Then the query designer will pop. Query designer makes us able to do queries with SQL with limited knowledge (unfortunately I’m not a database nerd 😅).
See those tabs in the left? The DWH contains tables we can query, they represent the data in the AURA tables.
Let’s check the fields we can query from the FactTreatmentHistory:
We’ll select just the following and press the Run Query Button:
This is the result from the query :
I get a series of all treatments. Of course it is the treatment history 😎!
Now let’s start filtering some information and get the Plan Id, Patient Id, Course Id and Machine Id (merging the Treatment History’s table with others).
First we’ll get only the last fraction of the Prostate SABR patients.
So the filter will be NoOfFraction = 5 ; FractionNumber = 5, and dose per fraction = 7.25Gy.
This is the result from the query:
Let’s merge the Course and Plan’s Table to see their actual ID.
With the fact treatment history fields still selected, select:
This is the image you’ll see, look how the tables are automatically merged by the query designer, using the DimCourseId field:
So when I run the query once more, I’ll get the Course Id:
Do the same with Plan Id with the DimPlan’s table:
Query Designer already filled the relationship for us:
There we go!
Do the same with the DimPatient’s table yourself.
So now I can filter based on another table as well, imagine a head and neck cohort, how would you look for the unilateral cases?
Back to track:
I want to get the treatments for a specific machine:
From the same principle, using the DimMachine’s Table:
Adding the Machine Id as a filter:
Query Once More!
Now we have a very good DataSet to work with!
Add the filter fields as parameters so we can change when running the report, and press OK.
There it is: good and old SQL Query written for us by the query designer:
Finally we’ll build our table and export it to a CSV file:
Patient Id, Course Id and PlanSetupId suffice for the next part!
Run the Report!
Export as CSV and you’ll have your patient list ready:
In the next Part, we’ll use this CSV file to run an executable at the Eclipse DB to export DVH for selected structures!
See you soon!
Joao Castelo
Thanks for reviewing Jonas (Congratz he’s now a certified medical physicist) 🤗
