You likely have heard about data warehousing, but are unsure exactly what it is and if your company needs one. I will attempt to help you to fully understand what a data warehouse can do and the reasons to use one so that you will be convinced of the benefits and will proceed to build one.
In my experience, not nearly as many companies have a data warehouse as I would have expected. And many of those that say they have a data warehouse don’t really have a true data warehouse, but rather a dumping ground for tables that are copied from source systems with little modification.
For a company to be successful in the future, they must make good decisions. And to make good decisions requires all relevant data to be taking into consideration. And the best source for that data is a well-designed data warehouse.
The concept of data warehousing is pretty simple: Data is extracted on a periodic basis from source systems, which are applications such as ERP systems that contain important company info. Data from these systems is moved to a dedicated server that contains a data warehouse. When it is moved it is cleaned, formatted, validated, reorganized, summarized, and supplemented with data from many other sources. This resulting data warehouse will become the main source of information for report generation and analysis via reporting tools that can be used for such things as ad-hoc queries, canned reports, and dashboards.
Building data warehouses has become easier over the years due to improvements in the tools, improvements in the processes (i.e. see Ralph Kimball Books) and a better understanding of the architectures (see Building an Effective Data Warehouse Architecture). And of course there are consultants who can help!
A goal of every business is to make better business decisions than their competitors. That is where business intelligence (BI) comes in. BI turns the massive amount of data from operational systems into a format that is easy to understand, current, and correct so decisions can be made on the data. You can then analyze current and long-term trends, be instantly alerted to opportunities and problems, and receive continuous feedback on the effectiveness of your decisions. See Why you need Business Intelligence.
The most important ingredient to a BI solution is that it must include a data warehouse.
The concept of a data warehouse is not difficult to understand. Basically the idea is to create a permanent storage space for the data needed to support reporting, analysis, and other BI functions. While it may seem wasteful to store data in multiple places (source systems and the data warehouse), the many advantages of doing that more than justify the effort and expense.
Data warehouses reside on servers dedicated to this function running a database management system (DBMS) such as SQL Server and using Extract, Transform, and Load (ETL) software such as SQL Server Integration Services (SSIS) to pull data from the source systems and into the data warehouse.
Typical data warehouses hold multiple subject areas, and from the data warehouse are built data marts, which each hold a single subject area such as sales or finance (see Data Warehouse vs Data Mart).
The data needed to provide reports, dashboards, analytic applications and ad-hoc queries all exists within the production applications inside your company, so why not use the BI tools directly against this data? Well, there are many reasons why you would want to use a data warehouse instead of the “direct access” approach:
Even though additional hardware and software are needed, the presence of a data warehouse costs less and delivers more value than a direct connection. With the continued drop in costs for processing power and storage, that makes the case for a data warehouse even stronger.
Once a data warehouse is in place and populated with data, it will become a part of a BI solution that will deliver benefits to business users in many ways:
This long list of benefits is what makes BI based on a data warehouse an essential management tools for companies.
This diagram shows a complete architecture of an end-to-end BI solution:
Why & When Data Warehousing? Is it Relevant?
Top Five Benefits of a Data Warehouse
Microsoft EDW Architecture, Guidance and Deployment Best Practices
Why You Need a Data Warehouse
Data Warehouse Architecture – Kimball and Inmon methodologies
Data Warehouse Architecture Presentation Slides
Why Do We Need a Data Warehouse?
Designing an ETL process with SSIS: two approaches to extracting and transforming data
Should you use a data warehouse with a tabular cube?
Why do we need a Data Warehouse ?
What is the purpose of a data warehouse?