JSON to Insights: Analysing the Tabulated Data with Power BI
By Jeff Carr
December 9, 2019

Analysing the tabulated data with Power BI

Now that the data has been tabulated, we can analyse it using software such as Microsoft Power BI.

PowerBI can either connect directly to Precog, or to a database or warehouse such as Snowflake or Postgres where Precog has pushed the data.

To connect Power BI directly to Precog, go to the results page for the ADVERSE_DRUG_REACTIONS table in Precog, then create and copy a reusable access link.

Creating and copying a reusable access link in Precog

Next open Power BI, click “Get Data” then click “Web”.

Selecting Web from the Get Data menu in Power BI

Then paste the access link and click “OK”.

Pasting a Precog access link into Power BI

Precog pages and tabulates the data from the OpenFDA API and provides the latest results to Power BI.

Click “Transform Data”.

A preview of data paged and tabulated by Precog shown in Power BI

In the “Query Settings” section, set “Name” to “DRUG_REACTIONS”. Remove “Changed Type” from the “APPLIED STEPS”. Then click “Close & Apply”.

Removing the changed type applied step in the Power BI Query Editor

The data is now available in PowerBI for analysis and visualisation.

The FDA states that

When a report lists multiple drugs and multiple reactions, there is no way to conclude from the data therein that a given drug is responsible for a given reaction.

As such let’s add a column which denotes whether each reaction is conclusive or not.

Click “Modeling”, click “Add Table”, then paste the following

STANDARD_REPORTS = ADDCOLUMNS(SUMMARIZE(DRUG_REACTIONS, DRUG_REACTIONS[REPORT_ID],"DRUGS",DISTINCTCOUNT(DRUG_REACTIONS[DRUG]),"REACTIONS",DISTINCTCOUNT(DRUG_REACTIONS[REACTION])),"STANDARD",[DRUGS]=1||[REACTIONS]=1)

Adding a calculated table in Power BI

Then click “Manage relationships”, click “New”, select “DRUG_REACTIONS”, set “Cross filter direction” to “Both”, click “OK” then click “Close”.

Creating a relationship in Power BI

Next insert a “Donut chart”. Drag CONCLUSIVE from CONCLUSIVE_REPORTS to “Legend” and drag REPORT_ID from CONCLUSIVE_REPORTS to “Values”.

A donut chart in Power BI showing 10.46K conclusive reports and 12.76K inconclusive reports

As shown here, the majority of reports are not conclusive. If reporting was changed such that reactions were listed per drug, then all new reports would be. Of course such a change is quite a large endeavour.

The FDA provides access to the following chart, but the usefulness of this is limited. It includes non-conclusive reports, drugs which weren’t indicated for osteoarthritis and drugs which weren’t suspected to have caused the reported adverse reactions.

OpenFDA Chart of drug classes in adverse event reports that contain drugs indicated for osteoarthritis. There are record counts for ten different drug classes. The top three are 433 records for ACEIs, 242 reports for NSAIDs, 151 reports for insulin analogs.

We can produce our own chart without these limitations.

Insert a “Stacked bar chart”, drag DRUG_CLASS to “Axis” and drag REPORT_ID from CONCLUSIVE_REPORTS to values.

A bar chart of frequency of drug classes in Power BI

Currently this bar chart is the same as the one provided by the FDA with one key difference. Our chart shows that the vast majority of records don’t contain drug class information. As such we’ll need to drill down into the individual drugs themselves. Before we do this, we will remove the limitations of the FDA chart.

Drag CONCLUSIVE, DRUG_INDICATION and CHARACTERIZATION to “Filters on this vidual”. Select conclusive as “True”, drug indication as “Osteoarthritis” and characterization as “1” (this means the drug was suspected as the cause of the reaction).

Filtered bar chart in Power BI

Only two drug classes are listed. There are 9136 reports containing drugs with no drug classification information. Out of the drugs whose pharmaceutical classes are included, 68 are NSAIDs and 1 is a Platelet Aggregation Inhibitor.

Next let’s drill into the drugs themselves. Copy and paste the bar chart and replace DRUG_CLASS with DRUG. This shows some drug names containing full stops which we should remove.

