Data-Transformation on Historical Data Using the RDF Data Cube Vocabulary
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 16 | |
| Author | ||
| License | CC Attribution 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/47543 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
3
8
10
00:00
Computer animation
00:19
Computer animation
02:51
Computer animation
03:40
Computer animation
05:02
Computer animation
06:02
Computer animation
06:24
Computer animation
07:02
Computer animation
10:22
Computer animation
11:47
Computer animation
14:11
Computer animation
23:02
Computer animation
Transcript: English(auto-generated)
00:07
Thank you for the introduction. My name is Sebastian. Today I'm going to talk about three things. First thing is a data transformation process. Second, about the historical data we are working on.
00:22
And third, about the vocabulary we are using in this context. Okay, good. What I'm going to do, first I'm going to motivate this work and give you a little insight why we are doing this in the project. Secondly, I'm going to talk about the vocabulary in more detail
00:44
and talk a little bit about the dataset so you can actually see what the data looks like. Then I'm going to define the problem setting, which can be determined by the properties of the dataset. And I'm going to show you our approach, how the workflow was implemented.
01:02
And finally, I will conclude with the contributions. Good. So what is motivating our work? First of all, we are working on the statistical and historical data source, which is the statistics of the German Reich. We have access to it in a digitalized format.
01:24
Well, this is data about ships traveling in the 1880s, or it's about import and export of goods in the German Reich. We want to access this data, we want to know what is in there, and we are all doing this in the context of the E-XS project.
01:45
So we want to access the data, recommend it as cultural long-tail data and do some analytics on it, like visualizations or more sophisticated analytics. But first, before we can do this, we need a process to access the data and actually do data integration,
02:06
and therefore we have to do heavy data cleaning, transformation, data fusion. So this is a theoretical approach. To make it more practical, I'll just show you some data of it.
02:22
It's statistical, so you see we have lots of numerical values, and also we have labels which give us the meaning about what is this data actually about. So on the left you can see, for example, a geographic hierarchy. On the top you can see, okay, these tables are not too simple,
02:42
maybe there could be some problems, how to integrate, how to analyze this data. The target structure of our data looks like that. So you have seen what is the source, and this is where we want to go. So naively you would say, I look at my numerical fact and just look at every label which is referenced by this value,
03:08
and reorder it into my nice table, and then I have this normalized version to do the data analysis on it. So for example, here you have your values together with, in the first column you see these are only numerical facts,
03:30
and then you have geographical information, you have time information, in a very normalized and approachable way.
03:41
But before we go into the actual transformation, let's talk about data cubes and the vocabulary. A data cube is a multidimensional data structure known from data warehousing processes, and it's specifically built to do analytical operations on it.
04:03
So maybe you want to do aggregations, you want to combine data from different years, or you want to drill into the data, or have a more aggregated view. And you can always think, for example, you see the sales cubes over here,
04:24
your numerical facts are for example your sales in a specific year, and your dimensions for this to define what this numerical value is, you need to know which client did you sell your product to, what was your product, and when did you sell it. So you have a key value relationship between all the dimension values and your product value.
04:49
So this is the visualization thing, you can have more than three dimensions, but you can keep this table in your head, this is the representation we are targeting for.
05:03
And what is the RDF data cube vocabulary? This is actually an RDF based vocabulary of course, and it just models this data cube in an RDF style. So you can reuse these concepts in the RDF world.
05:23
And also this brings us the interlinking, and disambiguation, and lots of other advantages you don't have in classical data warehousing. So you can profit from this concept here. So it defines the vocabulary, it defines what are dimensions, what are measures, you can construct hierarchies,
05:49
but for now we can just think, when we talk about the data cube, you can think about this ordered structure table as I've shown you before.
06:02
Good. Again an example. Well this one you would say doesn't look too difficult to interpret. So you just take your numerical values, put it in your column, and look what are my dimensions which describe my values.
06:20
But it gets more difficult. For example, this is the one you've seen before. You see there are lots of missing values, hierarchies on one side, or very complex and depending dimensions. So for example, on the top you see these dimensions actually form a sentence.
06:43
And you can just reorder them, because then you would lose the context of the cells. So they actually highly depend on each other, and it's not just, okay this is this value, I just reorder it, because you don't have this structure anymore.
07:02
Luckily, we don't have to work on images of this data, but we have data which is in the TE format, so this is an XML based format, and we also have annotations on it, like we know what is a data, we know what is a label, which helps in the conversion process.
07:25
So this looks like a very simple example, but we have actually also problems. So let's first have a look at the data. For example, here you see a row which contains a sum.
07:43
It sums all values which are above of this row. This is, at first this doesn't look bad, but in the target structure you can't have this. All the data have to be in the same granularity, and also when you aggregate every of the data,
08:01
you can't have the sum already in there because your computation wouldn't be correct anymore. Let's look at another sample. Here are repeat samples. So the data depends on the cell above.
08:21
If you do your reordering, this reference is broken. Here we have a composition. These files are in this way that one document corresponds to one page in the book, but the tables are larger than one page, so we have to concatenate lots of these pages,
08:43
and in the books there are references, headers are repeated, or chapters are repeated, just to know where you are, but when you integrate the data, you have to resolve all these dependencies which are inside of the table, and there are lots more of this.
09:02
There are lots of very small structural properties which have to be resolved before they could break anything in your final target. These are not even every possible mistake or error in this data,
09:21
and this is a very simple table. So your algorithm you would need to implement to convert this to a target structure could get very, very complex because you have to consider every of these problems. And also, not only your data can actually be problematic, but also the structure of the tables.
09:47
For example, labels could be wrong, or structural dependencies could be wrong, there are row and call spans which have to be resolved, and if in your source data is only a minimal error,
10:04
it would make your table crooked, and all your resolving would be false. So you have to do a lot of checking in this case, to not only the data itself, but also you have to check the structure.
10:21
So let's define the problem setting. Our data is encapsulated in multiple files, where one file corresponds to one page in the actual book. It is in this structure unusable for sophisticated data analysis
10:41
because you can't apply your standard algorithms to it, because you have to do a heavy normalization before you can do this. So you have to resolve this complex structure to have a normalized structure.
11:01
Also there is dirty and faulty data, and also you have to check structure and the annotations because they might introduce another error. And also the status is very huge, and you have lots of really small problems,
11:22
but they add up to a really high amount, and also you don't know which of them will occur in your table, and how do they relate to each other. So you have to resolve it in a specific order, because fixing one problem could generate two new, or could break the resolvement of another.
11:45
So how can we approach this? First of all we have chosen the RDF data cube vocabulary as our target format, because it gives us this normalized view of data, which is able to be the input for data analysis,
12:03
and also we can interlink existing resources, and also we are able to merge this data, which comes from the different data sets, and give it into more sophisticated analytics.
12:22
The next point is we've developed an incremental workflow. This means that we are not trying to look at a table, and build this huge algorithm, which is then able to fix all my problems, then get to the next data set,
12:40
which is about some other data, and do the same thing again. The approach is to generate a set of transformations, which are very fine granular, and one algorithm only fixes one problem in this data set, or in this table. And so you have a huge set of these transformations,
13:02
and the next step would be, how can I order these transformations, and how and which sequence have we applied to this data set, to be able to get to my normalized version. And therefore a prototype was implemented,
13:20
which can do this, and it also has a not very beautiful, but functional graphical user interface, which allows you to do that. So you can select and configure what transformations should be applied to the input data, and you also get an HTML preview of every step you have done.
13:43
So you see, okay, this row probably must be deleted. You select the according transformation, click on the correct row, and it will be deleted. The next step you will see, okay, this row is gone. Now we have another problem you can fix,
14:00
and iteratively you can work on until all datas, or hopefully all datas are fixed, and you can export it. So how does the actual workflow look like? First of all, the prototype is capable of loading a complete link group.
14:22
A link group are all tables that are part of one table, which might be split over different pages, so we all load them at once, and we pass all the files into an internal data format. So we are not anymore depending on the TE format,
14:43
and doing all the transformations on Java objects, and therefore this prototype is also capable of importing other formats, for example HTML tables or something like that, and therefore other importers could be written.
15:03
The next step is merging all the single tables into one large one, and then the real work begins. So you have to look at your preview and see, okay, maybe the merging is wrong, so you have to fix some spans or something that the alignment is correct of all this data,
15:22
and then you can check all the transformations and apply them. In every step, the HTML visualization is produced to give you feedback, instant feedback. Finally, when you're done, you can export this into your RDF data cube,
15:41
and then we are able to reintegrate it into the E-XS workflows and do recommendations or something on it. Also it is possible here to export it to other data formats. If you do not want to work on RDF data or you have specific needs for your data, you can also write another export mechanism here.
16:07
Good. So let's look at some transformations. Everything revolves about this normalization process we had in the beginning. You're looking at your facts,
16:22
and you're trying to find all your dimensions which are relevant in this case. You have to reach this step, but first you have to restructure your data to be able to perform this step. So this is your temporal target to find a normalized version of your table
16:42
which you can then reorder. And mainly you do that by introducing redundancy into the data. So the source data is very precise and very dense and normally relatively good, understandable for humans,
17:01
but when it comes to the computer, it's getting different. So you're trying to normalize the data by introducing redundancy in the data, which is later no problem, because in the analytics there are other formats and other schemas which are prepared for that, but for our case, redundancy is the thing to go for.
17:25
So most transformations actually deal with pre-normalization steps. So there are, for example, over 30 of them, and most of them do some sanity checks or do data cleaning. For example, they try to fix the structure, they introduce redundant cells
17:41
to split up the column spans and row spans, and also, for example, deleting rows. For example, they're deleting headers which could be in there. The second step is the normalization. You can have just a simple one or there could also be multiple cubes in one table
18:03
or there could be still a horizontal or vertical partitioning of your data, for example, because of repeating headers, and there are more complex normalization algorithms which can also deal with that. And lastly, you can do post-normalization transformations.
18:25
You can think about it as, okay, now I have a normalized table which is very column-based, and here you can add or merge columns. Merging could be, for example, relevant if there are any hierarchies in your data and you want to resolve it by combining it into a single cell.
18:44
And also, you can add the headers. For example, when you have lots of products in one of the cells, you need to describe it. For example, you need to say, okay, this is product, and in my cells are the instance of it,
19:02
and then also you can find a disambiguation for it. This means you can link it into an existing, for example, DBpedia resource, and to link it to existing data. This then can have additional benefits, like you're able to merge some of the data.
19:21
And also you can add metadata like provenance information or something like that. Good. There are also advanced transformations. So I told you there are lots of very simple ones, for example, deleting a row, and we are able to combine such simple transformations
19:44
into more complex ones to be able to fix more complex problems. For example, finding errors and then automatically fixing it with such a combined transformation. So for example, every repeat symbol can be,
20:04
or every sum column can be deleted, so we are looking for there is a sum, and then automatically applying the already implemented transformation. Also, to assist the user in the graphical user interface, there are transformation suggestions implemented,
20:27
like if you find characters which tend to be the cause of a problematic or an error, you can suggest to him, okay, in this cell you have this character, maybe you want to delete the cell,
20:41
or you have to do some structural modifications to it. And also, this is a step towards automation. Because the user, this must be a manual process, because the structure is too complex for an automated process,
21:00
but scanning the layout and the structure, you can determine if there might be a problem, and then suggest the user, okay, look at this specific point, maybe this is an error, you have to fix it. And then add the contributions. So we've implemented a modular workflow
21:21
for a data integration process. We have defined a huge, relatively huge set of granular transformations, which are also combined to more complex ones. And this prototype is usable with this data source,
21:41
and also with other data sources. And also the good thing is, you have lots of these links groups are very similar to each other. And what you can do is, you can use an existing chain of these transformations and apply it to another one. For example, the imports, not of the year 1880,
22:01
but of the year 1881, but the structure is minimal difference, so you have other structural errors. You can reuse your transformation sets with little modifications. Also, we have lifted and enriched historical statistical data by linking the headers,
22:22
this integrated headers into the data cloud, and this is now ready for visualization analysis. And our current data set contains more than 30,000 files, and we've converted more than 10% of it. And in this process, we've created 10 of these conversion chains,
22:42
whereby five of them are very similar, because we used similar input tables, and also there are different ones to see if we can reapply the implemented transformations to the new data sets.
23:02
Thank you very much.
Recommendations
Series of 17 media