Functionality of Data Warehouses and Building a Data Warehouse

Data warehouses exist to facilitate complex, data-intensive, and frequent adhoc queries. The data warehouse access component supports enhanced spreadsheet functionality, efficient query processing, structured and adhoc queries, data mining, and materialized views. These offer pre-programmed functionalities such as :
- Roll-up : Data is summarized with increasing generalization.
- Drill-down : Increasing levels of details are revealed.
- Pivot : Cross tabulation is performed.
- Slice and dice : Performing projection operations on the dimensions.
- Sorting : Data is sorted by ordinal value.
- Selection : Data is available by value or range.
- Derived attributes : Attributes are computed by operations on stored and derived values.

BUILDING A DATA WAREHOUSE :
In constructing a data warehouse, builders should take a broad view of the anticipated use of the data warehouse. Acquisition of data for the warehouse involves the following steps :
- The data must be extracted from different sources.
- Data must be formatted for consistency within the data warehouse. Names, meanings, and domains of data from unrelated sources must be reconciled.
- Data must be cleaned to ensure validity.
- The data must be fitted into the data model of the data warehouse.
- The data must be loaded into the warehouse.
- How up-to-date must be data be ?
* Can the warehouse go off-line, and for how long.
* What are the data interdependencies ?
* What is the storage availability ?
* What are the distribution requirements ?
* What is the loading time ?
Data warehouses must also be designed with full consideration of the environment in which they will reside. Important design considerations include the following :
- Usage projections.
- The fit of the data model.
- Characteristics of available sources.
- Design of the metadata component.
- Modular component design.
- Design for manageability and change.
- Consideration of distributed and parallel architecture.

Introduction To Data Warehousing

A data warehouse is a type of computer database that is responsible for collecting and storing the information of a particular organization. The goal of using a data warehouse is to have an efficient way of managing information and analyzing data.
In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

Despite the fact that data warehouses can be designed in a number of different ways, they all share a number of important concepts.
A data warehouse is a :
- Subject-oriented : This means that the information that is in the data warehouse is stored in a way that allows it to be connected to objects or events which occur in reality.
- Integrated : Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated.
- Time-variant : A time variant will allow changes in the information to be monitored and recorded over time.
- Non-volatile : This means that it cannot be deleted, and must be held to be analyzed in the future. All of the programs that are used by a particular institution will be stored in the data warehouse, and it will be integrated together.



CHARACTERISTICS OF DATA WAREHOUSES :
- Multi-dimensional conceptual view.
- Generic dimensionality.
- Unlimited dimensions and aggression levels.
- Unrestricted cross-dimensional operations.
- Dynamic sparse matrix handling.
- Client-server architecture.
- Multi-user support.
- Accessibility.
- Transparency.
- Intuitive data manipulation.
- Consistent reporting performance.
- Flexible reporting.