A data warehouse brings dreams of turning data into information and saving your corporation millions of dollars. It also brings nightmares to someone who has to administer and manage this dynamic and growing giant.

Most data warehouses require from three to 10 system administrators working around the clock. With Teradata data warehouses, I always recommend two system administrators. Why two? In case one gets hit by a bus! In this article, we'll look at how easy system administration is with the NCR Teradata RDBMS data warehouse engine.

With most databases, the system administrator is responsible for setting up the database, placing and partitioning data, running database reorganizations and tuning queries. This is a tremendous responsibility, especially when dealing with large amounts of data in a complex data warehouse environment. And, data warehouses, on average, are doubling in size each year. Teradata was designed to let the system manage these functions; and the larger the database or the more mission critical the application, the more Teradata outshines competitive databases.

Many databases depend on the system administrator to carefully place and partition the data. Teradata was designed with the belief that the database could place the data more effectively than a system administrator. Teradata spreads each table row evenly across nodes by using a hash algorithm on the Primary Index of the table and automatically places the data on the disks in a way that optimizes performance. This philosophy is the cornerstone to easy system administration.

Most databases have to split up the mainframe files, FTP these over to landing zones, and then fire up instances of load programs for each file fragment. Teradata uses a hashing algorithm philosophy and state-of-the-art utilities called FASTLOAD and MULTILOAD to make the process automated. It is as easy as defining the input, the output and pressing a button. Detail data can even be loaded and summary tables built simultaneously. Plus, data conversion is automatic and bi-directional.

One of the biggest nightmares for a system administrator is something called a "reorg." About twice a year or when performance suddenly degrades for no apparent reason, a system administrator has to reorganize by unloading the data, checking the fragmentation and reloading the data. Teradata does not require data or index reorganizations and automatically maintains balanced workload processing across all nodes. There is never a "reorg" with Teradata. Even when nodes are added to an expanding system, the database redistributes data on its own.

Another scary dream for a system administrator is the actual data warehouse setup. Some of the system administrators' responsibilities require them to define tablespace, configure UNIX I/O subsystems, set indexes to support a specific data model and manually place data and indexes for optimal performance. Consider this simple example: a system has 150 tables with an average of three indexes each, and you need an average of 100 partitions for performance. That's 60,000 objects that need to be manually placed on the disk for optimal performance.

The system administrator also must set up the entire warehouse before testing to see if the model works. Changing the model means breaking down the database and virtually starting all over again. Teradata comes already configured and allows system administrators to load data and test the model. Once there is a model that works, the system administrator can then set up indexes and summary tables.

Most databases actually require the system administrator to tune the database to optimize some of the queries. Teradata runs SQL queries as written. There is minimal tuning required. And, we all know most users' tools could do a better job in their SQL generation.

Teradata is able to accomplish this because it has the oldest and one of the best decision support optimizers in the industry. Most database vendors have optimizers that are relatively new; but the Teradata optimizer has been "learning" from giant customers such as Wal-Mart, AT&T and others for more than 15 years. That is why Teradata is able to design the data warehouse around both third normal form, star schema models or a combination of both. The database optimizer should do the work, not your system administrator.

Today's data warehouse system administrator should focus on designing strategic applications, helping users become self-sufficient, planning for future warehouse growth and getting the best return on investment (ROI) for the company's data warehouse dollars. They should not be placing and partitioning data, running "reorgs," tuning queries and managing complex database setup. Let the system manage your data so that your system administrator can manage and derive value from your information.

The NCR Teradata data warehouse engine can help make your data warehouse dreams come true. Your company can count dollars during the day and your system administrators won't be counting sheep at night.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access