Loading a data warehouse requires that a mechanism be created for tracking which source system records have been processed and which are pending. ETL & ELT are very custom processes, but general design patterns are often repeated. In an ideal
There are many things about data warehousing that are subjective. The use of surrogate keys is clearly not one of them. Let’s start with a quick refresher.
Surrogate Key: A system generated value which uniquely identifies a record. Surrogates
When building a dimensional model it is critical that facts have accurate foreign keys pointing back to related dimensions. However, there are some situations where the fact record is known before the related dimension record. This situation is called a
Data sources come in all shapes and sizes. Some include all of the features needed to easily integrate into load processes. Others can cause all kinds of trouble during the initial design and on an ongoing basis. One of the
When creating a model to describe a process that has a clear beginning, end, and phases in between, the accumulated snapshot fact table may be a good fit. Ralph Kimball has a very nice summary of this option in his
Data warehousing closely resembles life in general. Just about when we feel like everything is going our way something unexpected happens. Before we know it we are on a completely different path working to regain that comfortable feeling. Luckily, there
When building SQL Server data warehouses should user-defined schemas be used? Should tables and views be prefixed? Or, maybe we should do both. After talking to a few people about this and doing a little research, one thing is clear.
Dimensions records are often derived from multiple sources. When these sources are in sync, it is a simple enough process to combine the sources to create a dimension load source that includes all required fields. When sources are not in
When a fact component attempts to load a record, a foreign key to a dimension lookup is performed. This lookup (join) may fail if the dimension does not have a record that meets the fk join definition. In this case
When using a merge statement to load a fact table it is important to ensure that the source component/s do not contain more than one record with the same composite foreign key. Often this can be resolved by performing an