Data Warehouse

A data warehouse is a copy of reference and transaction data specifically structured for query and analysis. Typically, it combines data from disparate systems, providing a complete view of business.

Many organisations have legacy systems, where applications were built or acquired some time ago. These systems provide functionality that met the requirements of the day, however, they are seldom integrated.

Even with modern systems, there is still a case for data warehousing. Most modern systems: CRM, Order processing, ERP, etc are designed to support high transaction through put. System requirements may provide for operational reporting but they seldom provide wider data analysis.

The 'copying of data' is typically a process of extracting data from systems, transforming and combining the results for storage in a consistent database format. As such it forms the hub of business intelligence systems. It provides a “single version of the truth” to ensure consistent reporting throughout an organisation.

Given this, end users will know what a data warehouse should provide. It should:

• Show me what is important
• Let me ask why
• Let me change my mind
• Let me see other data
• Let me see the top line, the bottom line, and where the money went

Who decides what is important? The end users, so this necessitates that the data warehouse can handle these requests. It requires a cohesive set of flexible structures to aide navigation and provide accurate summarisation. Without this the end user would find it hard if not impossible to query and analyse the data in the data warehouse.

How the answers to these questions are presented varies depending on the needs of the end users. There are a number of ways to design a data warehouse and many supporting technologies to provide the navigation aides and summarisations.

Simple reports may be delivered by querying the data warehouse directly. Typically these reports are produced offline in batch and provide for the operational reporting needs of the organisation.

Data analysis, attempting to answer “Why did it happen?” usually requires the data be packaged in a form of “Multi-dimensional Cube” or “Data Mart”. This allows users to “drill down” into the data to refine analysis and gain greater insight.

This type of structured analysis does pre-suppose that the area under investigation is well, or at least reasonably well understood. The intention being that the structures aide navigation to the answers. But what if the area under investigation is not well understood or is volatile and unpredictable? It may be difficult to predict the structures needed to provide the users with the flexibility needed to answer their questions.

To tackle this issue users need to analyse data without recourse to formal structures.  Instead they rely on the natural relationships held within the data itself. This provides the means to explore relationships within the data rather than simply navigating through it.

Users with full access to the data warehouse can address questions like:

• What effects change
• Where are the impacts
• What is significant
• What is it that I don’t know
• What are the undocumented rules of my business

This is sometime call an 'Exploration Warehouse'. It is an integral part of the data warehouse. It provides added value and insight into the often hidden nature of the business.

Lets blow away one myth. Building a data warehouse, need not be onerous or expensive.

• Focusing on what the business needs is key
• Designing a database that the users can understand is essential
• Choosing the right technology to implement your data warehouse will make all the difference