Data Warehouse - Why do you need one?

The word “Data Warehouse” sounds quite exciting! It suggests something complicated, a kind of warehouse or collection of data. In fact a data warehouse is a database just like all the other “ordinary databases”. People call it a “warehouse” because you keep more in it than in an ordinary database.

What is a database?

A database is a kind of container in which you can keep data. A database consists of, among other things, tables, and those are also the most important parts of a database. For convenience, compare a table with an Excel sheet. A table also has rows (records) and columns. Say you have a database with personnel data. It will have a table with the staff. This table holds all employees of a company. Every employee is a record in the employee table with a number of properties, the column values. Think of name, address, phone number and gender.

All these tables together make up the database of the system. The tables are all connected to each other with keys, so you can trace what belongs together. You will not store all information in a single table. You will normalize the database, which means you split it into several tables. Usually the 3rd normal form is used for this. If you want to know more about designing databases, the best thing to do is buy this book or this book about modelling and SQL.

What is a data warehouse?

A data warehouse is also a database. The difference from an ordinary database is the architecture and the way it stores data. In a personnel system you keep only one record/row for every employee with the data of that employee. In a data warehouse you can store several versions of the employee. When an employee moves house, you may want to keep the old address. A data warehouse is built specifically to keep this kind of change. Also, the data model (the way you design tables and relations) is designed specifically to handle historical data, or to report information fast / store it simply. You can also design the data model of a data warehouse in 3rd normal form, but this type of model is not well suited for building fast reports. Usually people choose to put an extra database “behind” the data warehouse in the form of a [“star schema”](/product/sterren-en-dimensies-harm-lek/ “"). This is also an ordinary database, only designed specifically for building fast reports. In a star schema you can store data redundantly (twice), or store certain calculations in advance so reports load faster. The best-known design for this is the Kimball model.

Which architecture do you choose for a data warehouse

Over the years several methods (architectures) have emerged for designing a data warehouse / business intelligence solution. Which method you use in the end depends on what the company needs. A classic method for designing a data warehouse is the Kimball Method. I advise you to buy the Kimball book and read it. It has many practical examples of how you can model data. The focus is on reporting information to the end user as simply as possible, which is what it is all about in the end. If your English is not good, books like this are hard to read. You could consider taking a free trial lesson or a short course in English for beginners :-) Another good book to start with is [“Sterren en Dimensies” by Harm van der Lek.](/product/sterren-en-dimensies-harm-lek/ “") The book is from 2000, but all the information still applies, and it is written in Dutch, something you do not see very often in technical books.

It is 2016 now, and a lot is being written about “Data Vault”. This is also a way to model a data warehouse. Data vault aims to keep all changes, under the banner of “auditability and traceability”. Companies that must comply with the Sarbanes-Oxley Act will often use this method. I wonder if this is the right modelling technique for most companies. I think most companies have no requirement to keep all changes from their systems. I also find the data model very extensive: you quickly get hundreds of tables and load processes. Every table (entity) is split into hubs, links and satellites. It is interesting material, though. I hope to do an interesting BI assignment one day, so I can learn more about data vault and also apply it in practice.

If you want to learn more about data vault, I advise you to read articles and books by Dan Linstedt. He is the inventor of Data Vault. Dan is also on LinkedIn, and he always responds to questions if you have any. Dan has also written a good book with all the tips and tricks about Data Vault.

Do I need a data warehouse?

A good question to ask yourself is whether you need a data warehouse. There is no single answer to this question. It depends on what you want to achieve. If your company wants to present information to employees, a data warehouse is not always necessary. With Microsoft Reporting Services (free with SQL Server if you already have it, I love it ) you can develop nice and powerful reports without a data warehouse. You write a SQL query directly against your system, so the data comes “real-time” from your system. In practice this works fine when not too many people open reports at the same time and the reports are not too “complicated”.

It is a different story when you:

• must fetch and report large amounts of data
• must run complex calculations in real time
• want many users to open reports at the same time
• want to put a lot of data side by side over time
• want to integrate or compare data from several databases
• want to keep changes over time
• must be SOX compliant.

If your company has one of the challenges above, you can hardly avoid setting up a data warehouse. Do not be too scared of the word “Data Warehouse”! As I described above, it is an ordinary database. The challenge is usually in agreeing on definitions and integrating data. You will also have to load the data into the database. This does not have to be very complicated or expensive with the tooling Microsoft ships free with its database, SSIS (Integration Services). If you go for another tool, it often costs you a lot of money.