I'm Daniel from the two-star platform in Europe, and I'm sitting over there, and I also work
with other projects like Detective, which we want to be an open-source solution to make
European public tendering data or public procurement data explorable for people who don't know
that much about the procurement data. So I want to do a couple of things in this talk.
First, I want to describe why public procurement data is interesting, why we should take a look at it,
and I want to discuss some problems of how this data in EU context is currently accessible.
And then I want to show you our project of alleviating some of these problems with Detective.
And then I want to show you how you can actually contribute to the project with your company.
Still very much in the early stages, just getting going, and we love the opportunity to show this now
so we can actually contribute even in the earlier phase of the project.
So what's TET? TET's in the name, and what's TET? So TET stands for Tenders European Daily,
and it's basically a data set that's published by the EU Publications Office,
and they've published this data for a long time. They've been publishing this for a long time since 2015,
actually, they've been providing this freely on the internet, and it's data about basically who buys what from whom,
like which public institutions in the EU buy what for what price from which organization.
So it's really data about the relationship between business and government.
And if, so for example your local school or some ministry in your country in the EU
wants to buy something that's of a certain threshold, they're defined in the EU legislation,
you can look them up in the link here, I will upload the slides upwards.
It needs to go into TET, and it will be in this data set, and there's at least 670 billion per year in value
that's kind of encapsulated in this data, and there's more than 700,000 notices that they publish each year.
They've described this entire process of public procurement in the EU.
It's very great that some of you want to join.
So you put things, well great, you publish it, so what's the problem with that?
I mean the way this data is made accessible is via this UI, one funny thing is,
one funny thing is, this button for statistics mode, I still haven't found out what that does,
like what that changes, maybe somebody from the EU can illuminate,
but basically you have to really know what you're searching for in the first place
in order to be able to use this kind of interface.
And there's also a lot of other problems with accessing this data.
For example, you can't really search by organization, which would be interesting.
I mean it's about the relationship between government and business in all of the money terms.
So why is there no option to search for organizations that I'm interested in?
I can only really do a full text search over these huge XML files, which are really complex.
And I can do some other stuff, but there's no type of tolerance, for example,
none of the really nice search features that we can use to.
And most importantly, there's no ability at all to readily visualize the results that I get.
Like if I type something in here, in a search mask, I get back a list of HTML,
basically just an HTML list of notices, then I need to understand what's a notice
or the different types of notices that I'm interested in.
So it's really hard.
So it makes the test right, because accessibility is really bad with this data.
So why is detective needed?
In the past, there have been a number of attempts to look at this data
and transform it into a more manageable or readily analyzable format.
And we weren't really able to identify a single, freely available solution
that was published under a free software license that allows you to explore this data
even if you don't have domain expertise or data science.
And you kind of need both now to be able to make some sense of this data.
And we thought this would be interesting.
So why isn't this more readily available?
So we applied to last year's EU Datathon with this idea, basically, to make this data more accessible.
And this is what we told them.
So we have any type of, let's say we have a public servant
that wants to find out who buys what from, like, within their state.
Who buys from Microsoft, in Germany.
And how much they spend on free software from this company.
And yes, maybe make the case of how much they can save if they use free software instead.
Or let's say you're a journalist who wants to investigate recent purchases made by Microsoft.
Or authority.
You could do that now with a patent to face, but it would be very, very difficult.
And you'd have to jump a lot of hurdles to get there.
So we want to take it as to be an application that you use
which lowers the barrier of entry to analyze.
So we thought let's present the publications of this concept with free software.
And keeping it very simple.
So we built something roughly with this architecture.
So you have this XML file.
And this was very quickly built just for this Datathon.
So I'll go through it quickly.
So we had this XML file.
I transformed it to JSON for whatever reason, which was a very bad idea.
And I parsed it in Python and put it in some ad hoc schema in Postgres.
And then I used the Neo4j ETL tool to put it to a Neo4j database.
The data I was interested in was relational data between, and it shows the relationship between business and government.
And then I used Neo-dash to visualize that.
And that actually already gave people at PUD some chance to see what might be possible with you if you open up this data.
So I'll show you the little demo of how that looked.
So basically this is just an overview.
I parsed data for roughly three years or two and a half years.
This shows you the activity per country.
This is just some general overviews, like roughly a million tenders.
And then it's not optimized yet.
You basically search for Microsoft Germany and then you have this graph.
You have a geographical distribution of commercial activity that's related to Microsoft.
And you get this nice graph of relationships between Microsoft Germany here in the center as an entity.
And then the yellow or red ones are tenders.
So here they sold something to some institution of German government in this case here.
Mostly because Microsoft Germany mostly sells to German government.
And the red ones are tenders above one million euro.
And that gave you a very quick overview of the commercial activity and the relationship between government entities and business entities.
I do the same with you get more information here.
You can actually go to the TED website to see the notice that analyzed this.
I'm searching for a short question.
You search now for Microsoft, usually they work with like these server providers.
Can we get back to the challenges that we face that you can overcome?
So here I do the same with the Polish order authority.
Here it's more like who there's an entity buys from over the past two and a half years.
You can see what kind of like fence and weapon and ammunition stuff they bought.
I'll get through this because this is actually another problem that I'll talk about towards the end of the talk.
It's deduplication.
So in TED data, as it's published in these Excel files, there's no deduplication of entities at all.
So you can have Microsoft Deutschland, DMPH, Microsoft Deutschland, just Microsoft, whatever that is.
And like you can see here, Microsoft Ireland, like there's all these different.
So I did some very naive deduplication attempt.
I also put that data in a new project graph, but there's much more to be done on that front.
And it's a very interesting problem, I think.
Also because you need to think about it from a policy side as well.
Like is Microsoft Deutschland a different entity from Microsoft Ireland?
And if yes, what does that mean for my data analysis?
Should I analyze them together?
Because they're really operating as one entity.
So they're interesting questions connected to this that are not only technical.
So let's go back to my...
So that was obviously limited in scope, because it was really ad hoc.
It was quickly made, and there were lots of problems with how I parked the stage up for this deduplication.
So now we're at the stage where there's actually a lot of interest in the FST doing this.
I've heard from a lot of people that they would be interested in analyzing this data
and being able to explore this data.
So what's next and what's already implemented?
So there's the open contracting data standard, which is something that actually came after.
TET was first published, so I told you already TET was first published in 2015.
I think the OCDS started being developed around 2018, 2019, something like that.
And if you now build any kind of public procurement platform, you use this data standard.
Because it's just a very nice way.
People have put a lot of thought into how can we display this entire process of public procurement?
How can we put this neatly into a data structure?
And so now we're building TET with this data structure at its core.
And the first task will be to parse this TET XNL jungle into this nicely specified OCDS.
So I built a relational database that roughly captures OCDS.
You see a lot of JCP because some things I didn't do for many to many or many to one,
but JCP for now makes it much, much easier.
Otherwise this table would not have been presentable.
And now, this is the graph system after all.
The next question, because I think analyzing this data, analyzing public procurement data,
analyzing these relationships between business and government,
is probably really lends itself to being encapsulated in the graph database.
So this is really the core of OCDS that's interesting,
and that would be interesting to model in a graph database like Neo4j.
You have this tender.
A tender is basically a company says, like we thought,
like a public entity says we want to buy X or Y amount.
And then an organization, another organization can apply for that.
They're usually like something commercial.
They say, look, we can furnish this tender, like we apply for this tender.
And that's interesting data, you know,
so who applies for which tender and which regions and stuff like that.
And then there's awards. That's basically who gets the contract after all.
And so that would be a very simple place to start with the graph database,
to just have this, have all the test data going back from F15 Parcet into OCDS,
and then take this subset of what's really central and put it into the graph database
and really start exploring this visually and that's what we want to do.
And part of it is already done, so I'm currently working,
we are currently working on parsing this data, this XML.
We use LXML library for that, which is really nice.
And I've parsed this into a relational database,
and I specify the OCDS data schema with SQL model,
which is really cool for the library.
It basically gives you identity models and SQL Openly models in one entity.
It's really cool. It's really nice to work with.
And then I want to create like a CSV export to be then able to input that data in Neo4j,
put fast API, and scaffolding around that,
and then also build some UI, which we are currently researching, which framework to use,
and I'm also here to find out which one would be the coolest one, so I'll stay here,
because I think there will be some problems in Neo4j's data.
Yeah, but there's also React Force Graph, and yeah, really like the nice UI
that's specifically geared towards that use case of analyzing public procurement data.
And yet, I had that back and back by these two, like the relational database
and the Neo4j database that choose, depending on the query which data sources you
actually use.
I'll go through the rest really quickly, but this is, if you want to get on-boarders,
the documentation is still up around the edges.
I'll do my best in the next days and weeks to really make the project approachable
to the developers.
The plan is interesting.
I want to work with you and the CSV on this.
So, some key characteristics that we want to really kind of put a focus on with
Detective is that it's, yeah, it must be free software.
It's reuse-compliant.
It means that every file has the license header and the copyright header,
so that it can really be easily used.
And we want to make it for the people, so like a lot of my work in the next weeks
will also include speaking to people who analyze programming data
and ask them what kind of queries they would, what kind of questions they would like to ask,
because that's really important for the design of the system that you use.
Ask people that are later going to use it, like, how could this be helpful?
We have done some of that, but we will do way more of that,
especially now because we start building the UI.
And we want it to be interoperable, so everything that Detective uses,
every data that it uses will be also published under the CC5 4.0 license,
and there will be open API interface, so that will be completely available.
Obviously, some limits gets too crazy, but we'll think about that when the problem arrives.
And also, we fundamentally believe that link data is more interesting,
because once you have this data in the OCS format, you can start linking it
with other data sources, right, or if you haven't already graphed it,
you can start linking it with other data sources.
Like, things that come to mind would be open corporate data,
where you can really enrich the data that you have in organizations
with data that's in this public database of corporate entities.
Open sanctions would then allow you to flag people or companies
that are on some sanction list, and stuff like the offshore leaks database
would allow you to highlight things to offshore companies and stuff like that.
That's of interest for your analysis.
So this would be a future possibility that I'm really excited about,
but the first step is obviously to get this into a nice format,
and then think about extending it.
Some of the challenges is between this step data, because some of it's quite old,
like if you look at data that was published in 2015,
and it's just, there's a lot of tables there,
and there's these huge XML files that didn't currently do much validation
on the forums that were used to take input this data,
so it's in some places very messy.
And also the S helps a lot actually with starting the session,
because it's a very well-defined standard,
and there's people like the mapping from S to S,
and some people have published, so it's pretty cool.
And then the next big problem that we would be helped with
is duplication of problem entities,
which are already kind of online-inning,
and they are very cool.
So we do have a good idea of that as they contribute,
because I think that's really central to taking it being helpful.
So how can you get involved?
All the code is on our get instance.
At the moment, you can only really contribute PR issues
if you make an account, and I'll get this free.
It's just a couple of weeks, but that's for now if we,
if somebody manages that,
then we'll think about mirroring GitHub,
but let's try this first.
Maybe there's a federation coming for the Git forges,
not there yet, as I understand.
There's also websites with the documentation,
and then you can also write an e-mail to,
this will reach always the maintainers.
Yeah, and I'm looking forward to your question.
Thank you very much.
Thank you.
Regarding funding, did you try to contact the official European institutions
so that you can have funding for this slide,
and so that it becomes like the default slide for that in Europe?
So I know that...
Ah, yeah.
So the question was whether we asked the Publications Office
for funding for this.
Not specifically yet.
I know that they are working themselves on a huge reform
of the entire ecosystem,
so they do this, what they call e-forms now,
which is supposed to substitute what used to be TED,
but e-forms still isn't most yet,
there's discussions around that,
but I don't fully understand all the time,
and they're also rebuilding the TED website.
We should get the compact for them.
I have the compacts because we want this data fund,
and we have the technical contact there,
and we should make use of it,
but I was really that keen to code the past couple of weeks,
but this would certainly be very helpful to reach out to them.
Absolutely, and this will happen.
And we already got some funding because we want this data fund.
We'll use this.
So the data that's currently produced for publishers
is it still some TED or is it also called NOS-DS?
It will be all NOS-DS format.
Honestly, I don't think anything else makes sense.
So it's just a whole data that we will republish as NOS-DS.
There's some place like OpenTenator.q,
which was a component project,
which also does this republishing of the NOS-DS as NOS-DS,
but it's not consistent in how it's regularly
and how it updates its database.
It doesn't seem very actively connected.
I got a question.
When you look at these centers and companies involved,
are you also able to extract what the ActionTender is about?
So is there an underlying structure?
This is about, I don't know, classroom furniture,
and this is about military equipment
so that you kind of can coordinate
both by item or by contract product?
Yes.
So shall I repeat the question?
Yes.
So the question was whether there's also data
on what has been procured and details about
what was being procured by a public institution.
And the answer is yes.
There's usually a title that's fairly descriptive.
And a description.
Sometimes an usage, sometimes another usage.
And then there's CPV codes,
which is more like a common procurement vocabulary
that specifies what kind of category this procurement is in.
But some stuff is excluded by this legislation.
For example, like military equipment.
It's not published in the state.
It's not open.
That's why we can't talk about open procurement
in good context yet, because there's still
lots of sensitive data that's not being included in that.
Do you plan to host it publicly?
Yes.
We plan to host it publicly.
Yes, absolutely.
It's just at the moment that the API is down
because I've retracted so many things.
But it will be off again.
Of course it will be publicly available,
but if everything crashes,
because there's so much interest in it,
then we'll think about limiting it somehow.
But there's a sister from there.
Exactly, yes.
So we'll see.
There's really that much interest in it.
So what was the biggest challenge in cleaning the data?
So I would say one is just finding,
if there isn't English translation available,
finding that for the specific,
because we really lay out layout in this text well.
Whereas if a translation exists,
where is it next in our time?
What does it apply to?
Another one was languages that I didn't know
the alphabet of for the hard to parse.
Yes, I just generalize company names
that they didn't have for a long time.
I mean any validation on what you could put in there,
which makes it really hard.
And it would have been very easy to implement upstream,
but now it's because of the sounds.
Thank you.