Six Letters of Decision Support Architectures

This article was originally published in "DM Review" (in the 4/15/1999 issue).

Everyone seems to be talking about decision support architectures. Most vendors shroud the issues in complicated terminology and marketing mumbo jumbo. It doesn't have to be this way. This article describes how six simple letters define the possible decision support architectures and how you can tell which architecture you want or can afford, or perhaps even already have.

The Six Letters - E, T, A, L, C, Z

The six most important letters when discussing decision support architectures are:

E xtract - Extraction from source system. (Required) If you don't read the source data, how can you analyze it?

T ransform - (Optional) Transformation of the source system data before analysis occurs. If not performed as a separate step, transformation logic must be done within any and all analysis. Data cleaning is an example of transformation logic.

A ggregate - (Optional) Aggregation of the source system data before analysis occurs. If not performed as a separate step, aggregation logic must be done within any and all analysis.

L oad decision support store - (Optional) Moving and loading source data to a different storage location, often a ROLAP star schema. If not performed and no cube is built, analysis occurs directly against the source system.

C build Cube(s) - (Optional) Building MD/MOLAP cubes either from a ROLAP storage area (when a load sep is used) or (rarely) directly from source system. If no cube is built and no load step is performed, analysis occurs directly against the source system.

Z analyZe - Analyzing the data, whether as an ad hoc query, a static report, an on-line report, an OLAP cube or anything else. This is the step that it's all about.

Which of these letters are used and how they're combined determine the possible decision support architectures. Each of the next sections describes the various interesting combinations, making some sense out of this alphabet soup.

Precursors to ETL and Data Warehousing

Analysis Directly Against Source System (EZ)

This is DSS from yesteryear, with analysis done directly against the source system. The entire data warehousing industry has come into existence because of the shortcomings of this approach. While simple, this approach suffers from several important shortcomings, including the fact that source operational systems suffer from the load of decision support analysis and that end users need to deal with the complexities of source systems. A typical consequence of this latter point is an extremely long IS backlog caused by the fact that end users are not able to write their own reports, perform their own queries, etc., due to the complexity involved.

Analysis Against a Copy of Source System (EL - Z)

This was the industry's first-cut solution to the problem of the load on source operational systems that were already overtaxed: copy the data from the source system to another system, with no transformations or aggregations. This approach still suffers from perhaps the biggest problem listed above, namely that end users must deal with the complexities of the source system (or deal with an extremely long IS backlog).

Data Warehousing and the ETL Market Segment

One-Step Data Warehousing (ETL - Z)

This is the entry point into the "ETL tools" segment of the data warehousing industry. A tool is used to extract data from the source system, transform it in some way and then load it into a separate decision support store. Transformations typically include operations like converting efficient-but-cryptic codes to longer human-readable strings, computing commonly used formulas or derived columns, and denormalizing data into a schema that requires few, if any, joins thereby greatly simplifying analysis. Note that no aggregation is being done here, so users must still know how to compose GROUP BY and HAVING clauses (even if graphically), and the system must compute these aggregations each time they are needed in an analysis.

Enterprise Data Warehousing with a Staging Area and Data Marts (EL - TL - Z)

When building a decision support architecture for a large enterprise, several complexities come into play. First, many source operational systems are so over-taxed that companies are severely constrained by so-called "extract windows" from these systems. It is not uncommon for the decision support group at a large company to get four, or even just two, hours in the wee hours of the morning to "get what it needs" from the operational system. Given this constraint, it often makes sense to perform transformation separate from (after) extraction so as to minimize extraction time. This is the world of staging areas, enterprise data warehouses, operational data stores or dynamic data stores that are built from (non-transformed) source system data and which feed one or more project-level or department-level decision support stores, often called data marts. Data cleansing is an example of transformation logic that might be perfo rmed independently from extraction, given its complexity. This architecture choice is appropriate if/when the business intelligence tools or analytic applications involved work with ROLAP stores as opposed to multidimensional cubes, in which case the cubes built from a non-transformed staging area (EL - TC - Z) architecture described below is appropriate.

Enterprise Data Warehousing with Data Marts and Shared Transformations (ETL - TL - Z)

If a large number of data marts are to be built from the staging area and each will need to perform the same complex transformation(s), it may make sense for these transformations to be done while extracting from the source system rather than while loading each and every data mart. This does slow the extraction down and, thus, may not always be an option.

Multidimensional Database Equivalents

One-Step Cube Creation (ETC - Z)

If/when the multidimensional database vendors start selling ETL tools, they might take this form - one-step extraction, transformation and direct building of a multidimensional cube. For customers who have embraced the multidimensional storage approach (MOLAP vs. ROLAP) to the point of excluding or avoiding an intermediary ROLAP store, this type of solution might make sense. The major players in the ETL market segment focus on populating ROLAP stores (ETL - Z), not cubes. However, several ETL vendors are adding facilities for directly populating multidimensional stores, and in this case, they are capable of populating both ROLAP (ETL - Z) and MOLAP (ETC - Z) stores.

Enterprise Data Warehousing with a Staging Area and Multidimensional Cubes

Cubes Built from a Non-Transformed Staging Area (EL - TC - Z)

