Sometimes a data warehouse will include two dimension tables which are so large that when they are joined by the business intelligence reporting tool, the join of those tables consumes too many resources and time to return a result set in a satisfactory time duration. These dimensions are too big to join. When this happens, the most common solution is to prejoin the two large dimension tables into one denormalized dimension table. How will that join happen? The extract, transform and load application will make it happen. The ETL application has a simple job – join two dimension tables that are too big to join, every single day, and do so before data warehouse customers arrive in the morning.

In a traditional changed data capture an ETL application realizes the complete universe of values which are in effect in the system of record at that moment – set A. The universe of values previously in the data warehouse – set B – is compared to set A. The resulting set of changes are then used to update the data warehouse. The advantage of this method is completeness. Because it includes the universe of values in set A and set B no values are excluded. The disadvantage is the consumption of resources necessary to create and manage two universes of values from a denormalized dimension which is built from two dimension tables which are too large to join.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access