There are many business intelligence tools out there, but as the DBA, it should not be your job to select one?just to support it. However, that means that you'll need a basic understanding of its architecture, resource requirements, database connection model, query construction techniques, query tuning capabilities, and numerous other aspects that will influence your software architecture definition.
There are three basic business intelligence software questions to ask:
Will the business intelligence user interface be fat or thin? (Will there be a web server?)
Will the business intelligence application be two- or three-tier? (Will there be an application server?)
If there are web and/or application server components, what operating system (OS) platforms are supported?
Often, the end-users' business intelligence software selection and/or general user interface preferences will decide the first two issues for you. While this may seem like an oversimplification, the answers to these two questions can yield many different results. Assuming that typical data warehousing business intelligence software users have Intel-based personal computers (PCs) running Microsoft Windows, then the four most common possibilities include (shown in Figure 2-1):
PC to database server(s)
PC to application server to database server(s)
PC to Web server to database server(s)
PC to Web server to application server to database server(s)
Of course, the Web and application server components could be on the same physical box as the database server. This diagram was meant merely to show the logical concept of all the possible components and their interrelationships.
Although there are numerous architectural designs for both Web and application servers, the key issue for any DBA is the Web and/or application server's process model. Common process models include:
Single-process/single-thread with blocking input/output (I/O)
Single-process/single-thread with non-blocking I/O
Process per request
Thread per request
The ramifications for the DBA are in the volume and nature of the corresponding database server processes. These characteristics can affect the DBA's decision regarding Oracle's process model for issues such as:
Multi-threaded server (MTS)
Parallel query option (PQO)
OPS or real application clusters (RAC)
Let's examine a simple, yet realistic example. The selected business intelligence software requires an application server. Typically, the business intelligence front-end constructs a report definition that the application server then processes. But, a single business intelligence report may in fact possess dozens of individual structured query language (SQL) queries, which the application server submits to the database and then coalesces into actual reports. Moreover, the application server submits all those requests simultaneously using a process per request process model. In addition, a single business intelligence user may submit multiple report requests concurrently. So, a single business intelligence end-user may in fact represent hundreds of simultaneous database connections!
We're not done yet with this example. Let's also assume that the application server can only run on a Windows NT server while the database platform will be UNIX. That's a "boatload" of network traffic described above between these two servers. So, it would probably be advisable to put the two servers on a dedicated, isolated fiber network connection. Are you now beginning to see how the software architecture drives the hardware selection process?