What activities and skill sets distinguish and differentiate a data warehouse DBA from a traditional Oracle production DBA?

What activities and skill sets distinguish and differentiate a data warehouse DBA from a traditional Oracle production DBA? Is it common for DBAs to manage the ETL apps? What approach should I take to convince them to relinquish control? We want to work with the DBAs but feel that our hands are often tied.

A:

Sid Adelman's Answer: The DBAs have enough to do; ETL should not be performed by the DBAs. It is uncommon and unnatural for the DBAs to manage ETL applications. ETL should be an application development job performed by programmers and, if you are using an ETL tool, familiar with that ETL tool.

Les Barbusinski's Answer: My experience has been that the skills needed by a DW DBA are very similar to those needed by OLTP DBAs (i.e., ability to manage the database environment, plan and execute database backups and recoveries, and monitor and tune the performance of the database over time). Data warehouse and data mart tables and views are most frequently designed by data architects and implemented by DBAs. Indeed, DBA involvement in DW/BI projects can minimal, at least until the system test phase (where their performance tuning expertise is invaluable). The short answer to your first question is that if you have a good DW data architect, you don't need a DBA who is dedicated full time to the data warehouse.

As for administering the ETL tool, DBAs should no more administer the ETL tool than they should administer you company's Java servers, BI tools, CRM applications or ERP applications. Any manager who wants control over an environment must be willing to accept full responsibility (no excuses) for its performance, reliability and flexibility ... a difficult feat indeed if your staff does not have the necessary training or experience. Hope this helps.

Evan Levy's Answer: This is a very difficult predicament; the DBAs are between a rock and hard place. I'm sure they're measured on user happiness and system stability. Once users are given ad hoc access to the data warehouse, performance and stability inevitably degrades to the new workload. And, ultimately, the DBAs are challenged with addressing performance in a more dynamic environment. It's very common for DBAs to clamp down on access to prevent surprises from occurring. Tragically, having a highly stable data warehouse system that the users can't access (either because they don't have the right data or ad hoc access) is a disappointment.

It's common got have DBAs involved with ETL processing - but usually after development has occurred. ETL development is a specialized activity that requires the right skills to ensure success. Most DBAs neither have the time or the specialized knowledge to develop ETL code; they typically do have the responsibility to ensure the completed ETL code be productionalized (scheduled and run on a regular basis, tracked, etc.) We typically encourage the DBAs to get involved during design and after development so they can be aware of the new "production" workload. There's a difference between "developing" ETL code and "managing" ETL code. Since most DBAs need to support and manage all jobs hitting the DBMs, it's only natural that they want to have knowledge of what's running.

It's very common in a data warehouse environment to separate application DBA and system DBA activities. Some of the skill/knowledge differences include:

• Detailed knowledge of data content to support data loading. Where most OLTP systems are loaded through online usage, most data warehouses are loaded in bulk on a regularly basis. This requires a more detailed knowledge of index and table structures to support the regular load intervals
• Detailed knowledge of data content to support ad hoc or analytic queries. Multi-table joins are more commonplace with a DW than OLTP. Consequently the application DBAs need to become familiar with query details to enable reasonable response time.
• Oracle configuration details. This is critical. Oracle is a very flexible DBMS, but only if the DBAs fully understand how to modify the settings correctly. Thinks like buffer sizes and data block sizes are critical - and will absolutely affect the user's response time. Using the standard template of the OLTP Oracle systems won't due. They need to configured specially for DW processing.
• Backup/restore planning. DBAs in an OLTP environment must address system backup/restore in a dramatically different fashion than in a DW environment. Transaction journaling is common in an OLTP environment - but not practical in a DW environment. The approaches and skills are different.
• System SLAs. System availability for an OLTP environment is different than DW. In a DW, data integrity is probably the most important thing. In an OLTP environment, response time is critical. In a data warehouse, you can usually wait 15 more seconds if the answer is always right. In an OLTP system, 15 extra seconds would be disastrous.

I would suggest you sit down the DBAs and understand how theyre measured - and ensure that your SLAs match their performance metrics (KPIs). It's very common that the DBAs are measured on KPIs that reflect OLTP not DW processing.

Chuck Kelley's Answer: I think that from an administration point of view, the traditional system Oracle DBA would be fine. However, from a design perspective, I believe that you need a dedicated application DBA as long has there is sufficient work for them to do. (I don't know the size of your data warehouse and how much design is done.) I definitely agree with you that they should not administer the ETL tool. They should have input and requirements on the user and how it is connected, but not the control they seem to be trying to exert

I think that there is a problem with what you are trying to do. Production DBAs are skilled and responsible for the day-to-day running of the database, backup and restore and performance from an overall system perspective. The data warehouse DBA would be more of a designer whose tasks are to build the logical and physical model and help the developers do the access to the database in the most efficient manner. These two roles are significantly different, and (eventually) you will need both roles. Personally, I have never seen a shop where the DBAs manage the ETL environment, so I cannot comment on that.

Larissa Moss' Answer: Education, education, education. Your DBA manager as well as the DBAs would benefit from a conceptual data warehouse seminar, as well as data warehouse design and data acquisition seminars. TDWI offers these seminars at their conferences and at different locations several times a year throughout the US. To find a class schedule, go to www.tdwi.org I suggest immediate training because anyone working on a data warehouse must understand that a data warehouse is completely different from an OLTP system. To start with, a data warehouse is not a "system" at all, but an ever-growing decision support environment that, over time, will contain multiple integrated and reconciled databases for multiple usages. Many of these databases will be multi-dimensional, which is a completely different design from relational. Since these databases are used to support decision-support, they are updated and tuned differently from OLTP databases. In addition, there will be only one expanding (logically coordinated and non-redundant) ETL process to feed this DW ecosystem, which will require dedicated resources to maintain. And the list of differences goes on. These differences (and complexities) must be understood in order to have an effective data warehouse organization with fully dedicated resources.