A data warehouse is a simple centralized storage of all the data collected by a company or an organization’s operating systems. This can be physical or logical. Data warehousing emphasizes more on capturing data from different sources for easy access and analysis, rather than the transaction process.
This typically is a database that is housed in a mainframe server or in the cloud. The data from different online transaction processing (OLTP) and from other sources are extracted selectively to be used for business intelligence activities, for decision support, and also to answer the users’ questions.
Data Warehouse Basic Components
Again, the data warehouse stores that data that is extracted from different data stores as well as from external sources. Now, the data records that are within the warehouse should have the details needed to make it easily searchable and useful to all business users. So here are the three major components of data warehousing:
- Data Sources. This is from operational systems like Excel, CRM, ERP, or financial applications.
- Data Staging Area. This is where the data is cleaned and ordered.
- Presentation Area. This is where the data is stored.
The Design Methods For Data Warehouse
If we go back in data warehousing history, Inmon introduced created one of the most popular methods for designing data warehouse – the top-down design. Kimball also introduced the ‘dimensiona modeling approach’ which is a bottom-up approach where the organization can build data marts first then combine all in a single data warehouse. Aside from these two approaches, some organizations have also used hybrid options.
- Top-Down Approach. According to Inmon’s method, you should build the data warehouse first. Data is extracted from the operational and external systems and are validated in the staging area before they are integrated into the normalized data model. With this approach, the data marts are created using the stored data.
- Bottom-Up Method. This is Kimball’s approach which calls for the dimensional data marts to be built first. According to this method, the data is extracted from the operational systems, then moved to the staging area which will then be modeled into the star schema design. This is done using one or more connected fact tables that are connected to different dimensional tables. The data will then be processed into data marts which will be integrated to form the enterprise data warehouse.
- Hybrid Method. This includes both the top-down and the bottom-up approach.
Benefits And Options Of Data Warehouse
Now you might be wondering by now how can an organization or company benefit from data warehousing? Simple. The benefits that your organization can get are both from IT and also a perspective. By separating operational and the analytical processes can help enhance your operational systems. This will also enable you to access and determine data transfer from different sources. A data warehouse can also offer you enhanced data quality and consistency which can greatly improve your business intelligence.
Organizations or companies can choose from these options: On-premise, cloud, or data warehouse-as-service-systems:
- On-Premises. The data warehouses from Oracle, Teradata, or IBM offers flexibility and security so that the IT teams would be able to control their data warehouse management and their configuration.
- Cloud. Like the Amazon Redshift, Microsoft Azure SQL Data Warehouse, or the Google BigQuery are just some of the cloud-based data warehouses that enable companies to scale while removing the initial infrastructure investments and also the current maintenance requirements.
For your organization or company, it is important that you know all the basics about data warehousing. Let this article be your guide if you want to know more about data warehouse, how it can benefit your business, and which of the methods is perfect for your operations.
