For my first blog, or a blog-series, I decided to focus on the Israeli Knesset. For those of you who aren’t Israelis, The Knesset is “the unicameral national legislature of Israel.” (Wikipedia), or in other words — the Israeli parliament.
I really care about social activities and politics, so I thought it would be a good idea to put my data analysis skills into use, and hit two birds with one stone. On one hand I will get some practice, and on the other — I might gather some interesting insights and share it with other people who care about it.
Data Sources
In this early phase of the project, I only have one data source, but it’s a good one. I used Google and searched for “Knesset Data Analysis” (in Hebrew) and I encountered this page — “Accessible Parliamentarian Information”. You can use Google Translate, but in general, it says that starting March 2017, Israel’s Knesset wanted to implement concepts like Freedom of information and the public’s Right to know, and therefore it decided to expose some information (that was previously saved internally) to the general public.
The published information includes general information about the members of the Knesset, laws, bills, committees’ activities and more. For some reason, the data is shared using a protocol/format called ODATA.
What is ODATA
If you never heard of ODATA — That’s OK. I wasn’t aware of ODATA until I started doing this project either. Luckily, it’s pretty simple and easy to understand. ODATA is a RESTful API which exposes data-sets online and allows users to query data and get an XML as a response.
For example, in the Knesset’s ODATA, there are multiple tables exposed. One of them is a table called KNS_Person, which is pretty self-explanatory.
A simple query towards this data set would be something like this:
https://knesset.gov.il/Odata/ParliamentInfo.svc/KNS_Person()/$count
This query results in a simple answer, the count of Knesset members that are in the KNS_Person table — 1096.
However, if you take a more complex query, and search for all the law bills that are documented in this ODATA database, with this query:
Then, the result would look like hell, and contain thousands of XML lines, which are unreadable for the human eye.
Luckily, data visualization tools like Tableau or PowerBI have implemented their own ODATA connectors and they allow you to import the data and consume it in a very easy way.
Playing with Knesset Data
After I walked over the existing information, I understood that if we want to draw some juicy insights, we will need to enrich it with additional data sources, like the Knesset’s website or Wikipedia. More on this will be covered in the next blog post.
Before we add more data, we first need to explore the existing ODATA info. The authors of the data-set were kind enough and shared a documentation of the available tables and columns. I decided that the first blog post will be a simple partial analysis of the data, and include the process of importing the data into PowerBI, manipulating it for our needs, and finding some simple (yet interesting) insights.
*Note: according to the Knesset’s website — some information might be missing, as this data set was exported from old internal systems of the Knesset and even hand-written documents.
Importing ODATA into PowerBI
What I love about PowerBI and other similar tools is the possibility to use pre-built connectors. Quick search on Google and I found out that there’s an option to provide PowerBI a URL to the ODATA, and it will do the magic for you. I followed this guide and ended up with all the data in my hands.
This step will result in a new screen, named Navigator, which allows you to select which tables you want to import and edit before actually analyzing the data and building dashboards around it.
Exploring the Data
Since there are many tables, I realized I have to pick one direction to analyze, out of numerous possibilities. I tried to create different models and examined which pieces of information can be linked to draw insights. Finally, I decided to focus on the aspect of people, e.g. the party a person belongs to, his personal information and how all of it relates to initiating bills in the Knesset.
These are the necessary tables;
- KNS_Bill — all bills, including whether it was approved or not, why it wasn’t approved, who initiated the bill, and more. Our facts table.
- KNS_Status — a list of status codes to understand the current state of a bill. For example, a bill with a status code of 118, means that the bill has became an actual law.
- KNS_Person — list of all involved personnel in the Israeli parliament throughout history, including Knesset Members, Ministers, etc.
- KNS_BillInitiator — contains the information about a person that initiated a bill. Will enable me to link between a given bill and a person.
- KNS_Faction — factions (political parties) from all historic Knessets.
- KNS_PersonToPosition — a table which contains information about a given person in each Knesset. Will allow me to correlate between a person and the party he belonged to in a given Knesset.
- KNS_MkSiteCode — a table which links between the ID of a Knesset Member in the ODATA and its corresponding ID on the Knesset’s website.
(This table will be heavily used in the next blog post)
The tables 2 to 7 are considered “dimensions” and will be used to enrich the facts table with additional details.
Manipulating Existing Data
The last step before I built the dashboard is to “clean” the data. While the data-set is very well documented and contains many tables and columns, I still had to do some data manipulation and sanitation.
Before loading the data into the project, a good practice is to first transform or edit the data to only contain what you need and how you need it, using PowerBI’s “Transform Data” feature.
For example, I removed redundant data that I knew would not be useful for my analysis. In addition, I translated some items that were written in Hebrew for no reason, like whether a Knesset Member is a female or a male. I also validated that all columns had the right data type and changed it if not. Lastly, just made sure that all tables can be correlated by some keys/identifiers.
After importing all the data, I used the Model view to make sure everything’s fine, and I noticed that the KNS_MKSiteCode was not linked to any table. These links were created automatically by PowerBI (a feature that is enabled by default). However, we will have to understand the relation and create the link manually.
Note: the Model view is a great feature which also allows you to see the relation type (e.g. one-to-many), the relating keys and more.
A simple Knesset report
I created a very simple report using PowerBI. This was my way to get familiarized with the available data. The report included several elements, like this clustered column chart which indicates the amount of bills per year.
As you can see in the above picture, it turns out (based on the available data) that 2008 was the most “active” year with 248 bills. It can be an interesting direction/lead to investigate and understand the reasons behind this phenomena. Could it be related to the Financial Crisis of 2007–2008?
This visualization is just a small part of the report I created to get familiar with the data. I found it super useful to create some charts to better understand what information I can work with.
Summary
This is pretty cool and all but also a little bit boring. We can analyze laws and bills, but I want to have some interesting insights! I want to have additional data sources.
In the next blog post, I will cover how I added data from The Knesset public website using Python and some interesting insights I have discovered based on the combination of the website and the publicly shared ODATA database.
