Background Traditionally, the Kimball methodology for data warehousing has advocated using integer surrogate keys to uniquely identify dimension records. This approach has been widely adopted due to its storage, indexing, and query performance efficiency. However, with the advent of Data Vault modeling, the use of hash keys as surrogate identifiers became popular. The rationale behind…
What is Data Warehouse Architecture Before you build a data warehouse, you must define some standards for how you and your team will design and structure your data warehouse system. A Data Warehouse Architecture describes how data is sourced, stored, processed, and accessed. The architecture can vary depending on the specific needs and scale of…
A Star Schema is a data modelling technique used to model the presentation layer of a Data Warehouse. It refers to the way Facts and Dimensions in the model are related. A Star Schema is organized around a central fact table that is related to its Dimension tables using foreign keys in the Fact table….
The Star and Snowflake schemas are two types of Dimensional modelling techniques used in Data Warehousing to organize and structure data for efficient querying and analysis. How do Star and Snowflake schemas differ, and which do we recommend? Read on to find out. Overview Below is the model for a Sales Fact Star Schema vs….
Many to Many relationships are defined in a Star Schema Dimensional model using a “Group” Dimension and a “Bridge” Fact. Take the example below: In the above example, an insurance company receives medical claims. The medical claims are the business event represented by the Fact table. Each medical claim can have more than one associated…
What exactly are Slowly Changing Dimensions, and why should you care about them? Many people find themselves asking this question. The term “Slowly Changing Dimension” is kind of odd. I mean, aren’t all Dimensions changing? And why does the speed at which the change matter? Never fear; this article will answer your questions about Slowly…
A Dimension table is one of the 3 key elements of dimensional modelling used to build a Data Warehouse. This article will give you an in-depth understanding of various Dimension table concepts. What is a Dimension Table? Conceptually, Dimension tables are database tables modelled to represent the business entities involved in business processes and events….
In the context of a data warehouse, a fact table represents a business process or event and contains the measures and metrics you want to analyze for that process or event. There are three types of facts corresponding to three kinds of business events. A Fact doesn’t exist in isolation. It is related to a…
Dimensional modeling is a data modeling technique used to model the presentation layer of a data warehouse. It focuses on delivering simplicity and query performance for the end user. It allows users to easily understand and navigate the data available for reporting and ad hoc analysis. A dimensional model supports high-performance aggregated queries and performs…
A Data Warehouse is a database that supports enterprise reporting and analysis. A well-designed data warehouse uses accepted data modelling and management techniques to provide an integrated data source that makes it easy to build reports and analyses. A data warehouse should exhibit the following four characteristics: At the heart of a data warehouse is…
