Using Power BI to tell stories about Oil

This blog post shows the potential of Power BI in creating insights when visualizing data dynamically. It is not only storytelling with data using static pre-made graphs, but something a lot more complex — interactive storytelling, which adds another dimension to a static image. As the user clicks and filters the graphical visuals change. Hence, one must account for the possible interactions, and explore all the main stories beforehand. This places more demand on the creator, but, if done right, multiplies the value, to tell not just a story, but a book of stories all on one screen.

Sources and why oil stats?

The dataset comes from the Statistical Review of World Energy produced by BP. It has a large number of fact columns and one dimension table. Fact columns are production and consumption statistics that in some cases reserves numbers of all key natural resources by year (since 1965), by country. The number of dimension tables is limited to one: countries and their characteristics.

In this example, I will show how it is possible to tell a story focusing on just one aspect of this dataset — oil stats. Three reasons for choosing oil first. First, most people are aware of oil market dynamics and know about OPEC, Saudi Arabia, Russia. Second, it is a sector of the economy that I spend a few years studying and working in, first during my Masters in Energy in Norway, then as Oil and Gas investment banking analyst in London. Hence, these numbers are quite familiar to me. Third, as a way to show my gratitude to this data source. Back in the day as an O&G sector analyst, I always felt grateful to BP for this report as it allowed us to get the quality data for industry overview slides, and maybe sometimes go home before 2 am.

‍

Main Graph

First — the main graphic of this report is the big Area chart (at the top center) with 3 lines in it. Two lines — consumption (light blue) and production (darker blue), are drawn on one axis. The third line — called Proficit (yellow area), which is the surplus of production vs consumption, is drawn on another axis.

Consumption and production are growing equally and almost without stops since the mid-1980s.
Proficit is starting to develop since mid-2010, after decades of equal consumption and demand. In this period, US production more than doubled, from 7 mln bbl in 2009 to 15 mln bbl in 2019.
Before that, from 1965–1984, there was a great proficit — close to 10% of consumption.
During this proficit period prices were quite low, but not until global consumption increased to 80 mln barrels per day when low proficit started to drive the price up.

There is also a tooltip, which allows the user by hovering over a point in the Main Graph to see additional information — which countries produced and consumed the most in that year. This tooltip, if hovered over 1984 and 1985 — shows that USSR is used in 1984, but replaced by Russia in 1985. If you keep hovering over the line as you through the years, you will see the drop in production coming from Russia that starts from 1994 to 2000. From 11 mln bbl per day to 6 mln bbl. A period is known as the “nineties” in Russia. Something that I can relate to — growing up in Russian region at the time.

This blog post shows the potential of Power BI in creating insights when visualizing data dynamically. It is not only storytelling with data using static pre-made graphs, but something a lot more complex...

What additional layers of filters can show us?

Using slicer by region on the left side we can see data filtered specifically for the regions chosen.

Thus, three main groups emerge.

Net consumers (consuming much more than producing) — Asia Pacific, Europe
Net producers (producing much more than consuming) — Middle East, CIS, Africa
Parity consumers (relative equal production and consumption) — North America, South and Central America

Deficit is increasing in the Asia Pacific

‍

**Europe’s** deficit is growing as Norway’s and UK’s fields are off their peaks (you can hover over the Main graph to see this better through the pop-up tooltip)

‍

**Middle East** consumes only a third of what it produces, accounting for one-third of the total global supply

‍

Total **CIS** has rebounded from the fall of the Soviet Union in 2007 reaching parity with the USSR’s maximum of 12 mln barrels per day, but consumption has decreased and never returned to the same levels, only half of USSR’s max of 8 mln barrels

‍

**Africa** has great potential to increase the supply further, given its great reserves. Libya and Nigeria are two oil powerhouses

‍

North America is a story of technical achievem

**South and Central America** — a sleeping giant

‍

South and Central America: Venezuela’s Orinoco Belt— extra-heavy oil — largest petroleum reserve in the world. It was reported to cost around 18 dollars per barrel to produce. Brazil is another interesting story — offshore oil (Campos and Santos basins hold roughly 90% of Brazill’s oil). Once the infrastructure is in place, breakeven prices could be as low as 21 dollars per barrel. More so, the oil that is produced is light with low sulfur, usually destined for Asian refineries.

‍

Using pop-up tooltips while filtering by region

‍

‍

Hovering over the Asia Pacific Main graph, one can see how Japan was overtaken first by China (in 2003) and then by India (in 2015) in oil consumption. Moreover, this process was bi-directional — Japan was lowering its consumption yearly as well.

In Europe, one can observe how Norway overtook UK’s leading place as top oil producers in 1991, while in the 80s the party was in the UK. How these two European countries spend their windfall oil profits is an interesting study in Oil and Gas History. In short, it seems like Norway’s citizen’s got the better long-term deal.

Pressing on the countries in the main table

Interesting graphs are those of US (hockey stick from 2010 in oil production), Russia (rebound from the 90s), India and China (ever-growing consumption), Venezuela (deep recent fall), UK ( very similar to production schedule with 2 peaks), Norway (classic production schedule of a field).

‍

The main table

Above the table, one can choose which year the stats should be for. We kept the most interesting years. I would advise sorting each column and then going through the ages. One can see for example, how the top list for net oil consumers changes. Starting in 1965 from the US, Germany, Japan, UK and finishing with China, India, Japan, and South Korea in 2019.

Possible questions we can answer using the main table? By clicking on each column header, you can sort and answer the following questions:

Who were the top producers in 2019? US, Saudi Arabia, Russian Federation (one needs to sort the column by pressing on the column header)

Who were the top consumers in 2019? US, China, India

Who has the greatest number of reserves? Venezuela (heavy oil, hard to produce, especially with sanctions, environmentally unfriendly), Saudi Arabia (cheapest oil possible to produce — at 5–10$ per barrel), Canada (also a heavy oil reserve — detrimental to the ecology)

Who has the greatest proficit? Russian Federation, Saudi Arabia, Iraq

Who has the greatest deficit? China, India, Japan

Keep in mind that this post explores the insights, eg. interesting trends and observations, that our analytical panel provides, but in a separate one, we will explain why and how we made it, touching upon many different disciplines and concepts, from data modeling (dimensions vs fact tables) to DAX and then to visualization methods (principles of pre-attentive attributes, gestalt principles. etc.) plus practical hints (how to set-up tooltips, edit interactions, etc.)

There is much greater analytical detail that can be added still. For instance, there is no analysis of how reserves stack up to production and vice versa. If I remember correctly the reserves when measured in years of production, in all years have 35–40 years ahead of them. But this analysis will be added in time. Also, the gas side is completely untouched here.

The dataset also includes stats on the following resources: