Executive Information Systems

The executive information system (EIS) is a module of the data warehouse that is specifically designed for high-level executives. The EIS must meet the following requirements:

1.  Easy to use

2.  Easy to access

3.  Fast

Easy to Use

Executives do not have the time or patience to learn a difficult product. They must be able to intuitively learn the functions of the EIS as they need them. This piece will determine the success or failure of the EIS. It is wise to consider a Web-based EIS system. This provides several benefits. First, the Web is traditionally a simple tool to use. Most executives feel more comfortable using a Web page than a separate application. A Web-based EIS also allows the executive to bypass the often problematic installation procedure of an EIS client-based application.

Easy to Access

The executive must be able to access the EIS from offices, board rooms, and even airplanes. A Web-based solution could be deployed on the Internet to allow the executive to use the EIS from any personal computer with an Internet connection.

---

Caution:  
Be aware of the security problems that an Internet-based EIS can bring. Consult a security expert BEFORE putting the EIS in place.

---

Fast

Executives will not wait three days for a query to be returned. To avoid this problem, the executive information system must be proactive in the way queries are executed. One of the best ways to decrease query response time is to create summary tables.

Summary tables allow the executives to issue high-level queries without reading all of the low-level data.

Figure 30.6 shows how an executive could issue several different queries that will all be returned quickly. If the executive were to pose the query “What is the total sales for our company?,” this would be easily answered by a summary table that contains the total sales for each region. Notice that the summary table that contains the total sales for the whole company is not used. Although a table of that size would be very small, it would be useless for any other query. Instead, summary tables are made at levels of summarization that can be used by as many queries as possible. The region sales summary table is a good candidate to satisfy this query due to its small size. This table will be scanned into the database in a single disk I/O. The Oracle database reads blocks in increments of the db_file_multiblock_ read_ count parameter. On HP UNIX, the maximum I/O allowed by the operating system is 64KB. Because most Oracle data warehouse applications will use an 8KB block size, the db_file_multiblock_ read_ count parameter should be set to 8. Oracle will read 64KB at a time during full table scans.

Figure 30.6.  Summary tables.

db_block_size X db_file_multiblock_ read_ count = the amount Oracle will

read for each I/O during a full table scan

8K * 8K = 64K

Because the total size of both the region and national Sales tables is less than 64KB, they will both be read in one operating system I/O. Because the same amount of I/O will be used, it is best to keep the data summarized at the lower level.

What happens when the executive asks for the total sales for each store? The Region table will not be able to handle this query. This query can do one of two things:

•  It can read the base order table’s 100 million rows and calculate the total sales per store.

•  It can read from another summary table that is one level down from the Region table. This summary table will have the total sales for each store. The query will read 1000 rows instead of 100,000,000. This option will run approximately 100,000 times faster than the first solution.

These examples show how summary tables are important to the speed of the EIS. However, it is not easy to implement summary tables. A traditional query using ANSI SQL does not have the ability to choose different tables based on the existence of summary tables. The EIS must be intelligent enough to perform this function for the database. This will be performed using metadata that tells the report engine how to construct the SQL statement.

---

Tip:  
Several OLAP vendors have products that eliminate the need for summary tables. Their applications pull data into custom-formatted files and optimize the data for speed. I would highly recommend using one of these products instead of creating the summary tables by hand.

---

A second way the EIS can speed up queries is by denormalizing tables. Relational databases are normalized to prevent redundancy. The lack of redundancy provides faster operation as well as cleaner data. In the data warehouse, redundancy can be used as a tool to speed up queries. An example is the Sales table (see Figure 30.7).

Figure 30.7.  Denormalizing tables for fast access.

The EIS system designer has noticed that the most used fields in this table are product_id, product_name, and price. The designer also notices that these fields are almost always used together. In order to speed up the queries that use these fields, the analyst moves those fields into a separate table. The queries will run faster because they do not have to read the additional fields. When the data is stored with the other fields, the database will retrieve the data in all of the fields including the unneeded ones. Initially one might pull those fields from the original table and join the two tables with a one-to-one relationship. This would prevent redundant data and help minimize the size of the EIS. However, queries that are pulling data from both tables would be much slower. A good solution is to keep the data for the important fields in both places.

An even better solution would be to place an index on both of those columns. Some databases have the ability to read only the indexes if the needed data resides in the index. In other words, all requests that are only asking for those columns could be satisfied simply by looking at the index and never actually hitting the table. Unfortunately, Oracle doesn’t currently support index-only reads.