Much like the enterprise data warehousing with a staging area and data marts (EL - TL - Z) architecture described above, this architecture is based on the notion of extracting data from the source system as quickly as possible - without transformation - to meet the constraints of extract window time periods. The twist here is that rather than moving data from the data warehouse to one or more ROLAP data marts, one or more application-specific multidimensional cubes are built. Analysis is done against the cubes rather than against ROLAP star schemas as in the enterprise data warehousing with a staging area and data marts (EL -TL - Z) architecture. This architecture choice is appropriate if/when the business intelligence tools or analytic applications involved work with multidimensional cubes (as opposed to ROLAP stores, in which case the enterprise data warehousing with a staging area and data marts (EL - TL - Z) architecture described above is appropriate.

Cubes Built from a Transformed Staging Area (ETL - TC - Z)

If a large number of cubes are to be built from the staging area and each will need to perform the same complex transformation(s), it may make sense for these transformations to be done while extracting from the source system rather than while building each and every cube. This does slow the extraction down and, thus, may not always be an option.

Enterprise Data Warehousing with a Staging Area, Data Marts and Multidimensional Cubes

Cubes Built from Data Marts Built from a Non-Transformed Staging Area (EL - TL - TC - Z)

Many of the leading multidimensional database vendors use a pull model to build cubes rather than providing a push-based API. This typically means that the data from which the cubes will be built must be stored in a relational database or set of flat files in a particular schema or structure. Because of this, it is impossible to directly build the cubes from a non-transformed staging area as in the cubes built from a non-transformed staging area (EL - TC - Z) architecture. This is because the data is stored in the same - or close to the same - structure as the operational source data, which is not likely if ever going to match the structure required by the multidimensional database loader. Compared to the cubes built from a transformed staging area (ETL - TC - Z) architecture above, this architecture has the advantage that the extraction is done without any transformations, again helping satisfy extract window constraints. A possible disadvantage, of course, is the extra data mart storage layer. Some enterprises use both ROLAP and MOLAP business intelligence and analysis tools, in which case having ROLAP data marts built is actually a requirement and an advantage.

Cubes Built from Data Marts Built from a Transformed Staging Area (ETL - TL - TC - Z)

If a large number of data marts are to be build from the staging area and each will need to perform the same complex transformation(s), it may make sense for these transformations to be done while extracting from the source system, rather than while building each and every data mart. This does slow the extraction down and, thus, may not always be an option.

Adding Aggregation to the Mix

No (Pre) Aggregation - Done Within Analysis (no A)

If no aggregation is done while extracting from the source system, while transforming the data for loading into a data mart or while building cubes (this latter case is somewhat unusual given the default behavior of multidimensional systems to generate aggregations), then aggregation logic must be done within the analysis step. This means the complexity of the SQL GROUP BY and HAVING clauses, or their graphical equivalents, is apparent to end users. Further, the system must compute these aggregations each time they are needed in an analysis.

Aggregation During Source Extraction (ETAL - Z; ETAL - TL - Z; ETAC - Z; ETAL - TC - Z; ETAL - TL - TC - Z)

Most decision support analysis is done with aggregations of the detailed source data, typically counts, sums and averages. Rather than have each end user deal with the complexities of formulating these aggregations and having the system have to deal with the workload of computing these aggregations each time, a common practice is to pre-aggregate data based on the analysis expected. Disk storage is cheap; even if the size of the target database increases by a large factor, this technique is still used so as to satisfy analysis response times requirements. In this architecture, the extraction and transformation tool is doing the aggregation.

Aggregation While Building Marts (EL - TAL - Z; ETL - TAL - Z; EL - TAL - TC - Z; ETL - TAL - TC - Z)

This architecture is similar to the aggregation during source extraction (ETAL - Z; ETAL - TL - Z; ETAC - Z; ETAL - TC - Z; ETAL - TL - TC - Z) architectures in the sense that the aggregation is being done by the extraction and transformation tool. The difference here is that the aggregation is being done while building one or more data marts rather than while building the staging area. This approach would make sense if/when different aggregations are needed for different data marts.

Aggregation While Building Cubes (ETAC - Z; EL - TAC - Z; ETL - TAC - Z; EL - TL - TAC - Z; ETL - TL - TAC - Z)

Most of the leading multidimensional databases provide built-in aggregation facilities. These architectures, unlike the previous architectures described in the previous two sections, rely on the multidimensional database to aggregate while building cubes rather than on the extraction and transformation tool.

Conclusion

Thinking in terms of the six letters of decision support architectures and their various combinations can help bring clarity to the architecture discussions in the industry today. By asking questions like the following, any confusion about a particular architecture or between architectures should be resolved:

Decision support architectures may not be as simple as A B C, but they're as simple as E T A L C Z.




Notes that accompanied the original article:

Mike Fine, senior product manager at Informatica Corporation, is responsible for the PowerCenter and PowerConnect product lines. Prior to Informatica, Fine was vice president of engineering at an Internet start-up company and director of engineering at Oracle Corp. for nine years. Informatica is a provider of the leading software platform for deploying business intelligence and analytic applications across the enterprise.