Designing steps of a data WareHouse

Before going to explain the designing steps of a Data WareHouse, let me explain what is meant by a Data Warehouse and its Usage?

Data WareHouse plays a major role in the current IT world.   Designing and maintaining this data warehouse is crucial task now – a – days. So this data  WareHouse must be designed effectively, for the instant query response of the database. The user will use a particular database if and only it provides an instant and continuous report of the query.  Designing of Data WareHouse is not as a simple as designing a database. For designing a data warehouse, it should follow certain designing steps of a data warehouse.  Now, in this article ill explain you the designing steps of a data  WareHouse.

Know more about databases and Data wareHouse from OnlineITGuru through MSBI Online Course.

The designing steps of a data ware  House typically contain three fragments. The guidelines discussed below were regarding the non –functional requirements like performance, storage, and usage of the system.  This allows the application of different styles and techniques generating Data wareHouse logical  Schema following the designer approach.  These guidelines were basically categorized into three ways. Let me explain step by step in detail.

Aggregate Materialization :

Before going to What is Aggregate Materialization, I would like to introduce  Fact tables and what does it mean?

A fact table is a central table in the star schema of a data warehouse. It stores the quantitative information for the analysis and is often denormalized.

During the conceptual design phase, the analyst identifies the facts that were related to the business which leads to the implementation of Fact tables at logical design. These fact tables can be stored with different degrees of details like maximum tables and aggregates tables. We provide a set of dimensions to the fact that confirm the fact. this provides the desired degree of fact to materialize it.

Generally We define this structure as a cube which allows the designer to declare the degree of materialization of each fact. This cube sometimes doesn’t have any dimensions, at that time it represents only crossing between the dimensions.

A cube  basically consists of 4 tuples

Where Cname refers to the Cube name that identifies it

                 R is a conceptual  Schema of the  Fact

                 L is the subset of levels of fact dimensions and finally

                M is the optional measure that can be either the element of  L (or) null.

Now ill take you to the next design step of Data wareHouse through the designing steps of a data  WareHouse.

Horizontal Fragmentation :

A Data Warehouse (or) a database is said to be more effective if it has a  high effective Query performance.The user will be attractewd only to the Query efficient and effective performance for an end-user query. Simultaneously A cube can be represented in multiple tables depending upon the degree of materialization. To horizontally fragment a table, a relational table needs to fragmented to several tables, As a matter of fact with the same structure and distribute the tuples between them.

In data ware Hosue contact, properties were necessary to considered but not necessary to verified. In the same fashion A data warehouse not consider regarding the redundancy if the fragmented table a joint table.

Let me walk Into the next step in designing steps of a data  WareHouse.

 Vertical fragmentation :

Before going to explain the concept of vertical  Fragmentation, let me explain to you what meant by  Normalization

Normalization simply defined as a organizing the data in the database.

In this phase, the designer has a  possibility regarding the level of normalization that he wants to obtain in a relational database for each dimension. Moreover, different users prefer different types of schemas, one user may prefer the Star schema whereas the other user may prefer snowflake schema. Finally, these decisions taken globally regarding the schema to applied for each dimension. 

Eventually If the designer follows all the above-mentioned steps sequentially and correctly,  For instance the person going a design good Data Base.

Hopefully, my dear readers, you got an idea regarding the designing steps  of a Data Warehouse

Recommended Audience :

 Software developers

ETL  developers

Project Managers

Team Lead’s

Prerequisites:

It's better to have a fundamental knowledge of keeping up and examining them for implementation. No Programming abilities 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. Checkout in OnlineITGuru now  MSBI online training