Historical Staging Area HSA in a DWH project

In Data Warehouse projects there is always the question of which architecture works best. Every architecture has its own pros and cons. What I find important in an architecture is that it is flexible and scalable. In other words: it must be easy to adapt to new needs from the business, and it must be possible to extend it “endlessly” without limits. The Data Warehouse project I am working on now has a so-called “HSA” (Historical Staging Area). I think an HSA is useful in every Data Warehouse project. Below I describe some advantages of the HSA that I found in practice.

In an HSA you follow the source system, so to speak. Every night the HSA is updated and holds a copy of the source. The only difference from the source is that we keep all changes in a separate table. This lets us find every change ever made to the source table. These changes are stored in a separate table, the so-called HIS table. The latest state (1:1 copy of the source) is kept in a registration table, the REG table. The HIS table has a validity period.

{module Easy Adsense Content}

You may wonder what the big advantage of this is. Why would you want to keep all changes to a source system? The answer is simple. You can never predict which information requests will come up in the future. When you make your star schema, you have to decide right away for which fields you do or do not want to keep history (Type I / Type II ). With an HSA you can easily change this choice later, because you can build your dimensions and facts fully retroactively, since you still have the history. And what do you do when a TYPE II field is added to the dimension? You can now build it retroactively, with full history!

In short, the advantages are big. Another advantage is that the ETL that fills the HSA can be generated, because it is the same trick every time! At the moment we have scripts that generate and fill the HSA, made specially for SQL Server. So we can connect new sources to our HSA quickly. The nice thing is that we can already follow a source system that is not yet connected to the Data Warehouse, so we build up history in advance.

{module Easy Adsense Content}