Oracle Data Warehouse Job Roles Oracle Database Tips by Donald Burleson

 

Implementing a useful Oracle data warehouse requires far more than just technical skill.  More than any other type of Oracle database, Oracle data warehouses have a super-fast payback period and many companies realize tangible benefits such as increased sales in just a few months.  For medical data warehouses the benefits are even greater, as lives are saved from analyzing the efficacy of different treatments. 

Of course, you need a skilled DBA to manage the large volumes of data, but the real value from any data warehouse requires knowledgeable professionals from other areas.  Without Informaticists (data content experts) and statisticians, the value remains buried deep inside trillions of bytes of data.  Any successful data warehouse project requires these team members:

• Project Manager
• Warehouse Informaticist
• Statistician
• Oracle warehouse DBA
• Warehouse programmers

Let's take a look at these critical best practice data warehouse team roles and understand how their job duties overlap.

Data Warehouse Project Manager

The data warehouse project manage must have enough technical savvy to understand the technical challenges and apply the right professionals to get the job done right.  Almost all successful data warehouse project managers have Oracle DBA skills, and absolute requirement for understand all-important architectural issues such as partitioning schemes. 

They must also have knowledge about the subject-matter internals of the data warehouse, and most of all, they must have the quantitative skills to manage the complex data mining and longitudinal studies that are required from the warehouse.  The idea data warehouse project manager will have proficiency as an Oracle DBA, plus they must possess at least two years of Calculus and a working knowledge of multivariate statistics, especially chi-square techniques.

The Data Warehouse Informaticist

An Informaticist is an absolute necessity for the data warehouse.  Only someone with intimate knowledge of the data will be able to direct the Oracle DBA staff with decision rules for identifying data anomalies and ensuring that the decision rules inside advanced analysis (e.g. studying user-defined cohorts) are valid.  In medical systems, a Medical Informaticist is used, someone with the rare combination of an M.D. degree and technical knowledge of statistics, advanced analytics and computer programming, and they also need Oracle skills including Oracle SQL, Discoverer and PL/SQL programming. 

Medical Informaticists in Oracle (with a M.D. degree) are among the most highly-paid of all Oracle professions with many earning over $200,000 per year, sometime more than the CIO.  In this article, we see medical Informaticists using Oracle to model artificial intelligence:

And at Stanford University, geneticists and medical informaticists led by Rus Altman have developed a program called Neighbor. It can use text in scientific abstracts to determine which genes share a common biological function. Stanford tested its program by trying to find 19 known gene groups amid 1,900 bogus groups. The program found 15 (79 percent) of the real gene groups, which, while not perfect, shows that a computer can scan abstracts as well as a trained researcher can.

a typical project might have the algorithms digest multiple document repositories in a company - perhaps Lotus Notes, Documentum, separate file systems, Oracle databases, internal Web sites, or some combination of all of the above. The company says it can handle hundreds of millions of documents. Its algorithms can differentiate between ambiguous terms: Is "ER" an estrogen receptor, or an emergency room? Does "Alzheimer's" have an apostrophe or not?

The warehouse statistician

Performing advanced analytics in an Oracle data warehouse requires skills that are far-beyond those of an ordinary Oracle system.  Many shops employ professionals with advanced degrees in areas that are statistics-centered drawing from people with doctorates in Economics, Experimental Psychology and Sociology.  To perform complex and valid studies, the warehouse team must have a statistician with these skills:

• Multivariate statistics - Even a simple longitudinal study required knowledge of the application of applied multivariate statistics.
• Artificial Intelligence - Oracle Data Mining (ODM) product is heavily-centered around the application of AI for the mining algorithms and the statistician should have a firm grounding in fuzzy logic, pattern matching and the use of advanced Boolean logic.

The Data Warehouse Oracle DBA

The data warehouse DBA has specialized skills beyond an ordinary database administrator, and a data warehouse DBA must understand all of the ancillary areas of Oracle data warehousing.  Most are senior Oracle DBA's with more than a decade of progressive full-time experience, and the best practice is to use a DBA with these skills:

?       Oracle Discoverer - The DBA is responsible for the installation and configuration of Oracle Discoverer, especially the web-based Discoverer OLAP tool which requires installation of Oracle 10g Application Server.  The Oracle data warehouse DBA must also understand how to build supporting structures for OLAP and drill-down, including materialized views, and STAR schema transformations.

?       Oracle Warehouse Builder - The Oracle data warehouse DBA must have an intimate knowledge of Oracle Warehouse Builder because the ETL (Extract Transform and Load) processes are a critical component of any warehouse.  The warehouse DBA must understand OWB mappings, the mechanisms for creating aggregation and rollups and the methods for creating complex job streams in OWB to make the ETL processes have complete error-checking and notification.

?       Oracle VLDB tools - The Oracle data warehouse DBA needs to be proficient in all areas relating to very-large databases, including Oracle multi-level partitioning, the creation and maintenance of materialized views and warehouse-specific skills such as bitmap indexes, star schemas and OLAP. 

?       Oracle tuning - Intimate knowledge of Oracle 109g tuning is an absolute requirement for the Oracle data warehouse DBA because of the huge amounts of data and the requirements for fast response time for the decision support components of the data warehouse.  The DBA must understand shared pool internals, Oracle SQL tuning (especially warehouse-related hints such a ORDERED and STAR), Oracle wait event tuning and knowledge of the Automated Workload Repository.

 

The Data Warehouse Programmers

Every data warehouse project needs programmers with specialized skills in coding the all-important components of the warehouse.  Skills for a data warehouse programmer would include online systems programming (HTML-DB is the best Rapid Application Development tool for online Oracle systems) as well as knowledge of Oracle PL/SQL programming, Oracle SQL, Java and regular expression syntax.

• Data Cleansing sub-system - The programmer would need to create an interactive online system for the Informaticist to locate and scrub incoming data anomalies. 
• Decision Support Systems - A programmer is required to create online systems for the end-users where they can display aggregations,
• Expert Systems - For applications with well-defined decision rules, the programmer may be called upon to create expert systems to scan Oracle data and apply sophisticated rules to the data warehouse.

Of course, every shop is different, but these are the minimum job roles and skills required to be successful in getting a fast payback from an investment in Oracle data warehouse technology.