In the environments, which are advanced and have grown over time, this is the most probable state. As per this topology, the Staging Area feeds into the Data Warehouse and the data warehouse feeds into the independent Data Marts.
This BI Architecture raises two questions:
The first question is on why to have additional layers of data marts, if Data Warehouse exists? There are many reasons for the same:
- A Data Mart already in use: If a data mart exists, and has been used extensively, this approach ensures a complete transparency from a user point of view.
- Reducing Network Traffic: In large organizations, you may not like to have the users across the globe to be accessing a central data Warehouse. Data Marts on local servers are created to allow a faster response with reduced WAN traffic.
- Security: Data Mart creates a physical barrier to deter the users to access the irrelevant information.
- Easy operation: For the power users it is important to be able to configure, browse and create views over the limited set of dimensions and measures useful for them.
The second question is the reverse of the first one. That is- what’s the need for Data Warehouse , when Data Marts exist? One can directly load the data from the staging database. The concept of foundation dimensions & measures and Dimensional Model can be implemented in the staging database level. The reasons for the relevance of Data Warehouse in this topology are as follows:
- A Data Warehouse provides quick access refresh for the data marts: A Loading process from staging takes greater amount of time. As Data Warehouse structure and layout is very similar to a Data Mart, this refresh takes lesser degree of time.
- Flexibility in creating of changing the Data Marts: In this topology, a Data Mart can be changed quite fast (unless it is introducing a dimension, measure OR an attribute which does not exist in Data Warehouse) as it has no impact on Data Warehouse design OR the ETL process. Loading direct from staging will impact the changes done to Data Mart design.
- A Data Warehouse may still be accessed for information outside of a Data Mart: Sometimes a data warehouse will be accessed for transaction level reports OR some ad-hoc analysis requirements, which don’t warrant the creation of a Data-Mart. Therefore the core purpose of Data Warehouse to serve as a enterprise system of record, still needs to be fulfilled.