Data warehouse architecture
A typical data warehouse architecture has different layers of Source, Staging, Data warehouse database and Business intelligence. Various activities happen in each layer.
DWH Architecture With Datamart
What is a Data mart?
Data Mart is for the specific subject area like Finance, HR or Project. Only the specific peoples whoever part of the subject area will get access to view the reports from the data mart. It will be built on top of data warehouse database.
Data warehouse and data mart:
DWH Architecture With Datamart
Need for Data mart:
- The data warehouse database contains integrated data for all business lines, for example, a banking data warehouse contains data for all saving, credit and loan accounts databases.
- The reporting access level will be given to a person who has authority or needs to see the comparison of data for all three types of accounts.
- Meanwhile, a loan account branch manager does not require to see the saving and credit card details, he wants to see only the past performance of loan account alone.
- In that case for his analysis, we need to apply data level security to protect saving and credit information’s data warehouse.
- At the same time, the number of end users across three accounts will access the same data warehouse, it will end up in poor performance.
- To avoid these issues, the separate database will be built on top of data warehouse, named as the data mart. The access will be given for respective business line resources not for everyone.
Source systems:
The source schema and table will be identified and data extraction process would happen with appropriate extraction rules.
Why is Staging Database required?
- To reduce the complexity of Job (It will be more complex when we move directly from Source to Target)
- To avoid the source database update.
- To perform any calculations.
- To perform data cleansing process as per business need.
- When the data has been corrupted in Target after the load, we can delete the corrupted data in Target database after that we can just load the unloaded/deleted data alone into Target from staging database.
Data warehouse Database:
The data from staging database will be loaded through ETL jobs along with different business rules.
