In the Field

Build Better Data Warehouses

The processing capacity of the Oracle Exadata Database Machine means fewer physical structures and better business intelligence.

By Andy Flower

January/February 2011

As a colleague of mine used to say, “If we had infinite processing capacity, we wouldn’t need data warehouses.” Having one machine with infinite processing capacity would allow our organization to handle both online transaction processing (OLTP) and our business intelligence (BI) and analytic needs. All the things needed to produce metrics in an analytic space could be done on demand: integration, computation, and aggregation.

Enter the Oracle Exadata Database Machine

No, the Oracle Exadata Database Machine is not the infinite machine that will make integration, computation, and aggregation all on-demand functions. But the Oracle Exadata Database Machine helps us to start thinking about simplifying some of the processing and gyrations we have to do to build and deliver an efficient data warehouse solution. With Oracle Exadata, we can more quickly read the data we need to satisfy our queries. Without Oracle Exadata, we need to build separate structures—such as materialized views—in our data warehouses to boost performance. With Oracle Exadata, we don’t need to build as many physical structures, freeing up more time for using the data instead of processing it.

In general terms, there are two reasons for building additional data structures, or even databases, above an integrated data warehouse. The first is to make the data easier to consume by our end users and their reporting and analytic applications. To get better usability and to focus our analytic applications, we create a new subset of our data in snapshots or data marts to support a specific analytic or reporting application, and that subset is just another physical projection, or movement, of the same data. With Oracle Exadata, however, we create fewer physical projections and can create more logical projections of the data in our BI tool of choice in our integrated data warehouse for the same purpose.

The second and probably more common reason we build additional structures above an integrated data warehouse is to improve query performance. Because users of data seem to change how and why they use the data in the data warehouse, good data warehousing teams monitor the usage of their data warehouses so that they can proactively manage usability and performance of the database and add performance- and usability-enhancing objects such as indexes, materialized views, and OLAP cubes.

Even with Oracle Exadata, it is still a good idea to actively monitor how the data warehouse is being used. And we will still need to build objects in the database that provide better usability and performance, such as OLAP cubes and materialized views. Yet with the efficiencies of the database machine, we do not have to begin our BI application design with the assumption that we will be building yet another new database. We can be more dynamic in satisfying changing user needs because there is one integrated warehouse with fewer physical projections that we must also manage and change.

Efficient, Integrated, and Green

With Oracle Exadata, fewer copies and reprojections of the same data also mean fewer database servers, fewer disks, less hardware, and reduced power consumption and datacenter floor space. And with Oracle Exadata, we get more in a single rack of the integrated server and storage platform than we get in other racks. Plain and simple, we can do more with Oracle Exadata with fewer datacenter tiles occupied and less energy consumed.

There are many other aspects of Oracle Exadata that are compelling, and as the number of Oracle Exadata users grows, so does the activity in the user community. The Independent Oracle Users Group (IOUG) has an increasingly active Oracle Exadata Special Interest Group (SIG). If you want to know more about the Oracle Exadata Database Machine and other Oracle Exadata solutions, the Oracle Exadata SIG is a great place to learn and share with some of the Oracle community’s best and brightest.

Next Steps

 LEARN more about Oracle Exadata SIG and COLLABORATE 11

Photography byJeremy Galliani,Unsplash