A data warehouse design plays a crucial role ineffectiveness of the business process.  For a useful data warehouse we need to find out the business needs, analyze them and then construct a business analysis framework. Constructing a big data warehouse is synonymous to designing a big building with top-down owner approach, architect and bottom-up builder perspective each having its own viewpoint.

The data warehouse design is carried out using various data warehouse tools which provide functions such as schemas, metadata, reporting and planning and analysis tools to check the schema changes, change the refresh rates and time.

A data warehouse designing is a complex task as it required efficient skills to manage a business, technology and the entire program. The major skills required in designing a data warehouse are:

1) Business Skills:

Business skills such as how systems work, how data is managed and stored in the DW system. It considers the process of transferring data from the operation data source to DW using extractors. How the DW data is maintained and updated using refreshing software is taken care. Business skills also involve understanding and translating the business requirements in queries which can be fulfilled by the data warehouse.

2) Technology Skills:

Using technology skills, one can find out the trends and patterns, explore any anomalies and give recommendations based on the analysis. These skills help in describing facts from the historic information and making decisions using the quantitative information.

3) Management Skills:

These skills help in connecting with end-users, vendors or technology service providers for efficient designing of data warehouse in a cost-effective and timely way.

 

A data warehouse information system has different views based on user roles. Therefore while designing a data warehouse these views should be considered. The four different views considered during data warehouse design:

1) Top-down view:

It selects the needed information for designing data warehouse such as business needs.

2) Data source view:

It displays the manipulated and updated information by operational systems. The information can be viewed at various levels of details such as individual data tables and integrated data tables. Traditional data modeling techniques such ER relationships are applied to the data sources.

3) Data warehouse view:

The data warehouse view shows the fact tables and dimension tables. It represents the details about historical information present in the DW such as data source, timestamp and data, etc.

Business query view: The data displayed by the query entered by end-user in the DW is catered here.

 

How Are Data Warehouses Designed?

There are different designing approaches to build a data warehouse:

1) Top-down Approach:

This approach is used if all business needs are clearly understood and technology to be used is well known.  This approach focuses on overall design and planning.

2) Bottom-up Approach:

In this approach the technology part is first analyzed to find out its suitability. This approach starts with experiments and building samples to evaluate before making any commitments. Thus, it leads to less expensive approach and useful in early stage of business modeling.

3) Combined Approach:

In this approach, the organization builds a data warehouse using combined benefits of above two approaches. The strategic part of top-down and rapid implementation of a bottom-up approach is used in the combined approach.

 

Steps In Data Warehouse Design

A data warehouse design is a software engineering process. It follows the steps are below:

1. Planning
2. Requirement  and Problem Analysis
3. Warehouse design
4. Data Integration
5. Testing
6. Deployment of the module.

There are various approaches to software development in software engineering. These methods or approaches are also used in designing large data warehouses. The two major software development methods are:

Waterfall model:

Waterfall model performs each step in a systematic manner, completes it and then moves to the next step. Waterfall model approach is used for long term projects.

Spiral model:

It is a rapid and incremental model. This model is used during short and rapid releases. There is a short span of time between two releases.

The spiral model is considered as a good choice for data warehouse design as the time span between two releases is short, changes are can be made easily and latest technologies can be used easily.

 

Steps To Perform While Designing DW:

Following steps are performed while designing a DW.

1) Business Process:

A business process such as sales, inventory is chosen in this step. The business process consists of objects, collection of objects. Depending on the business line of focus, a data warehouse or an appropriate Data mart building is chosen.

2) Fact Tables:

In this step, the fundamental and atomic level of business process data to be represented in fact tables is chosen. The atomic level of data such as daily transactions etc. are shown in fact tables.

3) Dimensions:

In this step, the dimensions which will be applied to fact table’s records are chosen. Dimensions such as product, location, customer, etc. are chosen.

4) Measures:

In this step, the fact tables are populated using measures. Measures such as total_sales, total_products are chosen.

5) Data warehouse Implementation:

A data warehouse designing is a difficult task thus its goals should be clearly defined and achievable. Budget and time, data sources, department types and numbers to be considered are clearly defined here. The data warehouse implementation takes place using highly efficient data cube computation. As DW contains a huge volume of data and the response time demanded is in order of seconds thus the DW systems should support highly efficient data cube computation techniques. Some of these techniques are “Operator and Curse of Dimensionality” ,”Bitmap Index” ,”Join Index”.

6) Data warehouse deployment:

When the data warehouse is designing the next step to follow is data warehouse deployment. In this step, the installation, training and induction and upgrades and maintenance tasks are considered.

7) Data warehouse administration:

In this step, the data source synchronization, data refreshing, recovery planning, access controls, security measures, optimization of database, data warehouse growth and enhancements are considered. The enhancements in data warehouse are controlling the range of dimensions, the data warehouse size, resource budget, queries, etc.

In this step, the data source synchronization, data refreshing, recovery planning, access controls, security measures, optimization of database, data warehouse growth and enhancements are considered. The enhancements in data warehouse are controlling the range of dimensions, the data warehouse size, resource budget, queries, etc.

In the next tutorial, we will study in Detail the OLAP server architecture such as ROLAP, MOLAP, and HOLAP.

---

⇓ Subscribe Us ⇓

---

If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:

Enter your email address:

 

Check email in your inbox for confirmation to get latest updates Software Testing for free.

  Happy Testing!!!