Oracle data warehouse staffing tips Oracle Database Tips by Donald Burleson

Question: The company I work for is considering using Oracle 10g as the underlying database for re-architecting our data warehouse.  What I'm trying to determine is the proper DBA team size for a Data warehouse in the 15TB+ range.

One of the main reasons I'm asking is because the Gartner groups says that as an oracle data warehouse grows beyond 10TB more DBA commitment is required.

Answer: I've never personally managed a DW that big, but like all questions about Oracle, the answer is "it depends".  Remember, the Gartner group only provides guidelines!  I've personally managed 40 separate instances, and the issue of scale is your main issue.

Just because there are 30TB mores files, it does not necessarily require more human resources (by DBA support), unless you are doing "fancy" architecture like transportable tablespaces, or partitioned tablespaces with RAM-SAN (SSD).

Data Warehouse DBA Skills

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.

That is why Dr. Hamm wrote the book "Oracle Data Mining". The Oracle data mining tools are complex by nature and the ODM professionals must understand how to apply Oracle's powerful tools to the data mining process.

If the DBA job roles include these items, then "yes", you could need lots more DBA staff:

• Oracle Data Mining - A full-time job for one DBA.  This is the capstone of Oracle data queries, a method for defining cohorts of related data items and tracking them over time. The basic goal of data mining is to identify hidden correlations, and the data mining expert must identify populations (e.g. Eskimo's with alcoholism) and then track this population across various external factors (e.g. treatments and drugs). These Oracle Decision Support System (DSS) interfaces require the ability for the end-user to refine their decision rules and change the salient parameters of their domain (i.e. the confidence interval for the predictions).
   
• OLAP (multidimensional database representation) - Building and maintaining materialized views is a big chore.  You may also need staff for Oracle Data Warehouse Builder, a business intelligence tool that assists in the loading and management of the underlying business intelligence data warehouse.
   
• Front End management staff - Discoverer, Hyperion, other front ends - This can be a big job, especially if y6ou are using your data warehouse for hypothesis testing tasks.  The Oracle Business Intelligence Suite  allows for multidimensional data display and aggregation, similar to spreadsheet pivot tables.  Regardless, the front-end should allow the end-user to specify dimensions and request a correlation matrix between the variables with each dimension. The system will start with one-to-one correlations and evolve to support multivariate chi-square methods.
   
• Predictive Analytics staff - Oracle started with predictive modeling in Oracle data mining (ODM) tools, and that Oracle Corporation is developing the Automatic Maintenance Tasks (AMT), a new Oracle10g feature that will automatically detect and re-build sub-optimal indexes.?

Related data warehouse notes:

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.