Foundation Layer is the heart of Oracle data warehouse reference architecture. The success of this architecture depends on how well the foundation layer is designed.

Foundation layer contains the detailed or atomic level of data. As it implies, this layer records the lowest possible granularity of data as this layer of the architecture is responsible for maintaining and managing the data over long term.

To enable the management of data over long term, Foundation layer is designed in such a way that it provides the following features to the warehouse.

Process Neutral:

                The goals and KPIs of organizations change over a period of time and hence process changes occur frequently. To accommodate such process changes, the data model of the foundation layer will be designed in a process neutral fashion. This means that any process change or addition of a new process will not impact the data model or the underlying data. This will reduce the huge costs involved in restructuring of the warehouse.

                For instance, a logical model may represent each level of a hierarchy in columns in a single table (eg: department, sub department, division) with a fixed one to many relationship between them. Now, what will happen if a fourth level is introduced called sub division or what if some divisions can directly roll up to department in future? This situation demands a restructure of our warehouse.  The foundation layer design should overcome such kind of problems using some simple design patterns.

3NF or Normalized:

                Since the lowest possible level of granular data is stored in this layer, data size will be huge in this layer and will be costly to maintain over a period of time. In view of this, data model is normalized to third normal form in this layer. This will ensure efficient storage of data.

The objective of reference data warehouse as already discussed is easier management of data over long period of data. Hence, it must be ensured that we are not duplicating data across layers. For this reason, the tables need not be precisely at 3NF. A little de-normalization can be applied for entities which are accessed for detail reports.

Enterprise View:

                Oracle Reference data warehouse architecture is popularly known as Enterprise data warehouse architecture. This implies that the foundation layer data model should provide an enterprise view of a business.  It is recommended to leverage Enterprise Information model sourced from one of the industry bodies or an Enterprise data warehouse model with database vendors like Oracle.  As the key relationships and entities are all identified in an enterprise model, it is safe to start from here and implement incrementally.

History:

                The goal of a warehouse is to report on historical data, identify trends that will enable business to take key business decisions. Hence, the capability of maintaining history should not be defeated in the foundation layer design. The data model should allow storage of history for any entity that is likely to change.

As we can see, the success of the reference architecture depends on how well the foundation layer is designed. It is very important to get the data model reviewed by SMEs before converting the logical model to physical model.

As with all the layers of reference architecture, reports and dashboards can be built from foundation layer as well. This layer is typically used to report on detailed data.

Swapna Alluri

Add comment