This article covers the general concepts of data modelling in simple terms. A very brief overview of dimensions and measures is provided.
A dimensional database needs a dimensional data model. To make a dimensional data model simple to understand, it can be looked at as a cube of three or four dimensions where users can access a slice of the database with one or more of its dimensions. Illustration of a 3 dimensional model is shown below.
There are two main concepts in a dimensional database model: Dimensions and Measures.
Dimensions are entities that contain a list of attributes that describe an object. Each dimension that is identified for the data model gets implemented as a dimension table.
Dimension tables provide the context for understanding the facts. The context of a measure can be the answers to the questions who, what, where, when how of a measure. For example where did the daily sale took place (Location/Region dimension), or what was the product that was sold (Product dimension), or when did the sale happen (Time dimension). A dimension table contains an element and an attribute if appropriate.
Dimension Elements: A dimension can define multiple dimension elements for different levels of summation. For example, all the elements that relate to the structure of a sales organization might comprise one dimension
Dimension Attributes are the various columns in a dimension table. For example in the location dimension table, the attributes can be Location Code, State, country, Zip etc. These attributes are used in report labels, and query constraints like a where clause. For example where Country=’New Zealand’. The dimension attributes contain one or more hierarchical relationships.
An illustration of dimension elements and attributes is shown below.
Dimension Members are the list of possible values for the dimension itself. For example all the rows returned for the query Country=”New Zealand”. Here each row is considered as a member.
Dimension Hierarchies are used when you need to filter data. For example you have a need to filter data by region first and then followed by the entities. Here the region will be the higher level and the lower level will be the entities. In this case you can join two dimensions into one. This will allow you to create a drill through of the data. A hierarchy is a set of parent child relationships between attributes within a dimension. An illustration is shown below.
Measures are the data values like the daily product sales. Measures can be quantitative or qualitative. They are most of the time numeric, and additive. They can be aggregated and they are stored in fact tables.
Fact tables contain the measurements or metrics associated with a business process. For example, for a sales business process, the daily sale is an appropriate measure that can be stored in a fact table. In addition to these measures the other fields that can be stored in a fact table are foreign keys that link to the dimension table.
An example of a fact table is shown below: