Dimensional Model and its Elements
A data warehouse (DW or DWH), is a system used for reporting and → data analysis. It is a combination of data sets and relations between them, which form a dimensional model.
A dimensional model is a data structure technique designed to read, summarize, analyze numeric information like counts, durations etc. in a data warehouse. Below we describe standard dimensional model elements and terms.
A fact table contains business process events with appropriate measures, and foreign keys, which refer to the dimension tables.
The grain of the fact table is the finest level of detail that is implied when the fact and dimension tables are joined. For example, the granularity of Task fact table is a finished Call, IM or Email. The call is referenced by started date and time and has its type dimensions and duration measures.
Fact Measure (Measure)
Measures are numerical values that mathematical functions work on.
E.g. in Task fact table there is ConnectedTimeMs field, which is a measure because you can find out a total or average of its values.
A dimension provides the context surrounding a business process event. A dimension is a structure that categorizes facts in order to enable users to answer business questions.
E.g. in the Call Center business process, for the Task fact table, dimensions would be
- Who – Customer (caller Sip)
- Where – Service (Service Sip)
- When – Started Date and Started Time
- What – Task Type (the type of the Task, e.g. Inbound Service or Inbound Direct, etc.)
In other words, a dimension is a window to view information in the facts.
Dimension Attribute (Attribute)
Attributes are the various characteristics of a dimension.
E.g. in the Agent dimension, the attributes can be
- First Name
- Last Name
- Display Name
Attributes are used to search, filter, or classify facts. Dimension tables contain attributes.
One record in Dimension table with the set of attributes and a key make one dimension member. All members make up the dimension.
Role play dimensions
If the same dimension is used more than once with different names in the cube then it is called the role-playing dimension.
E.g., Organization Unit dimension has multiple references in Task fact table: to Service Organization Unit, First Accepted Agent Organization Unit.