In today’s world, data is increasing enormously. This data incrementation is in such a way that the amount of data that needs to be stored, cannot be even predicted. Because the data that was incremented till today, over the past 5 years is in an exponential manner. Maintaining this though the amount of data is a tough job this day. So we need to organize this data in a smarter manner for the faster retrieval of data.

Today, this organizing is also becoming a difficult job. So we need an architect who can who has a capability to give an outline regarding the where to store the particular amount of data. We a solution which provides the logical design of the data warehouse. The schema provides the solution to this problem. Today, in this article, let me explain you the types of schemas in the data warehouses.

Get the more information on data warehouses from OnlineITGuru through msbi online training.

The schema is the logical representation of a data warehouse which includes the collection of data warehouse objects, including tables, views, and indexes. Similar to databases, a data warehouse also requires the schemes. Usually, a database utilizes the relational model, while the data warehouses use the star, snowflakes, and fact constellation schema. Today, in different types of schemas in data warehouses, ill let you know each schema in detail

Types of schemas in data warehouses :

As explained above, schema plays a major role in organizing the data effectively. So let me explain you the first type of schema in data warehouses.

Star schema :

Each dimension in a star schema is represented in one dimension table. Each dimension has a set of attributes. Basically, a  fact table is placed at the center, which consists of several dimensions.Types of Schemas in Data warehouses

For example, consider the above image. The fact table contains several dimensions like time, item, branch, location etc. And each dimension in the fact table shows several different attributes. In these attributes, there will be some case, where the one same attribute will be repeated in different dimension tables. But this repetition of attributes leads to redundancy.

Snowflake  Schema :

Before going to explain you the star schema let me explain you what is meant by normalization?

Normalization is the process of converting the existing data into standard form by eliminating the redundancy, insertion, deletion and updation anomalies.Types of Schemas in Data warehouses

For example, the items in this table are split into two dimension tables. In the above case, consider the item and supplier table, the supplier key attribute will be in common in both the tables. So this attribute will share the data between two tables. Moreover, redundancy elimination will be achieved through this schema.

Fact Constellation schema :

This schema is also called as the Galaxy schema. This schema usually consists of several fact schemas. Moreover, each fact table consists of several fact tables. The greatest advantages of this schema are this that several dimension can be shared between these tables. For example, time, item, location can be shared between sales and shipping fact table.Types of Schemas in Data warehouses

So data architects usually select any on the above schema for organizing the data. The selection of schema will be decided by the architect. The greater the effective design, the faster retrieval of data will be achieved. Developers usually pick the database based on the retrieval time of the query. Moreover, they usually select the databases which have a low query execution time and efficient storage of data. Today, developers were struggling on retrieval time of data to reduce as much as possible. Because as days pass on, there will only increase in data. This incrementation of data will be in such a way that, there may be a case where the user needs to query on petabytes of data in a fraction of seconds

So get the real-time  organizing of data from the real time experts of OnlineITGuru through  msbi online training bangalore.

Recommended Audience :

Software developers

ETL  developers

Project Managers

Team Lead’s


It’s better to have a fundamental knowledge of keeping up and examining them for implementation. No Programming abilities were required to take in this course. It’s great to have knowledge of the utilization of Excel or the worksheets to understand the whole design of MSBI.

Drop Us A Query

100% Secure Payments. All major credit & debit cards accepted.