Right click on DRUG_REACTIONS and click “New column”, paste the following then replace DRUG with SANITIZED_DRUG in our newest bar chart.

SANITIZED_DRUG = SUBSTITUTE([DRUG],".","")

Bar chart showing the drugs which are most commonly reported for adverse reactions in Power BI

The position of drugs in these tables does not mean they are more or less dangerous than each other, as there are many factors that affect reporting of adverse events. Please refer to the important disclaimer.

This table shows that as expected the majority of reported adverse reactions involving drugs indicated for osteoarthritis are NSAIDs. NSAIDs are often prescribed for osteoarthritis. Also included in this list are a TNF inhibitor, a monoclonal antibody and a gabapentinoid.

Other drugs which are often prescribed for osteoarthritis include acetaminophen and opioid analgesics, however these don’t feature prominently in our chart. This may be the result of reporting practices.

Vioxx is an NSAID which was approved by the FDA in 1999 and voluntarily removed from the US market in 2004 due to concerns about risks of heart attack and stroke. Despite only being on the market for a short period, it is prominently featured on this list. Bextra (also known as Meloxicam) is an NSAID which was approved by the FDA in 2001 and removed from the US market by the FDA in 2005. The other drugs in this list are approved at the time of writing.

Celebrex (celecoxib), Voltaren and Ibuprofen are NSAIDs. Celebrex is reported as the suspected cause of adverse reaction more than twice as often than Ibuprofen. Some organisations, such as Best Practice Advocacy Centre New Zealand state that “A significant benefit of celecoxib is that it is associated with less risk of gastrointestinal bleeding compared to non-selective NSAIDs”. Let us see if the BPACNZ statement is reflected in this dataset.

Copy and paste the last bar chart and replace SANITIZED_DRUG with REACTION. Then drag SANITIZED_DRUG to “Filters on this visual”. Search for “Celebrex” and select all drugs that only contain Celebrex. Search for “Celecoxib” and select all drugs that only contain Celecoxib. Avoid values marked as “Blinded” and values that contain drugs other than Celebrex and Celecoxib.

Copy and paste the Celebrex reactions bar chart, click “Select all” then select all drugs that only contain Ibuprofen. Avoid values marked as “Blinded” and values that contain drugs other than Ibuprofen.

Then for each of the two newest bar charts select them and click “Table”.

Tables in Power BI showing the frequency of reported reactions to Celebrex and Ibuprofen

Gastrointestinal bleeding is the 15th most reported adverse event where Celebrex is suspected and the 1st most reported adverse event where Ibuprofen is suspected. This seems to reflect BPACNZ’s statement, however, there are many possible causes for these results. These results should not be used to make decisions about medical care.

Let’s summarize our insights into adverse reaction reporting of drugs indicated for osteoarthritis.

  • Out of 23217 reports, only 10457 could be used to conclude that a given drug is suspected to be responsible for a given reaction.
  • Out of the 10457 resulting reports only 246 contained drug classification information.
  • There are more than twice as many adverse reaction reports where Celebrex is suspected as responsible than there are where Ibuprofen is suspected as responsible.
  • Gastrointestinal bleeding is the 15th most reported adverse event where Celebrex is suspected and the 1st most reported adverse event where Ibuprofen is suspected.
  • Despite being on the market for a short time Vioxx and Bextra are listed in the top 10 most reported drugs.

Research into the causes of these insights could lead to preferable patient outcomes and more valuable adverse event reporting.

In just a few minutes we’ve connected, tabulated and gained insights from paginatated non-tabular Web API data with Precog and Power BI.

Previous page: Tabulating non-tabular data without Precog

Next page: Joining datasets with Precog

NEWS & BLOG

Precog Partners with SME

Precog Partners with SME

Precog, the company behind the popular Precog solution for transforming and loading complex data for analytics and data science, has partnered with SME Solutions Group, Inc. based in Tampa, Florida.

read more

Time to Stop
the Delusions

Everywhere you go in the
analytics world today, you’ll hear
the same term repeatedly:
“self-service.” It’s the new hotness.

Self-Service Analytics: