| Dimensional data model is the most common design | | | | be Location Code, State, Country, Zip code. Further, |
| concept used by data warehouse designers to build | | | | dimension attributes contain one or more hierarchical |
| data warehousing systems. The data model design is | | | | relationships. |
| the underlying data model used by many of the | | | | If you are looking forward to building a data |
| commercial OLAP products available today in the | | | | warehouse for your organization, you should first |
| market. Some of the terms commonly used in this | | | | decide what your data warehouse will contain. |
| type of modeling are: Dimension- a category of | | | | Depending upon your organizational goals, you can |
| information (e.g. The time dimension); Attribute- a | | | | choose the type of dimensions that can best meet |
| unique level within a dimension (e.g. Month is an | | | | your requirements. For example, if you want to build |
| attribute in the time dimension); and Hierarchy- the | | | | a data warehouse that would contain monthly sales |
| specification of levels that represents relationship | | | | numbers across multiple store locations, across time |
| between different attributes within a dimension (e.g. | | | | and across products then your dimensions would be |
| Year → Quarter → Month → Day). | | | | Location, Time and Product. |
| Dimensional data model contains two types of tables. | | | | In designing data models for data warehouses or |
| They are: | | | | data marts the most commonly used schema types |
| Fact Table: Fact table in a dimensional data model | | | | are Star Schema and Snowflake Schema. |
| contains the measures of all interest, such | | | | Star Schema: In this type of schema design, a single |
| measurements or metrics or facts of business | | | | object or the fact table is placed in the middle and is |
| processes. Take the example of the sales amount of | | | | radially connected to other surrounding objects or |
| a business. The amount can be a monthly sales | | | | dimension tables like a star. Here, each dimension is |
| number or sales number for a day. This measure is | | | | represented as a single table and the primary key in |
| stored in the fact table with the appropriate | | | | each dimension table is related to a foreign key in the |
| granularity. For sales measures, a fact table generally | | | | fact table. A simple start schema consists of one |
| contains three columns: a date column, a store | | | | fact table and a complex star schema may contain |
| column and a sales amount column. Besides the | | | | more than one fact table. |
| measurements the table will also contain foreign keys | | | | Snowflake Schema: This type of schema design can |
| for the dimension tables. | | | | be called as an extension of the star schema. In this |
| Dimension Table: The dimension table in a dimensional | | | | design each point of the star or each dimension table |
| model represents the context of the measurements. | | | | contains more points. In other words, in a star |
| The context of measurements can also be | | | | schema each dimension is represented by a single |
| understood as the characteristics such as who, what, | | | | dimensional table, while in a snowflake schema that |
| where, when, how of a measurement (subject). For | | | | dimensional table is normalized into multiple lookup |
| example, in a business process Sales, the | | | | tables, each representing a level in the dimensional |
| characteristics of the 'monthly sales number' | | | | hierarchy. |
| measurement would be a Location (Where), Time | | | | Choosing a particular type of schema design depends |
| (When) and Product Sold (What). A dimension table | | | | on personal preference as well as business needs. So, |
| contains a number of dimension attributes or columns. | | | | it is up to you which one you choose among the two |
| In the Location dimension the various attributes can | | | | for your data warehouse project. |