Most organizations have vast quantities of data. Data is constantly collected as every transaction is made, every employee review is completed, and every sales lead is chased. This data is at the core of the systems that run an organization, and the databases that power these systems have been designed in a way to make an organization’s business processes as efficient as possible. The problem arises when business users need to report on this information, to determine how many transactions were made this year, for example.
There are several challenges to using transactional systems for business reporting:
- The database design that is required for reporting is very different from the design that is required to optimize the performance of transactional systems.
- Running reports against mission-critical transactional systems slows their performance, negatively affecting the systems that run the organization.
- Data that is stored in a transactional database system is not centralized; there is no single source of information against which reports can be generated.
To solve these challenges, we should create a data warehouse that is designed specifically with business reporting in mind, and contains all of the relevant information for reporting. The data warehouse is an important supplier of information to the business, so it is essential that we model both its physical and logical designs. The physical design determines the performance and functionality of the data warehouse, and the logical design is the view that we present to developers and users to capture business requirements.
The Importance of Data Modeling as a DataWarehousing Best Practice
Most data warehouse designers use a data modeling tool to create the logical and physical design of the data warehouse. The logical design ensures that all business requirements, definitions, and rules are supported. The physical design ensures optimal performance in the planning of indexes, relationships, data types, and properties. To support developers of OLAP, data-mining, and reporting systems, the data model also acts as documentation for the final data warehouse.
If we can harness the vast quantities of data that are available in our organizations, we can derive huge business benefits. We can accurately analyze past results, supply this information to BI systems to find correlations in our data, and present information in a user-friendly way to business users. To achieve these goals, we must model the data warehouse very carefully. The source systems are often varied and might lack accuracy. The destination systems often have different requirements and, equally, each business user might have different demands.
We should spend a substantial amount of time modeling the data warehouse system. Data warehouses are an expensive and time-consuming undertaking, but, when designed correctly, they can give enormous business benefits. To achieve our goals, we must carefully model a data warehouse system that we have designed to meet our goals; we must create logical models for the many consumers of our data and physical models to ensure proper database performance; and most importantly, we must strive to meet the needs of the business.