A data warehouse is a collection of an organization’s data stored in an electronic format, most commonly organized and stored in a structure conducive to high speed analysis and summary reporting. A data warehouse is normally thought of as a database of meta data (summarized data about data), while a data warehouse system would also include components to load, transform, and extract data, as well as tools used to retrieve, analyze, and report on the data. Although the raw data is collected from various sources throughout the organization, the data warehouse normally contains consolidated (rolled up) versions of the information designed to provide fast, effective reporting without the burden of significant added overhead to the organization’s operational databases. Processing queries and reports from the data warehouse leaves the databases free to provide the transactional processing for which they were designed. Additional benefits include standardization of data collected from multiple sources, data collections tailored to enterprise analysis and reporting requirements, and providing the option for data retention schedules differing from the original source data.

Data Warehouse Architecture

A Specialized Form of Database Development, Because a data warehouse is specifically designed for querying and reporting, the construction rules differ considerably from a typical transactional database. While the transactional database uses a normalized database model to insure referential integrity, the data warehouse may, or may not, follow standard database design and database normalization rules. This is because the data structure is ‘report ready’ and as such, certain information is repeated to enhance performance. In fact, the meta data sets are designed to the requirements of the output specifications, not necessarily for drill down inspection of the underlying source data. Most data warehouse models do not contain the source data but rather depend upon updates from the native data sources. Whereas the transactional database depends upon validation rules to insure that only appropriate data is accepted, the data warehouse approach is to standardize the data through the use of standardization filters utilized in the update process. an example would be the collection and summarization of product sizes: S, SM, and Small all get summarized in the Small reporting category. Addition of new data sources requires careful validation of the source data and addition of any necessary data conversion rules.

BI Reporting from DWH

The value of the data warehouse is not in the amount of data that it contains, but rather in the quality and effectiveness of the summarized meta data, and the ease with which it can be queried. User interfaces are an integral part of the warehouse structure and tools such as charts, graphs, and executive dashboard presentations provide effective methods for enterprise business intelligence reporting and can be customized to the precise needs of the user.

Web database development initiatives can also be enhanced by the superior performance gained by utilizing summarized data warehouse information and if you find that your transaction databases are becoming sluggish, you might also want to consider a warehouse solution for commonly reported data that requires extensive processing involving multiple databases. Common sources of meta data integration include CRM and sales reporting systems, parcel shipping systems, cash register and POS systems, payroll time clock systems, and internet based data collection systems.

Our database design consultants can assist you in creating a detailed data model designed to insure maximum query and reporting performance and our database integration team will create the data collection and validation tools necessary for the development of an enterprise quality data warehouse. Contact PROPEL today to discuss your data warehouse requirements.