Oracle data warehouse staffing tips
Oracle Database Tips by Donald Burleson
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
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
That is why Dr. Hamm wrote the
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
If the DBA job roles include these items, then "yes", you could need lots more
- Oracle Data Mining -
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
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
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.