Reporting Databases
CC Attribution 3.0 Germany:
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.
Regebro, Lennart
It's hard to make reports from NoSQL databases, and this is true for some SQL databases as well. And what if your reports need data from several databases? Then you need a separate reporting database. This talk will give you an introduction to reporting databases - what they are, when to use them, how to design them, and a quick look at some of the tools available. Lastly we'll look at how we implemented reporting databases at Shoobx and some of our experiences there.
 I talked about time zones, but I had technical problems. So I was not able to start with this song, which was the whole point. So it's extra content. So we'll see. All right, looks like everybody's here. Right, I have to switch my glasses too, so I can see something. OK, so my name is Lenard. I haven't been on the Plum Conference since Arnhem. I could spend some time talking about what I've done since, but that would use up all the 40 minutes. So I'm not going to do that. Instead, I'm going to talk about what I've done the last half year for which I've worked for a company called Chewbox, which is here in Boston, although I live in Poland. And Chewbox is the one place where entrepreneurs, lawyers, investors, and board members can come together to generate, review, approve, and execute company documents. It's a system of record execution and engagement built to simplify standard corporate legal processes. And if you want to know what that actually means, you can talk to Stefan over there. But we handle legal documents and legal things for companies, hence the name Chewbox, which is traditionally where you keep all your documents related to a company. And at Chewbox, I've been mostly dealing with reporting. Because you want reports, you might want many reports, which office is selling the most yellow telephones, which workflow step are people getting stuck at, are we actually getting new customers at a rate that is higher than what we are losing the old ones. People all around the company have many questions. And you have a big database, either a big relational database like this one here. It's extremely normalized because it was designed by people who's taking university courses in relational databases. So it means that no data is duplicated anywhere, and you have several tables that contains nothing but a key column and a value column, and your queries become very complicated, have many joins. If you do one of the joins wrong, the query will take 10 hours instead of 10 seconds. Or you use a no SQL key value database, which may have some indexes, but not only things you need to index on, and there's no reporting tools for it, especially because you selected it as the brand of obscure no SQL database that was the database of the week when you started the project. Or because you're using such a fantastic and stable and old, amazing database that nobody else has forgotten which one it is. Or the typical case is, of course, that you have many databases with information relating to your company. So if you're going to make a report, you have to make these elaborate queries with loads of joint statements for the SQL database, and you have to write a little program that exports data to a comma separated file from the no SQL database. You have to merge them with a little script, and out you get a nice table and a nice graph out of the data after using some office program. And all is well, and your boss is happy, and your job is secure, and she actually wants a new report the next week, but then somebody has slightly changed the schema for the database. So your queries don't work anymore. But your boss wants the week's report. Now, in fact, she thinks they're so useful she wants it every day. But with all these querying and merging and manual changing you have to do of the reports, it actually takes you half a day to make the report, which means you don't really have any job over to make your actual job. And the query takes so long to run and loads down the database so much that the application gets slow and people start complaining that the internet is slow because they don't actually know the difference. And they complain to the network guy, and he gets angry at you and stops bringing you coffee when he fetches coffee. So how can you get your coffee back? Well, obviously the title of the report, the talk has already said what you need a reporting database. That's what, because databases are designed to be fast and useful for the software that uses it, not to making reports from it, which means that you'll have an index on last name and first name of all your customer, but you won't have an index of which region of Poland the customer belongs to because that's not what customer service searches on. But you need to search on that because you need to make one report per region. The reporting database is made to do reporting easy and it's not afraid of duplicating data. It can integrate data from several sources and it's often, even typically is read only, that is you load in the data in batch form and then you don't modify it after that. When should you use it? Well, if your transaction performers is critical, so you can't modify the database in any way because you can't make it slower or you have multiple databases or if the reports take a really long time to run or if you need to massage the data before doing a report. Massaging here means that maybe the internal field names and values don't actually match what the economics department want. We internally have a lot of companies in our database, we call them entities because they're legal entities. Maybe not what you actually want to type out. We want to have out entities on our reports because we know what it is, but maybe on some report it should actually say companies, things like that. Or the regions of your state or country might not actually fit the regions of your company so that you have to map over each customer depending on address to sales office. That's a typical data massage. And how do you do it? Well, it should be a separate database, should preferably even be a separate server so that it doesn't load down the production database. Updates need to be asynchronous, meaning that when you update the data in the production database, the reporting database should not be updated at the same time because that will slow down your application. It needs to be either an asynchronous call, so it's done by some other asynchronous worker later or which seems to be more common using batch exports. Data should be rebuildable, so you can drop the whole database and rebuild it. And it should use star or snowflake schemas. And that's schemas that look like this. They're simple schemas where relations are only between the central fact table and the surrounding dimension table. And the fact table should hold the measurable quantitative data. That means this is what you want to get numbers out of. You want to know how many for which quarter of something. That's a fact table. And the dimensions are the descriptive attributes. So for example, a fact table could contain a list of all sales transactions, while dimension tables should hold the information about the things sold and the customers, et cetera. In practice, it can be hard to know what is the fact and what is the dimension or what should, at least, what column should go in the fact table and what should go in the dimension table. But with a reporting database, always design it so it makes it easy to make reports. The important part in the star schemas is that you don't have relations between dimensions because then queries can become complicated again and you can get absurd results when you do an inner join and you should have done an outer join or the other way around. You can add another level of dimensions so that the dimensions also have dimensions and then you have a snowflake schema. I don't know how common that is. We don't have any use of that. And one dimension you have is a bit special called the date dimension. So it's worth talking about that. And that has to do with date calculations being both tricky and slow and not searchable. So you can, for example, not make a query on a date field saying that I want every Tuesday from the third quarter out and nothing else. So if you have a date dimension, which is a table that keeps all this information for every date, pre-calculated. So I don't know how much you can see of this. Actually, these are the fields that we have in our date dimension. We're probably not going to use a lot of them, but it's good to have there. So it's information like, yeah, which month, which day, which day of year, which weekday it is. But also display information. Like we have the field, or the column called quarter ordinal, which in this case contains the string 3rd, which means that it's easy to make a table where it says first, second, and third for the quarter when you're going to do a nice or chart rather. The ID, as you can see, is an integer because it needs to be an integer for fast indexing. But it should be an integer that is easily recognizable for humans. So I'm using the ISO date format, but in integer form. So this specific date, the 6th of September 2016, is then 20,160,906, which mathematically makes absolutely no sense. But you can easily recognize what date it is as a human. The basic procedure for updating the reporting database is called extract transform load. And that sounds obvious. You extract the data, you transform it, and then you load it into the reporting database. But it isn't actually obvious because there's extract load transform as well. That's used for data lakes, where you stick all the company data unstructured into huge databases, which you then do data mining on. But those kind of things are for when you don't know exactly what it is you're looking for. In this case, we know what we want to do reports for. We know what we want. We want reports. So then we should have instead what's called a data warehouse, and then you do extract transform load. Because the database should be easy to use. So the data should be in a ready-transformed, easy-to-use date. These three steps don't need to be done at the same place or at the same time or even on the same computer. And where you do them depends a lot on what you have for requirements. Extracting can be done basically in two ways. Either you do it in some sort of batch fashion. Every common is, of course, to do a nightly batch extractions. But those tools that exist, often called data integration tools, they can often do this quite often. They can do a batch extract maybe every 15 minutes, for example. If you have very sensitive data in your database, you have customer-sensitive data, the extract step should be careful of what is extracted. So that's kind of a half-transformed step already. If you don't have sensitive data, you can actually just dump all the tables out in raw forms and use that. But you don't want to have world-readable CSV files with sensitive data, such as if there's any Catholics that has bought French ticklers. And therefore, it's not allowed to be in multi-pital movies. But the big batch extract is the most common, as I understand it. And it's preferable if you can do a dump of all the data in a reasonable time frame. But that means you need to be able to dump all your data out maybe once a night. If you can do that, you can dump all the data out. And you can drop the reporting database, massage the data, transform the data, and load it. So you have a nice new fresh reporting database every day. You can do that in those cases. A lot of times doing such an extract is going to take too long. Our extract currently takes around two hours. But we're then hopefully just now in the end of a beta phase. So hopefully we're going to get more than 10 times more of the customers. And then extract would take 20 hours. And it's going to be kind of hard to do it. So we're not doing that. Or we could then maybe do one of these extracts. Well, in the end, we'll end up in a situation that we would have to run several extracts at the same time. So that gets to be a bit tricky. But again, using batches means you have ready-made tools. So that's a big benefit. And if you can't dump everything out every night, you need to be able to detect changes. It's called change data capture. You have to figure out what it is you're going to extract. And then if you don't extract everything, you risk that the data is inconsistent, that it's not actually the same as in production database. But on the other hand, you don't have to use up your main database so much. And change detection can be done in four ways. Time stamps so that everything that gets changed gets a time stamp. And then you just do a query saying, I want to export everything after the last time step. Sequence ID, if it thinks that gets added, or database logs. So you can add most many databases, have the possibility to create a log of each record in which each table that got modified. So then you can use those to then extract the right data. And the fourth way is to do an update on change. So you might remember that I said they should do the update asynchronously. They shouldn't actually update when the main application data updates. Well, you can, but you have to do it asynchronously. So you have to have some sort of event system. And again, databases, many databases do have event triggers. So if a record gets triggered, you can write something. If a record gets modified or added, you can actually execute some sort of code. Or your application might have events, but this typically means that you have to modify your own application. We are the ones that are modifying our application. And if you're running Plone, then you can modify your own application and you can make your own event hooks. So that's fine. But not all the time, not all the applications might have this. And the benefit is you get near real time updates. But you have to use some sort of asynchronous framework like Celery, which is what we're doing. So you get the information that something has changed and you just tell the asynchronous framework that this has changed. And then in the background, things get updated and exported. The transform step means then that you need to take all this data that you have extracted and make something that you can import into the reporting database. Worst case, you might actually need to load all the data that you have from a table or maybe two tables and then somehow do magic on that to get the data out. And that can be so much that you can't even keep the data in memory, which means you have to create a temporary database and do that for there, do queries from that. But that's kind of unusual. I think it's pretty unusual. Most cases, you can do the transform record by record. So you can do a simple script to transform the data and then maybe read in comma separated value files and then print them out again. And then you have the data that you want. And then you can load it into the database. This is so trivial that you don't actually really have to separate the transform and load. You don't really have a clear separation between the transform and load because once you have the data, it's just a question of writing it to the SQL database. And ta-da! You have a reporting database and you can make reports. But for that, you need some sort of reporting tool. We have looked at a bunch of reporting tools and this is my opinions of the ones that we have looked at. But we have only looked at open source reporting tools because we're a bit fanatic and I am about open source. So we haven't looked at any commercial ones. And almost all of the open source ones that exist are bad in various ways. They are definitely not really designed to be used by normal end users. And quite often you need to write your own SQL. But for the first one, you don't. LibreOfficeBase is the only open source tool that is aimed at normal mortals and it has this query editor where you actually can make SQL queries without typing any SQL. It's pretty good. Is it use? The reports, however, made in LibreOfficeWriter and it's by default horribly ugly. You can of course theme it as you want because it's LibreOffice after all, but LibreOffice is not that easy to make templates in. And I couldn't figure out how to insert a chart, which is something you want. So maybe you have to use then LibreOfficeCalc to make the charts, but what you can do also with this. But often you sometimes you maybe want to have the same in both reports. You want both a chart and a table and I can do that. A power user could probably use LibreOffice. Then we have BIRT, which is a plug-in to Eclipse. And if you think that it's strange to write a graphical user interface tool as a plug-in to a code editor, then we can only blame Emacs because they started it. So yeah, it's a plug-in to Eclipse. It's kind of slow, it's kind of weird partly because it's part of Eclipse. So it has a lot of menu items and things that makes no sense and has nothing to do with reporting. But it is usable and their default reports are prettier than LibreOffice. But here on the other hand, you have absolutely no help in writing SQL queries. You have to write them by hand. The last tool that we have looked at, it's Pentaho. And Pentaho comes in two versions. It's a community edition, which is open source and free. And an enterprise edition, which is so expensive that they don't even tell you how expensive it is. Which if you know from shoe shops and stuff, if you have to ask how much it costs, you can't afford it. So that's the question. So Pentaho Enterprise Edition is only really interesting for enterprises who have basically infinite amounts of money. But Community Edition is pretty good and the Enterprise Edition doesn't seem to have that many more features. The Enterprise Edition has a JavaScript-based report generator where you can create your own reports, which is a little bit more usable than the one that is written in Java, that is the desktop one. But it's also kind of almost a little bit more strange. So it takes a while to get into that one as well. So it might not be worth it. But Pentaho has a lot of other cool features. It's not only a report generator, which is what you're seeing here. It also has data integration tools. And so it's the whole thing for doing all of the reporting database stuff that you need to do. And the Pentaho Report Designer is maybe about as quirky as Birth is or Burt. I don't know what it's called actually. But quirky in a different way. The theming is probably incomprehensible unless you're already used to CSS. The theming isn't CSS though, but it works in very much the same way as CSS. And that's probably because this can generate HTML reports as well. So they can transform your theming into CSS, which is what they're doing in the background. So you theme things by having a long list of themes where you name actually what kind of field and in which place it is and that kind of stuff. And that's how you add theming. It also has a nice benefit because it has what it's called a business analytics server, which as I understand it runs the data integration that we're not using. And it's the bit that will run every 15 minutes or once a day or once a week, the batch export and transform and load stuff. But it also has a feature that you can publish reports to this database server. And then anybody with the password can go to that server in their browser and run the reports, which is very nice. It has a GUI query designer like LibreOffice, but it's not as good. So you end up having to type SQL anyway. I was going to make a slide here on libraries and frameworks in Python that can help you do all these things. But after looking for it, I can only find report lab where you can take your data and make nice PDFs. So if you want to do the whole batch thing, including the extract, transform and load and generating the reports, all batch during the night report lab is a good thing. You can do it. You can even then mail the reports to your boss when the report has run finished at 8.15 in the morning. And the boss will think that you're awake. If you know of any libraries and frameworks that can do reporting, tell me and I'll add it to the slide. So how do we then do this as shoebox? Well, we do it the hardest way we could think of because we have very unusual requirements or had, I should say. So our solution is unusual. We wanted near real-time data because we wanted this to be partly customer-facing because we had some reports, essentially, or data that were essentially reports that we needed to show to customers through our main application. And we wanted to offload that to a reporting database. But since this customer data is super sensitive, it meant we needed one database per customer that added some problems. And we also then liked that was not a hard requirement, but we definitely liked that you could run the reports as needed. And this means that we ended up doing the update on change, as I say, that we have, our updates are done asynchronously with salary in the background instead of by batch. And we needed one database per customer, although that requirement has gone. We're not doing that anymore. We decided to use Pentau. What helped us is that although we have high requirements, we also have an infrastructure that could easily handle that because we only have one source for data. We have two different databases, but it's just one application. So we don't need to merge data from different databases. And that application is written by us, so we can change it as if we like. And we already have events because this is built on the SOAP tool kit. So we're essentially using SOAP 3 or large parts of it. So we have there's modification events for everything already. So that was easy. And we already had salary set up to do other asynchronous tasks. So all we needed to do was to add a bunch of salary tasks to export the right data and then hook in events. To call these salary tasks. Now that was harder than it sounds, but we could do it. So our extract step looks like this. We have the events, normal SOAP events stuff, where we send just information to salary of what changed and when, what time stamp this happened, what time it has happened. And then you collect the data because we have super sensitive data. Because we can't just dump the objects directly out of the database. We have to choose what we extract. And then we make a REST call with the requests library. And that REST call is picked up by a server we wrote with Flask and SQL Alchemy and PostQuestQL that has a REST API that Stefan fixed and implemented. So it will then just do some minor transforming of the data and it sticks it into the PostQuest star schemas. We use Pentaho as I mentioned and a report with a chart looks like this. There you can see how many incorporations that has been done per month. And you can see that there's one in 2013 and that's the test data that comes automatically when you load a test data for development environment. And then we've got one incorporation, which is the yellow one here in August 2016, which is when I made the chart. So it's not very interesting, but it looks good. And it's themed to use the shoebox colors and the shoebox logo. And here's the column or table report. And here you can see also that up there you can select which company you should run the report on. So this is another feature of the Pentaho reports. You can publish them like this and you can actually have input fields. So you can let whoever runs the report say which company and which time frame you should use and things like this. And here is one example of the star schema. So we see we have here in down in the corner, we have the date dimension. And the fact here is facts about work items, which is a part of the workflow. So this fact table is what we're querying on when we query the table we saw before, which tells you which workflow step people have gotten stuck on. So you can see who is stuck on which workflow step. We use that for reports. And here's another one. And one thing you can see here is that we again used the date dimension at the corner this time and we actually use several of the other dimensions again. And in the blog posts and papers I found about star schemas and reporting databases, nobody mentioned this. So and definitely nobody said that you can't do this. So we're actually reusing the dimension. So our schema really looks like this. So it's much more complicated because this is five star scrimas that are overlapped on each other. But because each time you make a report, you only care about one fact table and the dimension table is connected to that fact table, it becomes a star schema and each query is there. They're easy, every query, even though this schema looks horribly complex. So I think you shouldn't really view it as one schema, even though we are reusing the dimension tables. We also have things like both an entity fact table and an entity dimension table. So when we do queries on entities, that is companies like the first chart on how many has been onboarded and incorporated, then we do a query on the entity fact table. Otherwise you have the entity dimension table, which are just then to display the company name basically in reports. And yeah, I forgot what I was going to say about that. But yeah, it looks a bit messy, but it really isn't because you should look at it like this, one fact table at a time. So we haven't had any problems so far with reusing the dimension tables. So yeah. So you have the ID from the dimension table and the fact table, for example entities, if you get in sync? Well, no. Well, yeah, we have internal IDs and those are in sync, but they come from the application database. But then you can also have just a key column and that's not in sync. That doesn't matter because you'll never query from the entity. If you have the entity fact, the information you have about the entity is already in there. But you could use the entity dimension table in the entity fact too. If you want to, if you want to make it a little bit more normalized, then they would be sick. Yes? You're not expiring your dimensions then? No, no we're not. So because you can't do that if you're expiring dimensions, if you have dimensional changing based on facts, then you can't share the dimensions. All right. Yeah, well, yes. I mean, the thing is if we do have dimensions changing based on facts, but that information always comes from the production database again, which means that it should be updated. That means that then something, for example, if the name of the entity has changed, then the name of the entity has changed. So then we change the entity dimension for that. So if you do a total drop in replace, you can do that. If you wanted to look at historical analysis of a dimensional change, you couldn't do that. No, no, right. So we can't extract information. If we would extract, we make a report on 2000, in 2013, and the company name has changed we get the new company name. That would be the reason for slowly changing dimensions. Yeah, yeah. Yeah, and the strategy of that is usually to just append on the dimension tables. Right. Yeah. Which we don't have any for. No. So this is an example of a query. This is the one you saw about the work steps where you stuck on the work steps. So the queries are very simple. You just select all the data you want out from then the work out in fact joined on the dimensions. And the benefit here is that for each of these five star schemas we have, this join bit, the from bit is always going to be the same. So you can always, you can just cut and paste it. And then I filter out what I want. And here you see something that's not SQL in the bottom there, which is why actually the nice little syntax highlighters say that this is wrong. And that's because this is the field you could select. So it gets stuck in there. Some problems we had. And here you can see that it's a lot of information here on this slide. And this is because I ran out of time. So I didn't have time to make lots of slides. But some information, some problems we've had is for example, how do you know if data is missing? Well you don't because we don't do a complete drop and replace. We don't really know if data is missing. We check how many entities we have in the application database and in the reporting database to see if somebody is missing. But we do not do queries on each of these entities to see that all the documents and everything is there. Because doing such a query would take about a long time to exporting the data. But we can resynchronize everything, of course. So that was one of the requirements. You should always be able to rebuild the data from scratch. So we can do that. And synchronous updates slow down the app. So we have asynchronous updates as we said, but they can come in the wrong order. This was a thing we also didn't think about from the start. Because salary can pick up the tasks in whatever order salary feels like. And this is why we added, so we have timestamps. So the event will start the salary task with saying what changed and when it changed. That means that our Flask server, when it gets the information, if the information is actually older than what we already have, we just ignore it. The only really big problem we had, that problem that actually took down our production servers is that the requests library is a little bit too good, but not quite as good as I thought. I knew that URL lib doesn't have timeouts. But requests lib is so good and easy to use, so I didn't think of that when it came to the requests library. Everything just works, right? It turns out the timeouts is actually not in any of these libraries. The timeout is the socket library, that said. So in fact, the requests also by default do not have a timeout, which meant that our salary workers slowly got standing and just waiting for the reporting database after we got connection problems in the reporting database and it stopped answering properly to requests. So that was an easy fix, but yeah, that's a little warning. We wanted multi-threaded server for speed, but when we implemented that, we got massive write conflicts that I didn't get locally. That turns out because the conflict strategy on Postgres SQL by default is one that is very nice and you basically don't get conflict errors because everything just waits for each other. So you don't get conflict errors, but for reasons I don't actually remember, the people who know databases better said we cannot use that in production and you should not use that in production. So we had another one and there you can get conflict errors and we got massive conflict errors. So we had to implement that, we had to fix that by implementing retries. So we now implemented retries both in the database server. So if you get a conflict error there, we just try again three times, we try. And we actually also on the application server and have retries. So if you get any sort of error that is not an internal server error, no, right, no, any kind of error except 400 errors of some sort, which because a 400 error basically tells you that you made a wrong request. So there's no point in retrying that. But all other errors including connection errors and internal server errors, we just retry because an internal server error is quite likely a right conflict. That means we now have up to nine right conflicts and the last sync that I did of all the data didn't have one single error message in any logs that I could find. So I think we got rid of all the right conflicts. I hope. That's all I have questions. The slides are available. You only need to remember slides.colliberty.com. Colliberty is my old company that I don't have anymore, but I still have to remain so I stick everything up there. So slides.colliberty.com and then you get a list of basically all conferences I've ever presented on and you have the slides there. Okay, no questions. There were some questions during the talk, which is okay, but nothing left. Good. Then I'm done. Thank you. Thank you. Thank you. Okay.
 I talked about time zones, but I had technical problems.  So I was not able to start with this song, which was the whole point.  So it's extra content.  So we'll see.  All right, looks like everybody's here.  Right, I have to switch my glasses too, so I can see something.  OK, so my name is Lenard.  I haven't been on the Plum Conference since Arnhem.  I could spend some time talking about what I've done since, but that would use up all  the 40 minutes.  So I'm not going to do that.  Instead, I'm going to talk about what I've done the last half year for which I've worked  for a company called Chewbox, which is here in Boston, although I live in Poland.  And Chewbox is the one place where entrepreneurs, lawyers, investors, and board members can  come together to generate, review, approve, and execute company documents.  It's a system of record execution and engagement built to simplify standard corporate legal  processes.  And if you want to know what that actually means, you can talk to Stefan over there.  But we handle legal documents and legal things for companies, hence the name Chewbox, which  is traditionally where you keep all your documents related to a company.  And at Chewbox, I've been mostly dealing with reporting.  Because you want reports, you might want many reports, which office is selling the most  yellow telephones, which workflow step are people getting stuck at, are we actually getting  new customers at a rate that is higher than what we are losing the old ones.  People all around the company have many questions.  And you have a big database, either a big relational database like this one here.  It's extremely normalized because it was designed by people who's taking university courses  in relational databases.  So it means that no data is duplicated anywhere, and you have several tables that contains  nothing but a key column and a value column, and your queries become very complicated,  have many joins.  If you do one of the joins wrong, the query will take 10 hours instead of 10 seconds.  Or you use a no SQL key value database, which may have some indexes, but not only things  you need to index on, and there's no reporting tools for it, especially because you selected  it as the brand of obscure no SQL database that was the database of the week when you  started the project.  Or because you're using such a fantastic and stable and old, amazing database that nobody  else has forgotten which one it is.  Or the typical case is, of course, that you have many databases with information relating  to your company.  So if you're going to make a report, you have to make these elaborate queries with loads  of joint statements for the SQL database, and you have to write a little program that  exports data to a comma separated file from the no SQL database.  You have to merge them with a little script, and out you get a nice table and a nice graph  out of the data after using some office program.  And all is well, and your boss is happy, and your job is secure, and she actually wants  a new report the next week, but then somebody has slightly changed the schema for the database.  So your queries don't work anymore.  But your boss wants the week's report.  Now, in fact, she thinks they're so useful she wants it every day.  But with all these querying and merging and manual changing you have to do of the reports,  it actually takes you half a day to make the report, which means you don't really have  any job over to make your actual job.  And the query takes so long to run and loads down the database so much that the application  gets slow and people start complaining that the internet is slow because they don't actually  know the difference.  And they complain to the network guy, and he gets angry at you and stops bringing you  coffee when he fetches coffee.  So how can you get your coffee back?  Well, obviously the title of the report, the talk has already said what you need a reporting  database.  That's what, because databases are designed to be fast and useful for the software that  uses it, not to making reports from it, which means that you'll have an index on last name  and first name of all your customer, but you won't have an index of which region of Poland  the customer belongs to because that's not what customer service searches on.  But you need to search on that because you need to make one report per region.  The reporting database is made to do reporting easy and it's not afraid of duplicating data.  It can integrate data from several sources and it's often, even typically is read only,  that is you load in the data in batch form and then you don't modify it after that.  When should you use it?  Well, if your transaction performers is critical, so you can't modify the database in any way  because you can't make it slower or you have multiple databases or if the reports take  a really long time to run or if you need to massage the data before doing a report.  Massaging here means that maybe the internal field names and values don't actually match  what the economics department want.  We internally have a lot of companies in our database, we call them entities because they're  legal entities.  Maybe not what you actually want to type out.  We want to have out entities on our reports because we know what it is, but maybe on some  report it should actually say companies, things like that.  Or the regions of your state or country might not actually fit the regions of your company  so that you have to map over each customer depending on address to sales office.  That's a typical data massage.  And how do you do it?  Well, it should be a separate database, should preferably even be a separate server so that  it doesn't load down the production database.  Updates need to be asynchronous, meaning that when you update the data in the production  database, the reporting database should not be updated at the same time because that will  slow down your application.  It needs to be either an asynchronous call, so it's done by some other asynchronous worker  later or which seems to be more common using batch exports.  Data should be rebuildable, so you can drop the whole database and rebuild it.  And it should use star or snowflake schemas.  And that's schemas that look like this.  They're simple schemas where relations are only between the central fact table and the  surrounding dimension table.  And the fact table should hold the measurable quantitative data.  That means this is what you want to get numbers out of.  You want to know how many for which quarter of something.  That's a fact table.  And the dimensions are the descriptive attributes.  So for example, a fact table could contain a list of all sales transactions, while dimension  tables should hold the information about the things sold and the customers, et cetera.  In practice, it can be hard to know what is the fact and what is the dimension or what  should, at least, what column should go in the fact table and what should go in the dimension  table.  But with a reporting database, always design it so it makes it easy to make reports.  The important part in the star schemas is that you don't have relations between dimensions  because then queries can become complicated again and you can get absurd results when  you do an inner join and you should have done an outer join or the other way around.  You can add another level of dimensions so that the dimensions also have dimensions  and then you have a snowflake schema.  I don't know how common that is.  We don't have any use of that.  And one dimension you have is a bit special called the date dimension.  So it's worth talking about that.  And that has to do with date calculations being both tricky and slow and not searchable.  So you can, for example, not make a query on a date field saying that I want every Tuesday  from the third quarter out and nothing else.  So if you have a date dimension, which is a table that keeps all this information for  every date, pre-calculated.  So I don't know how much you can see of this.  Actually, these are the fields that we have in our date dimension.  We're probably not going to use a lot of them, but it's good to have there.  So it's information like, yeah, which month, which day, which day of year, which weekday  it is.  But also display information.  Like we have the field, or the column called quarter ordinal, which in this case contains  the string 3rd, which means that it's easy to make a table where it says first, second,  and third for the quarter when you're going to do a nice or chart rather.  The ID, as you can see, is an integer because it needs to be an integer for fast indexing.  But it should be an integer that is easily recognizable for humans.  So I'm using the ISO date format, but in integer form.  So this specific date, the 6th of September 2016, is then 20,160,906, which mathematically  makes absolutely no sense.  But you can easily recognize what date it is as a human.  The basic procedure for updating the reporting database is called extract transform load.  And that sounds obvious.  You extract the data, you transform it, and then you load it into the reporting database.  But it isn't actually obvious because there's extract load transform as well.  That's used for data lakes, where you stick all the company data unstructured into huge  databases, which you then do data mining on.  But those kind of things are for when you don't know exactly what it is you're looking  for.  In this case, we know what we want to do reports for.  We know what we want.  We want reports.  So then we should have instead what's called a data warehouse, and then you do extract  transform load.  Because the database should be easy to use.  So the data should be in a ready-transformed, easy-to-use date.  These three steps don't need to be done at the same place or at the same time or even  on the same computer.  And where you do them depends a lot on what you have for requirements.  Extracting can be done basically in two ways.  Either you do it in some sort of batch fashion.  Every common is, of course, to do a nightly batch extractions.  But those tools that exist, often called data integration tools, they can often do this  quite often.  They can do a batch extract maybe every 15 minutes, for example.  If you have very sensitive data in your database, you have customer-sensitive data, the extract  step should be careful of what is extracted.  So that's kind of a half-transformed step already.  If you don't have sensitive data, you can actually just dump all the tables out in raw  forms and use that.  But you don't want to have world-readable CSV files with sensitive data, such as if  there's any Catholics that has bought French ticklers.  And therefore, it's not allowed to be in multi-pital movies.  But the big batch extract is the most common, as I understand it.  And it's preferable if you can do a dump of all the data in a reasonable time frame.  But that means you need to be able to dump all your data out maybe once a night.  If you can do that, you can dump all the data out.  And you can drop the reporting database, massage the data, transform the data, and load it.  So you have a nice new fresh reporting database every day.  You can do that in those cases.  A lot of times doing such an extract is going to take too long.  Our extract currently takes around two hours.  But we're then hopefully just now in the end of a beta phase.  So hopefully we're going to get more than 10 times more of the customers.  And then extract would take 20 hours.  And it's going to be kind of hard to do it.  So we're not doing that.  Or we could then maybe do one of these extracts.  Well, in the end, we'll end up in a situation that we would have to run several extracts  at the same time.  So that gets to be a bit tricky.  But again, using batches means you have ready-made tools.  So that's a big benefit.  And if you can't dump everything out every night, you need to be able to detect changes.  It's called change data capture.  You have to figure out what it is you're going to extract.  And then if you don't extract everything, you risk that the data is inconsistent, that  it's not actually the same as in production database.  But on the other hand, you don't have to use up your main database so much.  And change detection can be done in four ways.  Time stamps so that everything that gets changed gets a time stamp.  And then you just do a query saying, I want to export everything after the last time step.  Sequence ID, if it thinks that gets added, or database logs.  So you can add most many databases, have the possibility to create a log of each record  in which each table that got modified.  So then you can use those to then extract the right data.  And the fourth way is to do an update on change.  So you might remember that I said they should do the update asynchronously.  They shouldn't actually update when the main application data updates.  Well, you can, but you have to do it asynchronously.  So you have to have some sort of event system.  And again, databases, many databases do have event triggers.  So if a record gets triggered, you can write something.  If a record gets modified or added, you can actually execute some sort of code.  Or your application might have events, but this typically means that you have to modify  your own application.  We are the ones that are modifying our application.  And if you're running Plone, then you can modify your own application and you can make  your own event hooks.  So that's fine.  But not all the time, not all the applications might have this.  And the benefit is you get near real time updates.  But you have to use some sort of asynchronous framework like Celery, which is what we're  doing.  So you get the information that something has changed and you just tell the asynchronous  framework that this has changed.  And then in the background, things get updated and exported.  The transform step means then that you need to take all this data that you have extracted  and make something that you can import into the reporting database.  Worst case, you might actually need to load all the data that you have from a table or  maybe two tables and then somehow do magic on that to get the data out.  And that can be so much that you can't even keep the data in memory, which means you have  to create a temporary database and do that for there, do queries from that.  But that's kind of unusual.  I think it's pretty unusual.  Most cases, you can do the transform record by record.  So you can do a simple script to transform the data and then maybe read in comma separated  value files and then print them out again.  And then you have the data that you want.  And then you can load it into the database.  This is so trivial that you don't actually really have to separate the transform and  load.  You don't really have a clear separation between the transform and load because once you have  the data, it's just a question of writing it to the SQL database.  And ta-da!  You have a reporting database and you can make reports.  But for that, you need some sort of reporting tool.  We have looked at a bunch of reporting tools and this is my opinions of the ones that we  have looked at.  But we have only looked at open source reporting tools because we're a bit fanatic and I am  about open source.  So we haven't looked at any commercial ones.  And almost all of the open source ones that exist are bad in various ways.  They are definitely not really designed to be used by normal end users.  And quite often you need to write your own SQL.  But for the first one, you don't.  LibreOfficeBase is the only open source tool that is aimed at normal mortals and it has  this query editor where you actually can make SQL queries without typing any SQL.  It's pretty good.  Is it use?  The reports, however, made in LibreOfficeWriter and it's by default horribly ugly.  You can of course theme it as you want because it's LibreOffice after all, but LibreOffice  is not that easy to make templates in.  And I couldn't figure out how to insert a chart, which is something you want.  So maybe you have to use then LibreOfficeCalc to make the charts, but what you can do also  with this.  But often you sometimes you maybe want to have the same in both reports.  You want both a chart and a table and I can do that.  A power user could probably use LibreOffice.  Then we have BIRT, which is a plug-in to Eclipse.  And if you think that it's strange to write a graphical user interface tool as a plug-in  to a code editor, then we can only blame Emacs because they started it.  So yeah, it's a plug-in to Eclipse.  It's kind of slow, it's kind of weird partly because it's part of Eclipse.  So it has a lot of menu items and things that makes no sense and has nothing to do with  reporting.  But it is usable and their default reports are prettier than LibreOffice.  But here on the other hand, you have absolutely no help in writing SQL queries.  You have to write them by hand.  The last tool that we have looked at, it's Pentaho.  And Pentaho comes in two versions.  It's a community edition, which is open source and free.  And an enterprise edition, which is so expensive that they don't even tell you how expensive  it is.  Which if you know from shoe shops and stuff, if you have to ask how much it costs, you  can't afford it.  So that's the question.  So Pentaho Enterprise Edition is only really interesting for enterprises who have basically  infinite amounts of money.  But Community Edition is pretty good and the Enterprise Edition doesn't seem to have that  many more features.  The Enterprise Edition has a JavaScript-based report generator where you can create your  own reports, which is a little bit more usable than the one that is written in Java, that  is the desktop one.  But it's also kind of almost a little bit more strange.  So it takes a while to get into that one as well.  So it might not be worth it.  But Pentaho has a lot of other cool features.  It's not only a report generator, which is what you're seeing here.  It also has data integration tools.  And so it's the whole thing for doing all of the reporting database stuff that you need  to do.  And the Pentaho Report Designer is maybe about as quirky as Birth is or Burt.  I don't know what it's called actually.  But quirky in a different way.  The theming is probably incomprehensible unless you're already used to CSS.  The theming isn't CSS though, but it works in very much the same way as CSS.  And that's probably because this can generate HTML reports as well.  So they can transform your theming into CSS, which is what they're doing in the background.  So you theme things by having a long list of themes where you name actually what kind  of field and in which place it is and that kind of stuff.  And that's how you add theming.  It also has a nice benefit because it has what it's called a business analytics server,  which as I understand it runs the data integration that we're not using.  And it's the bit that will run every 15 minutes or once a day or once a week, the batch export  and transform and load stuff.  But it also has a feature that you can publish reports to this database server.  And then anybody with the password can go to that server in their browser and run the  reports, which is very nice.  It has a GUI query designer like LibreOffice, but it's not as good.  So you end up having to type SQL anyway.  I was going to make a slide here on libraries and frameworks in Python that can help you  do all these things.  But after looking for it, I can only find report lab where you can take your data and  make nice PDFs.  So if you want to do the whole batch thing, including the extract, transform and load  and generating the reports, all batch during the night report lab is a good thing.  You can do it.  You can even then mail the reports to your boss when the report has run finished at 8.15  in the morning.  And the boss will think that you're awake.  If you know of any libraries and frameworks that can do reporting, tell me and I'll add  it to the slide.  So how do we then do this as shoebox?  Well, we do it the hardest way we could think of because we have very unusual requirements  or had, I should say.  So our solution is unusual.  We wanted near real-time data because we wanted this to be partly customer-facing because  we had some reports, essentially, or data that were essentially reports that we needed  to show to customers through our main application.  And we wanted to offload that to a reporting database.  But since this customer data is super sensitive, it meant we needed one database per customer  that added some problems.  And we also then liked that was not a hard requirement, but we definitely liked that  you could run the reports as needed.  And this means that we ended up doing the update on change, as I say, that we have,  our updates are done asynchronously with salary in the background instead of by batch.  And we needed one database per customer, although that requirement has gone.  We're not doing that anymore.  We decided to use Pentau.  What helped us is that although we have high requirements, we also have an infrastructure  that could easily handle that because we only have one source for data.  We have two different databases, but it's just one application.  So we don't need to merge data from different databases.  And that application is written by us, so we can change it as if we like.  And we already have events because this is built on the SOAP tool kit.  So we're essentially using SOAP 3 or large parts of it.  So we have there's modification events for everything already.  So that was easy.  And we already had salary set up to do other asynchronous tasks.  So all we needed to do was to add a bunch of salary tasks to export the right data and  then hook in events.  To call these salary tasks.  Now that was harder than it sounds, but we could do it.  So our extract step looks like this.  We have the events, normal SOAP events stuff, where we send just information to salary of  what changed and when, what time stamp this happened, what time it has happened.  And then you collect the data because we have super sensitive data.  Because we can't just dump the objects directly out of the database.  We have to choose what we extract.  And then we make a REST call with the requests library.  And that REST call is picked up by a server we wrote with Flask and SQL Alchemy and PostQuestQL  that has a REST API that Stefan fixed and implemented.  So it will then just do some minor transforming of the data and it sticks it into the PostQuest  star schemas.  We use Pentaho as I mentioned and a report with a chart looks like this.  There you can see how many incorporations that has been done per month.  And you can see that there's one in 2013 and that's the test data that comes automatically  when you load a test data for development environment.  And then we've got one incorporation, which is the yellow one here in August 2016, which  is when I made the chart.  So it's not very interesting, but it looks good.  And it's themed to use the shoebox colors and the shoebox logo.  And here's the column or table report.  And here you can see also that up there you can select which company you should run the  report on.  So this is another feature of the Pentaho reports.  You can publish them like this and you can actually have input fields.  So you can let whoever runs the report say which company and which time frame you should  use and things like this.  And here is one example of the star schema.  So we see we have here in down in the corner, we have the date dimension.  And the fact here is facts about work items, which is a part of the workflow.  So this fact table is what we're querying on when we query the table we saw before, which  tells you which workflow step people have gotten stuck on.  So you can see who is stuck on which workflow step.  We use that for reports.  And here's another one.  And one thing you can see here is that we again used the date dimension at the corner  this time and we actually use several of the other dimensions again.  And in the blog posts and papers I found about star schemas and reporting databases, nobody  mentioned this.  So and definitely nobody said that you can't do this.  So we're actually reusing the dimension.  So our schema really looks like this.  So it's much more complicated because this is five star scrimas that are overlapped on  each other.  But because each time you make a report, you only care about one fact table and the dimension  table is connected to that fact table, it becomes a star schema and each query is there.  They're easy, every query, even though this schema looks horribly complex.  So I think you shouldn't really view it as one schema, even though we are reusing the  dimension tables.  We also have things like both an entity fact table and an entity dimension table.  So when we do queries on entities, that is companies like the first chart on how many  has been onboarded and incorporated, then we do a query on the entity fact table.  Otherwise you have the entity dimension table, which are just then to display the company  name basically in reports.  And yeah, I forgot what I was going to say about that.  But yeah, it looks a bit messy, but it really isn't because you should look at it like this,  one fact table at a time.  So we haven't had any problems so far with reusing the dimension tables.  So yeah.  So you have the ID from the dimension table and the fact table, for example entities,  if you get in sync?  Well, no.  Well, yeah, we have internal IDs and those are in sync, but they come from the application  database.  But then you can also have just a key column and that's not in sync.  That doesn't matter because you'll never query from the entity.  If you have the entity fact, the information you have about the entity is already in there.  But you could use the entity dimension table in the entity fact too.  If you want to, if you want to make it a little bit more normalized, then they would be sick.  Yes?  You're not expiring your dimensions then?  No, no we're not.  So because you can't do that if you're expiring dimensions, if you have dimensional changing  based on facts, then you can't share the dimensions.  All right.  Yeah, well, yes.  I mean, the thing is if we do have dimensions changing based on facts, but that information  always comes from the production database again, which means that it should be updated.  That means that then something, for example, if the name of the entity has changed, then  the name of the entity has changed.  So then we change the entity dimension for that.  So if you do a total drop in replace, you can do that.  If you wanted to look at historical analysis of a dimensional change, you couldn't do  that.  No, no, right.  So we can't extract information.  If we would extract, we make a report on 2000, in 2013, and the company name has changed  we get the new company name.  That would be the reason for slowly changing dimensions.  Yeah, yeah.  Yeah, and the strategy of that is usually to just append on the dimension tables.  Right.  Yeah.  Which we don't have any for.  No.  So this is an example of a query.  This is the one you saw about the work steps where you stuck on the work steps.  So the queries are very simple.  You just select all the data you want out from then the work out in fact joined on the dimensions.  And the benefit here is that for each of these five star schemas we have, this join bit,  the from bit is always going to be the same.  So you can always, you can just cut and paste it.  And then I filter out what I want.  And here you see something that's not SQL in the bottom there, which is why actually  the nice little syntax highlighters say that this is wrong.  And that's because this is the field you could select.  So it gets stuck in there.  Some problems we had.  And here you can see that it's a lot of information here on this slide.  And this is because I ran out of time.  So I didn't have time to make lots of slides.  But some information, some problems we've had is for example, how do you know if data  is missing?  Well you don't because we don't do a complete drop and replace.  We don't really know if data is missing.  We check how many entities we have in the application database and in the reporting  database to see if somebody is missing.  But we do not do queries on each of these entities to see that all the documents and  everything is there.  Because doing such a query would take about a long time to exporting the data.  But we can resynchronize everything, of course.  So that was one of the requirements.  You should always be able to rebuild the data from scratch.  So we can do that.  And synchronous updates slow down the app.  So we have asynchronous updates as we said, but they can come in the wrong order.  This was a thing we also didn't think about from the start.  Because salary can pick up the tasks in whatever order salary feels like.  And this is why we added, so we have timestamps.  So the event will start the salary task with saying what changed and when it changed.  That means that our Flask server, when it gets the information, if the information is  actually older than what we already have, we just ignore it.  The only really big problem we had, that problem that actually took down our production  servers is that the requests library is a little bit too good, but not quite as good  as I thought.  I knew that URL lib doesn't have timeouts.  But requests lib is so good and easy to use, so I didn't think of that when it came to  the requests library.  Everything just works, right?  It turns out the timeouts is actually not in any of these libraries.  The timeout is the socket library, that said.  So in fact, the requests also by default do not have a timeout, which meant that our  salary workers slowly got standing and just waiting for the reporting database after we  got connection problems in the reporting database and it stopped answering properly to requests.  So that was an easy fix, but yeah, that's a little warning.  We wanted multi-threaded server for speed, but when we implemented that, we got massive  write conflicts that I didn't get locally.  That turns out because the conflict strategy on Postgres SQL by default is one that is  very nice and you basically don't get conflict errors because everything just waits for  each other.  So you don't get conflict errors, but for reasons I don't actually remember, the people  who know databases better said we cannot use that in production and you should not use  that in production.  So we had another one and there you can get conflict errors and we got massive conflict  errors.  So we had to implement that, we had to fix that by implementing retries.  So we now implemented retries both in the database server.  So if you get a conflict error there, we just try again three times, we try.  And we actually also on the application server and have retries.  So if you get any sort of error that is not an internal server error, no, right, no, any  kind of error except 400 errors of some sort, which because a 400 error basically tells  you that you made a wrong request.  So there's no point in retrying that.  But all other errors including connection errors and internal server errors, we just  retry because an internal server error is quite likely a right conflict.  That means we now have up to nine right conflicts and the last sync that I did of all the data  didn't have one single error message in any logs that I could find.  So I think we got rid of all the right conflicts.  I hope.  That's all I have questions.  The slides are available.  You only need to remember slides.colliberty.com.  Colliberty is my old company that I don't have anymore, but I still have to remain so  I stick everything up there.  So slides.colliberty.com and then you get a list of basically all conferences I've ever  presented on and you have the slides there.  Okay, no questions.  There were some questions during the talk, which is okay, but nothing left.  Good.  Then I'm done.  Thank you.  Thank you.  Thank you.  Okay.
Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_0_71.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_72_167.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_168_947.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_948_1079.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_1080_1235.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_1236_1475.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_1476_1535.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_1536_1799.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_1800_2075.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_2076_3311.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_3312_3815.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_3816_3863.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_3864_4427.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_4428_4511.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_4512_4583.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_4584_5615.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_5616_6287.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_6288_6443.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_6444_6491.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_6492_8531.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_8532_9095.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_9096_10703.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_10704_12011.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_12012_13079.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_13080_13619.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_13620_13667.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_13668_14339.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_14340_19211.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_19212_19739.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_19740_20519.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_20520_20879.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_20880_22955.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_22956_23675.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_23676_24683.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_24684_26639.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_26640_28151.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_28152_28247.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_28248_28379.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_28380_28643.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_28644_31343.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_31344_32555.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_32556_37331.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_37332_38471.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_38472_40055.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_40056_40655.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_40656_42095.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_42096_42863.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_42864_44591.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_44592_45503.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_45504_46391.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_46392_47123.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_47124_49007.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_49008_49115.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_49116_53519.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_53520_60107.png Day_1__Reporting_Databases__by_Lennart_Regebro_nLZHUquzTQ4_60108_61020.png