EDW: Data Warehousing and Business Intelligence

EDW (recursive acronym for "Edw is a Data Warehouse") is an array of tools for building information-oriented applications, with an emphasis on Data Warehouse (DW), Extract/Transform/Load (ETL) and Business Intelligence (BI) features.

EDW's goal is to allow developers to build these kinds of applications, while giving them much room for customization even if they don't develop in Delphi. Thus, even the final user can customize the application, as required.

Why EDW?

Once upon a time we had the problem of collecting in electronic form large amounts of data about specific aspects of the real world. Now the focus has shifted towards effectively querying such large amounts of data, correlating them, extracting synthetic information from them, in short making so that the data is useful in a company's decision-making processes. In order to do that, it is required that the data is integrated, made homogeneous, validated for correctness and quality, transformed in many ways, and this is what a BI infrastructure in general, and a DW in particular, help to do.

Let's now list the requirements that brought to the creation of EDW, a tool that helps in rolling out a BI infrastructure.

  • Need to collect data from heterogeneous sources and integrate everything in the DW; this implies the need to transform, validate and correlate data as required.

  • Need to improve and certify the quality of the data collected, by applying rules for data consolidation, normalization and de-normalization, encoding, decoding and re-encoding.

  • Immediate availability of the data collected, an quick response to queries. This is achieved by a smart design of the DW schema, which employs different design criteria from those used for transactional (OLTP) databases.

  • Flexibility in following changes to the company's organization and changes in processes and information flows.
  • Knowledge about the specific business areas: a tool, as flexible and sophisticated as it can be, becomes useless if you don't know how to use it well.

We have made available a document called  "Data Warehousing and Business Intelligence solutions with EDW" , which explains in greater detail what are the functional areas in which EDW is useful.

Features of EDW

Applications built with EDW offer features that can be grouped into the following functional categories:

  • ETL: extracting, consolidating, cleaning, converting, importing data from a number of heterogeneous sources into the database.

  • Data browsing: exploring the tables of the DW (or any database) with advanced features such as customizable searches/filters, lookups and master/detail structures), following a model that can be created automatically from the database schema.

  • Data editing : inserting, modifying and deleting data, with customizable (possibly complex) business rules to enforce data integrity.

  • Reporting: EDW integrates several different reporting engines (including but not limited to the great open source OpenOffice.org suite) to create both simple and complex reports that can be exported in many different file formats and/or sent/published in different ways.

  • Multi-dimensional analysis: creating interactive analysis "cubes" with advanced features such as slicing, drill-down, rotations, projection, analysis, charting, ability to extract the data in different formats and produce reports.

Case study

We used and use EDW in many projects, and EDW grows and gets better with each project. We are preparing a few small case studies of applications developed by us or by our customers. Here is the first one:

  • StatMaster, that gathers data and produces statistical reports about visitors of fairs and exhibitions.

More case studies to come.

Technology

EDW works with different database engines. The applications we have created with EDW so far work with Firebird, Microsoft SQL Server and all databases supported through Microsoft's ADO and Embarcadero's DBExpress technologies. Adding support for a particular database engine is easy and quick in EDW.

The core of EDW is built in Borland Delphi 2007 and Delphi 2010 for Win32. We're considering porting EDW to .NET or other platforms, in the future, depending on customers' requests. You can extend EDW at the source code level to achieve a total degree of customization, but you should also know that most of the things needed to build an application with EDW do not require Delphi or expert developer skills.

A document called "Introduction to EDW" contains more information about the various technologies integrated into EDW.

Availability

At Ethea, we use EDW and our own BI-related skills to develop custom applications for information analysis. Thus, we have developed and continue developing EDW mostly for our own use.

Nonetheless, as we do with  InstantSolutions, we intend to licence the technology itself to those wanting to develop EDW applications on their own (possibly with support from Ethea).

Download and documentation

We have created a demo EDW application and made it available for direct download. The application contains a small (Firebird embedded) test database, plus all the documentation for developers (EDW Developer's Guide and EDW Reference) which will help you to explore the demo and get an idea of EDW's architecture.

Ethea uses these Technnologies