87. Data warehousing

Data warehouse is a subject-oriented database where data are associated to a single organizational process (such as Product Sales), often called entity. A Data warehouse is integrated with various source databases (part of ERPs or other systems) and data warehousing has enormous strategic implications for business intelligence. Our consultants will model a powerful knowledge management tool to organize enterprise organizational processes. We call data warehouse or data mart enterprise organizational processes entities. A data warehouse or data marts give a subject oriented view of one particular entity. This view may give to the enterprise a competitive advantage.

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but can include data from other sources. Data warehouses separate analysis workload from transaction workload and enable an organization to consolidate data from several sources. In addition to a relational database, a data warehouse environment can include an extraction, transportation, transformation, and loading (ETL) solution, online analytical processing (OLAP) and data mining capabilities, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

There is different type of data warehousing architecture: Complex data warehouse systems, often called Open Warehouse Solution Framework, made for large corporations and meant for processing large amount of historical data. Data marts, miniature data warehouses or partitioned subsets of larger data warehouse systems, often called department databases (a sales division may build its own datamarts by using sales related data). Operational Data Stores (ODSs), small OLAP datamarts, with a very much reduced amount of historical data, which may be useful for single individuals within the organization

Architecture, in the context of an organization's data warehousing efforts, is a conceptualization of how the data warehouse is built. There is no right or wrong architecture. The worthiness of the architecture can be judged in how the conceptualization aids in the building, maintenance, and usage of the data warehouse. One possible simple conceptualization of data warehouse architecture consists of the following interconnected layers: Operational database layer: The source data for the data warehouse - An organization's ERP systems fall into this layer.

Informational access layer: The data accessed for reporting and analyzing and the tools for reporting and analyzing data - Business intelligence tools fall into this layer. And the Inmon-Kimball differences about design methodology, discussed later in this article, have to do with this layer. Data access layer: The interface between the operational and informational access layer - Tools to extract, transform, load data into the warehouse fall into this layer. Metadata layer: The data directory - This is usually more detailed than an operational system data directory. There are dictionaries for the entire warehouse and sometimes dictionaries for the data that can be accessed by a particular reporting and analysis tool.