John
Chris Adamson defines a dimensional model as “…a business process in terms of measurement and context”. One of the misconceptions of dimensional modeling is that it is a database design, which it is not. A database design is something like a Star, Snowflake, or Cube. A couple of reasons for a dimension model is to make it easier to answer business questions and the performance coming from joining tables. For instance, in an entity relationship (ER) model, you may have to join through multiple relational tables to get information; whereas in a dimension model, it could be as simple as a single join.
There are two parts of a dimension model – the dimensions and the facts. When developed in a relational database management system (RDBMS), it is called a star schema. When developed in a multidimensional database (MDB), it is known as a cube.
The dimensions describe objects and its context – often in business terms like a Customer or a Product (as depicted in the first image). These tables should have as much information in them as possible. Contrary to the entity-relational mindset, it is okay to have redundant data for each row like adding the date of birth and the age. By doing so, it creates consistency and makes it easier to get needed information. Dimensions should also have a surrogate key for each table – a single column with a unique identifier – which should be referenced in the fact tables. The primary keys from the source system are now referred to as natural keys.
The facts are the metrics that are being generated by dimensions (as depicted in the first image). Fact tables are used to answer business questions such as “What is the ratio of customers to products being purchased?” These tables need to be at the lowest grain possible so it makes it easier to ‘slice-and-dice- the information multiple ways during reporting. These will have foreign keys to all surrogate keys in the dimensions that apply to it as well.
This is just the very tip of the iceberg when it comes to dimensional modeling. The uses and the patterns relating to data marts are all great things to learn and also beneficial to a company. Stay tuned for more overviews.
