What is a Confirmed dimension?
As we know that, a dimension table stores non-quantifying data. Dimensions will be used as a viewpoint in the analysis process. A data warehouse database contains integrated fact and dimension tables for different business lines.
In some cases based on business need, a dimension can be referred in multiple fact tables of different business lines. This type of dimension is called confirmed dimension.
Sometimes, two dimension tables have the same structure and with the same data. In that case, both dimensions are called as confirmed dimensions.
Advantages of having this dimension:
Only one data load
Data load happens only for one dimension table. It is not required to load the same data into different databases/data marts.Data consistency
It keeps the same data across all business lines. If any changes happen all no need worry about by individual business line users.Data redundancy
As we are keeping data in a single, centralized table, the duplicate data won’t be available.Memory consumption
If we keep the same data in individual tables for every business line, it consumes huge memory. But the process of using confirmed dimension table reduces the memory consumption also.
Examples for this type dimension:
In the banking sector, typically the data warehouse database contains all three major business lines such as Saving, Credit and Loan accounts.
For every data analysis, customer information is required. The bank stores all customer data in a single table called “customer” dimension.
This “customer” dimension reference to fact tables of Saving, Credit and Loan account data marts. So that all three data mart reports point the same dimension for customer details.
Date dimension is an another example. Date dimension contains day, month and year along with a surrogate key.
All business area fact tables can have the reference with this dimension wherever calendar related report requires.
