Enterprise data warehousing is the framework that integrates diverse data stores and various models to achieve organization goals and user requirements. It acts as a single point of contact for any type of current or historical information leading to logical expertise and answers to complex queries.

Designing an apt data warehouse is a significant task for any organization, since it refers to the central repository of reliable data. One of the prime focus areas while designing a data warehouse is its database component, the dimension model. It is a consistent design practice which presents the data in a standard, insightful form which allows high performance access.

Every dimensional model consists of a table with a composite primary key, which is called the fact table and a set of smaller tables called dimension tables.

Dimensional model elements of a data warehouse design

data warehouse design

• Choose the schema model
There are two prevalent schema models being used in a dimensional data warehouse:

o Star Schema
It is a well known and simple type of data mart schema, where in the physical model represents the shape of a star and hence its name. It consists of a fact table in center, surrounded by dimensional table around in the shape of a star. The main highlight of this model is its effortless design with which it achieves quick results with the help of simplistic queries. A star schema mainly separates the data involved during the business processes as facts which carry the quantifiable information and the dimensions which describe the attributes associated with the fact information.

Easier queries, quick aggregations and simplified business methodology are few of the many benefits of using star schema in dimensional modelling. During the star schema design and implementation, the rules of normalizations are not applied and hence this are de-normalized schema.

o Snow flake Schema
It is on a similar line as a star schema, except that here, the design is normalized and the facts tables, which are surrounded by dimensions, are further related to other dimensions. The shape that it resembles is that of a snow flake. It mainly caters to convert complex dimensions into smaller and convenient units.

When there are elaborate dimensions and multiple levels of relationship in the design, a typical snowflake shape is seen. The normalization is primarily to remove the low cardinality attributes and form multiple tables.

• Identify the grain
Choosing the grain is an important aspect of designing a robust data warehouse. Here, the granularity of the fact tables is identified and so is the level of atomic details that we require from the data. Also, the initial candidates for dimensions and measures are culled out.

• Declare the dimension
A significant phase of dimension modelling – it focuses on categorizing the correct dimension for the requirement. A dimension can be explained as a preparation of data to collate business information.


• Choose the facts
Identification of facts is a pivotal phase in designing data warehouses. A fact is a centralized table enclosed by dimensions and has to match to the grain defined in the identification. Facts are located at the center of the star or snow flake schema surrounded by dimension tables. There are two types of columns involved, a measure used for data analysis based on which varied analytical reports are generated and a foreign key to dimensions.

A fact table has the capacity to store the various contents of the data warehouse and different measures such as additive, non-additive and semi-additive.

SPEC INDIA has been involved with diverse and a wide range of data warehousing and BI projects and has a pool of skilled expertise in these technologies. Kindly visit our website www.spec-india.com for further details and we would be glad to get in touch with you through lead@spec-india.com