This interactive Neo4j graph tutorial shows how to use Neo4j to analyse the Offshore Leaks : the case of Azerbaijan.
Table of Contents
-
Introduction
-
Data Model
-
Database Setup
-
Cypher Queries
The Offshore Leaks released in 2013 by the ICIJ is a rarity. It is a big dataset of real information about some of the most secret places on earth : the offshore financial centers. The investigation of the ICIJ brought to the surface many interesting stories including the suspicious activities of the President of Azerbaijan. We are going to see how graph technologies can help us make sense of the complex data in the Offshore Leaks.
We want to know how the President of Azerbaijan is connected to offshore accounts. This means that we will need to focus on the network he uses to control his assets stored in offshore entities. These networks includes family members and a complex set of intermediaries or partners. We want to see how things are connected so we are going to have to represent each of these entities as distinct nodes in a graph.
Our graph will include :
people : they are the ones building and using the network. Some of them are quite visible…some are working behind the scene ; companies : offshore entities, banking services providers, businesses ; addresses : they are interesting to track, especially as they have legal implications (eg a company registered in a financial heaven pays no tax) ;
People and companies have addresses they are connected to. People can be linked together directly by family ties. Links between companies are more complex. A company can be designated as the "master client", as the "offshore provider" or as the "records and registers" of an other company. All off these relationships refer to diverse roles as intermediary or go-between. They are usually played by very discrete companies. They are selling their know-how and contacts to the people who want to take advantage of the offshore system.
Here is a concrete example of the network behind the ICIJ dataset :
John and Sam are married. They have stored assets in the company Treasure ltd. They control the company as they are both shareholders and John is a Director. To set it up, they have used an address in Dubai. The company, Treasure ltd, is established in the Bahamas. That means that the assets it controls are private and tax-free. Treasure ltd has been set up with the help of two companies : Good Advice Inc and Hide and Seek. Oleg, a business partner of John, is also a Director of Treasure ltd.
This schema is one of the many ways we can model the ICIJ dataset. I have added here the concept of family ties. That kind of data is not present in the Offshore leaks but will be key for our subject.
// Create people
CREATE (Ilham_Aliyev:Person {first_name:'Ilham', last_name:'Aliyev', job:'President', Employer:'Azerbaijan', picture: 'http://bigbrowser.blog.lemonde.fr/files/2013/04/000_Par7450762-530x343.jpg'})
CREATE (Hassan_Gozal:Person {first_name:'Hassan', last_name:'Gozal', job:'Vice President', Employer:'Intersun Holding', picture: ''})
CREATE (Abdolbari_Gozal:Person {first_name:'Abdolbari', last_name:'Gozal', job:'President', Employer:'Intersun Holding', picture: 'http://www.azersun.az/news/news146.jpg'})
CREATE (Mehriban_Aliyeva:Person {first_name:'Mehriban', last_name:'Aliyeva', job:'Director', Employer:'Heydar Aliyev Foundation ', picture: 'http://www.mehriban-aliyeva.org//u/a/4/o.jpg'})
CREATE (Arzu_Aliyeva:Person {first_name:'Arzu', last_name:'Aliyeva', job:'', Employer:'', picture: 'http://legend.az/uploads/posts/2011-09/1315158828_arzi-aliyeva-008.jpg'})
CREATE (Leyla_Aliyeva:Person {first_name:'Leyla', last_name:'Aliyeva', job:'', Employer:'', picture: 'http://www2.pictures.zimbio.com/gi/Leyla+Aliyeva+Fly+Bakou+Exhibition+Launch+Nn74uXjDl_cl.jpg'})
CREATE (Heydar_Aliyev:Person {first_name:'Heydar', last_name:'Aliyev', job:'', Employer:'', picture: ''})
CREATE (Ridzuan_Salleh:Person {first_name:'Ridzuan', last_name:'Salleh', job:'', Employer:'', picture: ''})
// Create addresses
CREATE (Al_Fairooz_Dubai_Marina:Address {address:'13 Al Fairooz Dubai Marina', city:'Dubai', country:'Dubai'})
CREATE (Portcullis_TrustNet_Chambers_POBox:Address {address:'Portcullis TrustNet Chambers P.O. Box 3444 Road Town', city:'Tortola', country:'Tortola'})
CREATE (UnitPL01A_Plaza_Level:Address {address:'Unit PL 01-A, Plaza Level, No. 45 Block A Medan Setia 1, Plaza Damansara, Damansara Heights, 50490', city:'Kuala Lumpur', country:'Kuala Lumpur'})
CREATE (Apartment_No1801_Dubai:Address {address:'Apartment No. 1801 Dubai Marina Lerev Residential', city:'Dubai', country:'Dubai'})
CREATE (Apartment_No1802_Dubai:Address {address:'Apartment No. 1802 Dubai Marina Lerev Residential', city:'Dubai', country:'Dubai'})
CREATE (Villa22_Gate51_Avenue6:Address {address:'Villa 22 Gate 51 Avenue 6 Jannusan 504', city:'', country:''})
CREATE (Prospekt_Kutuzonskiy:Address {address:'444, 4/2 Prospekt Kutuzonskiy', city:'Moscow', country:'Moscow'})
CREATE (Honest_Bright_Company_Ltd:Address {address:'Honest & Bright Company Ltd. Office 53-54, Construction 3 3-rd Tverskaya-Yamskaya Street, Bld. 12', city:'Moscow', country:'Moscow'})
CREATE (PO_Box_117920:Address {address:'P.O. Box 117920 Jebel Ali Free Zone', city:'Dubai', country:'Dubai'})
CREATE (Shenton_Way_DBS:Address {address:'6 Shenton Way #14-01 DBS Building Tower One Singapore 068809', city:'Singapore', country:'Singapore'})
// Create companies
CREATE (Harvard_Management_Limited:Company {name:'Harvard Management Limited', form:'Standard International Company', incorporation:'07/11/2008', status:'Active', link: 'http://offshoreleaks.icij.org/nodes/166436'})
CREATE (LaBelleza_Holdings_Limited:Company {name:'LaBelleza Holdings Limited', form:'Business Company Limited by Shares', incorporation:'07/11/2008', status:'Active', link: 'http://offshoreleaks.icij.org/nodes/166434'})
CREATE (Arbor_Investments_Limited:Company {name:'Arbor Investments Limited', form:'Business Company Limited by Shares', incorporation:'07/11/2008', status:'Active', link: 'http://offshoreleaks.icij.org/nodes/166435'})
CREATE (Portcullis_Trustnet:Company {name:'Portcullis Trustnet', form:'OFFSHORE SERVICE PROVIDER', incorporation:'', status:'', link: 'http://offshoreleaks.icij.org/nodes/54662'})
CREATE (Naziq_and_Partners:Company {name:'Naziq & Partners', form:'', incorporation:'', status:'', link: 'http://offshoreleaks.icij.org/nodes/294050'})
CREATE (Crovelent_Holdings_LTD:Company {name:'Crovelent Holdings LTD.', form:'Business Company Limited by Shares', incorporation:'29/03/2007', status:'Active', link: 'http://offshoreleaks.icij.org/nodes/204584'})
CREATE (Rosamund_International_Ltd:Company {name:'Rosamund International Ltd', form:'Standard International Company', incorporation:'08/11/2002', status:'Inactive', link: 'http://offshoreleaks.icij.org/nodes/138523'})
CREATE (DBS_Trustee_Limited:Company {name:'DBS Trustee Limited', form:'', incorporation:'', status:'', link: 'http://offshoreleaks.icij.org/nodes/290319'})
// Create relationships
CREATE (Ilham_Aliyev)-[:Family]->(Mehriban_Aliyeva)
CREATE (Ilham_Aliyev)-[:Family]->(Arzu_Aliyeva)
CREATE (Ilham_Aliyev)-[:Family]->(Leyla_Aliyeva)
CREATE (Ilham_Aliyev)-[:Family]->(Heydar_Aliyev)
CREATE (Mehriban_Aliyeva)-[:Family]->(Arzu_Aliyeva)
CREATE (Mehriban_Aliyeva)-[:Family]->(Leyla_Aliyeva)
CREATE (Mehriban_Aliyeva)-[:Family]->(Heydar_Aliyev)
CREATE (Leyla_Aliyeva)-[:Family]->(Arzu_Aliyeva)
CREATE (Leyla_Aliyeva)-[:Family]->(Heydar_Aliyev)
CREATE (Arzu_Aliyeva)-[:Family]->(Heydar_Aliyev)
CREATE (Hassan_Gozal)-[:Family]->(Abdolbari_Gozal)
CREATE (Hassan_Gozal)-[:Uses_address]->(Al_Fairooz_Dubai_Marina)
CREATE (Harvard_Management_Limited)-[:Uses_address]->(Portcullis_TrustNet_Chambers_POBox)
CREATE (Naziq_and_Partners)-[:Uses_address]->(UnitPL01A_Plaza_Level)
CREATE (Arzu_Aliyeva)-[:Uses_address]->(Apartment_No1801_Dubai)
CREATE (Leyla_Aliyeva)-[:Uses_address]->(Apartment_No1802_Dubai)
CREATE (Ridzuan_Salleh)-[:Uses_address]->(Villa22_Gate51_Avenue6)
CREATE (Leyla_Aliyeva)-[:Uses_address]->(Prospekt_Kutuzonskiy)
CREATE (Mehriban_Aliyeva)-[:Uses_address]->(PO_Box_117920)
CREATE (Ilham_Aliyev)-[:Uses_address]->(PO_Box_117920)
CREATE (Rosamund_International_Ltd)-[:Uses_address]->(Portcullis_TrustNet_Chambers_POBox)
CREATE (DBS_Trustee_Limited)-[:Uses_address]->(Shenton_Way_DBS)
CREATE (LaBelleza_Holdings_Limited)-[:Uses_address]->(Portcullis_TrustNet_Chambers_POBox)
CREATE (Arbor_Investments_Limited)-[:Uses_address]->(Portcullis_TrustNet_Chambers_POBox)
CREATE (Crovelent_Holdings_LTD)-[:Uses_address]->(Honest_Bright_Company_Ltd)
CREATE (Portcullis_Trustnet)-[:Is_offshore_provider_of]->(Harvard_Management_Limited)
CREATE (Portcullis_Trustnet)-[:Is_offshore_provider_of]->(Naziq_and_Partners)
CREATE (Portcullis_Trustnet)-[:Is_offshore_provider_of]->(LaBelleza_Holdings_Limited)
CREATE (Portcullis_Trustnet)-[:Is_offshore_provider_of]->(Arbor_Investments_Limited)
CREATE (Portcullis_Trustnet)-[:Is_offshore_provider_of]->(Rosamund_International_Ltd)
CREATE (Naziq_and_Partners)-[:Is_linked_to {role:'Master Client', date:''}]->(Harvard_Management_Limited)
CREATE (Portcullis_Trustnet)-[:Is_linked_to {role:'Records And Registers', date:''}]->(Harvard_Management_Limited)
CREATE (Hassan_Gozal)-[:Is_linked_to {role:'Director', date:'39759'}]->(Harvard_Management_Limited)
CREATE (Ridzuan_Salleh)-[:Is_linked_to {role:'Director', date:'39759'}]->(Harvard_Management_Limited)
CREATE (Leyla_Aliyeva)-[:Is_linked_to {role:'Director', date:'39759'}]->(Harvard_Management_Limited)
CREATE (Leyla_Aliyeva)-[:Is_linked_to {role:'Shareholder', date:'39759'}]->(Harvard_Management_Limited)
CREATE (Naziq_and_Partners)-[:Is_linked_to {role:'Master Client', date:''}]->(LaBelleza_Holdings_Limited)
CREATE (Portcullis_Trustnet)-[:Is_linked_to {role:'Records And Registers', date:''}]->(LaBelleza_Holdings_Limited)
CREATE (Hassan_Gozal)-[:Is_linked_to {role:'Director', date:'39759'}]->(LaBelleza_Holdings_Limited)
CREATE (Ridzuan_Salleh)-[:Is_linked_to {role:'Director', date:'39759'}]->(LaBelleza_Holdings_Limited)
CREATE (Leyla_Aliyeva)-[:Is_linked_to {role:'Director', date:'39759'}]->(LaBelleza_Holdings_Limited)
CREATE (Leyla_Aliyeva)-[:Is_linked_to {role:'Shareholder', date:'39759'}]->(LaBelleza_Holdings_Limited)
CREATE (Naziq_and_Partners)-[:Is_linked_to {role:'Master Client', date:''}]->(Arbor_Investments_Limited)
CREATE (Portcullis_Trustnet)-[:Is_linked_to {role:'Records And Registers', date:''}]->(Arbor_Investments_Limited)
CREATE (Arzu_Aliyeva)-[:Is_linked_to {role:'Director', date:'39759'}]->(Arbor_Investments_Limited)
CREATE (Hassan_Gozal)-[:Is_linked_to {role:'Director', date:'39759'}]->(Arbor_Investments_Limited)
CREATE (Ridzuan_Salleh)-[:Is_linked_to {role:'Director', date:'39759'}]->(Arbor_Investments_Limited)
CREATE (Arzu_Aliyeva)-[:Is_linked_to {role:'Shareholder', date:'39759'}]->(Arbor_Investments_Limited)
CREATE (Leyla_Aliyeva)-[:Is_linked_to {role:'Shareholder', date:'39653'}]->(Crovelent_Holdings_LTD)
CREATE (Mehriban_Aliyeva)-[:Is_linked_to {role:'Director Of', date:'37635'}]->(Rosamund_International_Ltd)
CREATE (Mehriban_Aliyeva)-[:Is_linked_to {role:'Shareholder Of', date:'37635'}]->(Rosamund_International_Ltd)
CREATE (DBS_Trustee_Limited)-[:Is_linked_to {role:'Master Client', date:''}]->(Rosamund_International_Ltd)
CREATE (Portcullis_Trustnet)-[:Is_linked_to {role:'Records And Registers', date:''}]->(Rosamund_International_Ltd)
CREATE (Ilham_Aliyev)-[:Is_linked_to {role:'Director', date:'37635'}]->(Rosamund_International_Ltd)
CREATE (Ilham_Aliyev)-[:Is_linked_to {role:'Shareholder', date:'37635'}]->(Rosamund_International_Ltd)
RETURN *You can download the complete dataset here : https://www.dropbox.com/s/d9ceb427xzrcpza/Azerbaijan%20Offshore%20Leaks.zip
We look for the direct links between the President and offshore accounts
MATCH (president)-[r]->(offshoreaccount:Company)
WHERE president.first_name = 'Ilham'
RETURN offshoreaccount.name as company, offshoreaccount.form as form, offshoreaccount.incorporation as incorporation, offshoreaccount.status as status, r.date as date, r.role as rolePeople who are trying to hide money tend to use proxies they can hide behind. That means that we must enlarge our search and look for indirect connections.
With a Neo4j database for example, finding all the foreign assets Ilham Aliyev controls directly or indirectly is as simple as adding a “*” to our first query. The search will return all the paths in the data between Ilham Aliyev and offshore accounts.
MATCH (president)-[r*]->(offshoreaccount:Company)
WHERE president.first_name = 'Ilham'
RETURN offshoreaccount.name as company, offshoreaccount.form as form, offshoreaccount.incorporation as incorporation, offshoreaccount.status as statusIf we want to measure the role of the two middle-men, we can use a Cypher query again.
MATCH (president)-[r*]->(offshoreaccount:Company)
WHERE president.first_name = 'Ilham'
WITH offshoreaccount
MATCH (offshoreaccount)-[t]-(middlemen:Company)
RETURN middlemen.name as name, count(DISTINCT t) as mentions, type(t) as type, t.role as role
ORDER BY mentions DESC
Two ICIJ associated journalists, Khadija Ismayilova and an anonymous Azerbaijani reporter, wrote an amazing article detailing the Aliyev story. Their take on the data is clear. The offshore accounts have been setup to funnel money to President Aliyev. The funds are payments made by Hassan and Abdolbari Gozal, two businessmen who have been awarded more than $4.5 billion in construction contracts in Azerbaijan.
MATCH (Ilham:Person {first_name:'Ilham'}),(Abdolbari:Person {first_name:'Abdolbari'}), p = shortestPath((Ilham)-[*]-(Abdolbari))
RETURN pThe ICIJ journalists did a great job of investigating the Offshore leaks. Their work shows the suspicious dealings of the Aliyev family. Equipped with Neo4j and Linkurious, we have seen that we can quickly come to the same conclusions. Graph technologies offer an exciting way to bring to the surface information. If you have complex, highly connected data you struggle to understand, give it a try!
For more graph-related use cases, make sure to check the blog of Linkurious : http://linkurio.us/blog
Remember to run this with the actual graphgist renderer