How Do I Know When I Need a Data Warehouse?

When getting started with business intelligence (BI), the first step most organizations take is to implement a query and reporting tool to help users create reports and build dashboards directly from the raw data held within the database. We call this the operational data. For many organizations, this approach works well and can support business needs for quite some time.

However, working with operational databases can become a real challenge. As your business becomes increasingly complex, you may need to overcomes cryptic column names, complex relationships, inconsistencies, legacy dates, huge data volumes, and many other data issues in order to achieve the results you need from your BI investment.

But how do you know when you need a data warehouse? Read on to find out.

Common Challenges with Operational Databases

You've already read about some of the challenges you may face when working with operational databases. In this section, we'll dig a little deeper into the top four reasons why you might need a data warehouse instead of pulling your data directly from an operational database.

1. Incompatible sources create problems.

In today’s complex business environment, you’ll almost certainly need to bring together data from a variety of sources. When data comes from disparate sources, it is often stored in different formats or with mismatched identifiers. For example, a vendor might send you data in an XML file or you might purchase demographic data, which could be delivered as delimited text files.

Each new data source brings its own set of issues, compounding the overall challenge of collecting and reporting on data in an actionable way. At some point, you will run into issues that a reporting tool alone simply cannot solve. That doesn't mean your BI investment has been misplaced! Remember, it’s not your BI tool's fault. It’s the way your data is structured.

2. Operational databases were designed for other purposes.

Frankly, operational databases were not designed to be used for reporting. They were designed to be used by a software application, which includes complex program logic to translate the data into meaningful information. This is very different from the way you would design the database if you were approaching the project from a reporting or analytics perspective. Instead of the data being centered around processing transactions, it should be organized in a way that makes it easy to analyze such things as brand performance, year-over-year growth, profitability, and so on.

3. Databases don’t come with instructions.

Operational databases are rarely documented, much less documented well. No user manual can tell you where to find the information you need, how to use it, or warn you about its nuances. No wonder that data is sometimes used incorrectly!

4. Operational data is often riddled with errors and omissions.

If you've been querying off an operational database, you probably already know this. The question to ask yourself is this: Are you making business decisions based on reports you can’t trust? Most query and reporting tools won’t highlight errors in the data, such as failed joins or incorrect groupings because of missing values.

Database vs. Data Warehouse

Many organizations implement ad-hoc solutions to address each challenge. They create a summary table to solve a performance issue or write an RPG program or three to convert the data into a useable format. While these piecemeal patches solve the problem in the short term, this fragmented and labor-intensive approach almost always breaks down in the long term. For example, you might end up with a dozen or more summary tables, each built and loaded by a different path, as well as a manual (often multi-step) processes to combine and massage the data into the required form.

Maintaining these tables over time and employee turnover can be a nightmare! Department heads spend days each month collating data for month-end reporting instead of doing their jobs. Business analysts show up at meetings with different numbers. Someone finds a major error in a critical report and you realize you’ve been making decisions based on incorrect information.

You can’t blame your reporting tool if you feed it inaccurate and untrustworthy data. If you’re in this situation, it’s time to consider a data warehouse solution or at least a managed data mart environment.

A data warehouse sits between your operational data sources and the reporting tool. It pulls together these data sources and transforms the data into a much more reporting-friendly format. Furthermore, building a data warehouse gives you an opportunity to validate and correct errors in the source data before you use it, giving you peace of mind that the reports you deliver are reliable.

Well-designed data warehouses have proven their worth countless times, allowing organizations to unleash enormous benefits from their reporting tools and BI initiative.

Sequel Data Warehouse on IBM i 

You might believe that you don’t have the technical resources to undertake a data warehouse project, expecting that you will need skilled programmers and database administrators. Not so with Sequel Data Warehouse. It takes care of the difficult technical aspects of building and managing the database tables, indexes, and ETL (extract, transform, and load) processes, so that you can stay focused on the business requirements.

Define tables by selecting the data items you want to include. Define business rules to select the data to load, to perform data validation, and to perform calculations and transformations. The user-friendly interface in Sequel Data Warehouse incorporates visual design perspectives that dramatically increase productivity. A metadata layer documents all aspects of the data warehouse—both automatically recorded documentation and your own annotations—to help report authors and end users get the information they need to use the data correctly. And much more.

Building a data warehouse is a journey. It won’t happen overnight, but with Sequel Data Warehouse you can build it in stages, providing real business value in a few short weeks and increasing that value as you build out more areas over time. And you don’t need an army of skilled technical resources to do it!