In the Information Age, more and more business and socio-political organizations are realizing the advantages that can be drawn by employing the mostly un-utilized resource represented by the
enterprise information pool. Customer orders, product shipments, invoices, customers and employees behaviour, and many more information items can enable a company to better integrate into its
market, predict its trends and proactively react to future market needs or directions. All these informational resources already exist in the enterprise, so there is no acquisition cost, so the
organizations only have to invest in organizing and meaningfully using it to adjust their business behaviour, directly and positively impacting their bottom-line.
The particular class of systems targeted to organize and use large sets of enterprise information to enhance the business perspective and decision-making process are largely known as Business
Intelligence (BI) solutions, such as On-Line Analytical Processing (OLAP), Multidimensional OLAP (MOLAP), Decision Support Systems (DSS) and Enterprise Information / Reporting Systems (EIS / ERS).
These can be deployed as non-integrated stand-alone solutions, commonly named Data Marts. But it is a truism that somebody that knows the past can predict the future, so the most useful deployment
of a BI solution is on top of an Enterprise Data Warehouse that holds integrated long-term history of organization’ information elements.
There is a large industry debate concerning the level of detail to be stored in the Data Warehouse (its “granularity”), and how meaningful a ten-years old atomic data element is for the
organization. The only definite answer to this issue is that there is no universal answer, as each organization has different data retention policies for electronic and written information.
However, the Data Warehouse should have a consistent history depth to provide meaningful cross-references, so many organizations are using an Operational Data Store to maintain atomic data elements
relationally organized, and the Data Warehouse to dimensionally store mostly aggregated data and only business critical atomic data elements.
The Data Warehousing is an intricate environment, and may include multiple components quite different as functionality and complexity. There are two opposed methodologies in Data Warehousing
theory, introduced by Ralph Kimball and Bill Inmon, and many combinations in between, such as the approach promoted by Open Data Systems and introduced in Data Warehousing Environment – An Introduction (Open Data Systems Inc., www.opendatasys.com, Publications section, December 2001). This approach is based on the axiom that there is no universal
“DW solution”, but each organization will implement subsets of the potential environment, shown in the figure below
The back-end layer is usually enterprise-wide and will include one or more of the following enabling structures:
- Data Warehouse, storing long-term (25 years or more) historical data, dimensionally organized, aggregated and/or granular.
- Operational Data Store, maintaining consolidated short-term (5-10 years) detailed history in a relational structure similar with the source systems.
- Staging areas, temporarily storing data in transition between permanent repositories.
Typically, very few users will get direct access into the Data Warehouse, as they should have advanced knowledge about the data warehouse structure and data (for example, to avoid sending an
all-tables all-data join that will seriously impact the DW performance). The regular user layer may typically include the following classes of client systems fed from the Data Warehouse:
- MOLAP (Multidimensional On Line Analytical Processing) cubes, used for their fast response time within a set of pre-calculated data set.
- Data Marts, usually a ROLAP (Relational On Line Analytical Processing) system that subsets the DW information for a particular business area that requires extensive ad-hoc explorations, at the
expense of data retrieval performance. - DSS (Decision Support Systems) usually in a proprietary format required by the rules engine functionality, but also as dimensional structures used with universal Data Mining tools.
- ERS (Enterprise Reporting Systems, also named EIS – Enterprise Information Systems), a de-normalized relational structure specifically designed to support specific reporting requirements
using elements originating from multiple operational systems.
The following table briefly compares the main classes of Data Warehouse clients and their main purpose (which defines the type of data required and processing performed):
Each of these types of client systems has different types of data requirements in order to satisfy their specific end-user analytical needs. Even if they can receive data from the Operational Data
Store, the Data Warehouse is usually better suited as a source at least for OLAP, Data Mining and MOLAP clients.
The basic data support in the Data Warehouse Database for any BI client is achieved by storing the atomic data at the highest granularity level (more detail) required to satisfy any potential
business demand for information. The client systems could then extract and process that elementary level data to create derived and/or aggregated data, pre-computed or on-demand.
Another option is to pre-aggregate some of the required derived information in the Data Warehouse Database, and feed it as a “new” fact to the client tools. While this should definitely
be done when the atomic data is moved out of the on-line media (reaches its retention limit), there are many voices arguing that this should be also done even if the detail data still exists in the
Data Warehouse.
There are many arguments in favour and against each approach. The following table depicts a summary comparison of the most important differences between the two options mentioned above:
It is quite obvious that the balance is clearly tilted toward performing the aggregations in the Data Warehouse Database. This is especially true for aggregates involving complex transformations of
the detailed data based on business-driven algorithms that, in fact, are “new” business measures derived from a set of underlying data elements.
However, caution must be exercised to not overdo and build every potential aggregate in the permanent storage. As a basic rule, the processing that is not part of the standard BI tools
functionality (in other words, is custom built to satisfy specific business rules) should be built as a permanent store in the Data Warehouse. The straight summary, average etc. aggregations along
one or more dimensions should be trusted to the client layer, as both MOLAP and ROLAP class solutions handle very well these standard BI operations.
Moreover, many RDBMS engines now offer advanced OLAP and data mining functionality as integral part of the database server software, enabling variations from the traditional aggregation approach.
For example, Oracle9i can store OLAP data in a relational star schema (consisting of fact and dimension tables), but also multi-dimensionally in an Analytic Workspace (AW), or in a combination of
both.
Also, with the lowering cost of on-line storage media (hard drives), in the recent years it became so much more cost-effective to pre-process data and permanently store the ready for usage results,
versus on-demand processing that requires significant increased CPU power to deliver acceptable performance levels.
In conclusion, creating business driven database level aggregates enables a certain level of client / toolset independence, data storage and protection within a specialized RDBMS and lower overall
Total Cost of Ownership. Without minimizing the role of the client systems in a Data Warehousing environment, it is quite obvious that the first candidate for aggregates deployment is the Data
Warehouse Database, regardless of the type of client system that will ultimately present the data to the end-user.
