A data warehouse or DW is an electronic database capable of storing information directed to fulfilling requests for users’ decision making tasks. The right data warehouse design is therefore important since some design models can’t be effectively used by users within an organization for proper decision making. An accurate data warehouse design model should be highly dynamic, current, and extensive in order to give users a more accurate representation of the data contained in the DW as it changes.
Likewise, the chosen schema for the database must be directly created and organized according to the model for it to serve as an efficient management, decision-making, and communication tool. Prior to actually completing a data warehouse design, architectural goals must be first specified and understood very well. Since the main goal of the warehouse is to efficiently serve its potential users, the need to understand different user types, their interaction characteristics with the database, and their particular requirements is of the utmost importance.
In this light, designing an effective data warehouse design must satisfy specific requirements for it to be useful. First off, it should provide a streamlined and effortless experience to users to become successful. It should likewise work without any interference to OLTP or online transaction processing systems and offer core storage area of consistent and pertinent data. In addition, the data warehouse must also be capable of quickly processing and answering complicated user questions and offer an array of robust tools for data analysis which include data mining and OLAP / online analytical processing.
Furthermore, for a data warehouse design to function properly, it should have particular characteristics. It must be built according to a dimensional model and store historical data with both summarized and detailed data versions. It should also be capable of focusing on one subject whether it is finance, inventory, or sales and merging unrelated data coming from various sources while still maintaining consistency throughout. Note that the size of a data warehouse is irrelevant and is generally dependent on the data amount required to be accessed by users.
An effective data warehouse design begins with taking into account all the requirements of specific users. These typically include the executives and information consumers which make up 83% of users, 15% knowledge workers, and 2% statisticians. Users can get data directly from the relational database of the analytical services output which include data mining and OLAP. Direct questions to the DW relational database must be restricted to queries that can’t be possibly answered via the existing means, which are typically more effective than direct questions and provide reduced load to the database.
Specialized applications and tools for reporting normally accesses the data warehouse directly. Information consumers don’t actually directly interact with the data warehouse and usually get reports via email or through a web page containing information sourced from the database. Executives may order other users to generate reports or utilize standard reports from the DW. On the other hand, analysts write complicated questions to acquire and accumulate particular data that can’t be accessed by conventional means while statisticians regularly obtain information by using specialized analysis applications.
As you know by now, ultimate success of a data warehouse design model is primarily dependent on its users’ approval since without them, an organization might as well confine their data to conventional data storage facilities—i.e., on disks, hard drives, paper, and kept in the company storage room.