Recommended Oracle Architecture

With all these various architectural design options, it should be evident that the software architecture is the single most important determinant of success. As stated earlier, the end-users' business intelligence software selection and/or general user interface preferences will often decide the need for application and/or Web servers. So, the data warehousing DBA can concentrate on the database server architecture. In short, the data warehousing DBA must decide on two basic issues: number and method. When considering the issue of number, the DBA must know how many servers, instances, and databases the data warehouse will have. And, when contemplating the issue of method, the DBA must know how the data will be loaded and then accessed. Thus, if you've read the last two sections carefully , you'll see that this is really all one and the same question. And you should be able to very easily answer that question based on your needs rather than just taking generic advice. But for those who still want to hear the advice, here we go.

Let's start by eliminating certain architectural choices that suffer from potential performance issues and excessive administrative complexities. In other words, let's stick to faster and simpler designs. With that in mind, we should be able to eliminate the following:

• Multiple database instances on one server (2PC and DBLINK performance)

• Multiple databases and multiple servers (2PC and network performance)

• The OPS/RAC option (overly complex administration and network performance)

Thus, we are left with a very simple conclusion: For an enterprise data warehouse, a setup with a single instance and database on one big server is better than multiple instances across many smaller servers accessing either distinct or shared databases. And, in many cases, a staging area makes sense and is advisable. This is a simple, yet effective and efficient choice. It also has the advantage of being the most well-known Oracle architecture, thus leveraging existing and common DBA skill sets. In other words, you don't need to hire a special or overly expensive DBA based on architectural needs.

The advice for people doing multiple data marts is nearly as simple: You should have N +1 databases and instances, where N is the number of data marts. The extra database and instance is for a common staging area from which to perform centralized ETL operations. Unlike the enterprise data warehouse where staging is an option, for data marts, the staging area is a necessity as there will be common information that will span data marts. Otherwise, your ETL programs will duplicate work. As for the servers, you should either place those instances on one large server (possibly partitioned) or across several smaller servers based on each data mart's transactional needs.

The more important point is how we arrived at these conclusions. We did not subscribe to any hardware or software vendor's recommendations. We instead concentrated on answering some very basic software architectural questions related to how we wanted to construct a data warehousing application. With this logically based information in hand, it became much simpler to select the appropriate hardware and software for a successful data warehouse.