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.
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 <Cname, R, L, M )
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. A cube can be represented in multiple tables depending upon the degree of materialization. To horizontally fragment a table, a relational table needs to be fragmented to several tables, with the same structure and distribute the tuples between them.
In data ware Hosue contact, properties were necessary to be considered but not necessary to be verified. A data warehouse will not consider regarding the redundancy if the fragmented table is 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 is meant by Normalization
Normalization is 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 were taken globally regarding the schema to be applied for each dimension.
If the designer follows all the above-mentioned steps sequentially and correctly, the person is a going a design good Data Base.
Hopefully, my dear readers, you got an idea regarding the designing steps of a Data Warehouse
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. Checkout in OnlineITGuru now MSBI online training Bangalore.