DBA's Scope in datawarehousing Projects

Question

As a DBA trainee, what needs to be known in order to be part of datawarehousing projects?

I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used for Datawarehouse projects?

Answer 1

From a general perspective (not platform-specific), here's what I'd recommend mastering for data warehouse projects:

Know how to load data fast. BI projects usually involve nightly loads of large amounts of data. The ETL guys need to shove data in fast with a minimum of concurrency issues. This means knowing when to disable indexes, when to perform tasks in temporary staging databases rather than the production environment, and how to offload processing to the ETL server instead of the database server.

Know how to handle large table scans. BI environments usually have multiple terabytes of data, beyond what can fit in memory. Index tuning can only take you so far. You need to know how to get as much throughput out of the SAN as possible.

Know how to segment archive data. BI environments usually have a small percentage of live, changing data and a much larger percentage of read-only (or read-biased) archive data. You have to know how to recognize those patterns and how to separate that data out into different tables or different storage targets with a minimum of work required by your ETL people.

Know how to handle maintenance tasks. Defragmenting or rebuilding indexes is easy on a 100GB database, but not so easy on a 1TB or larger database. Maintenance windows have to be carefully planned. Backups are another story altogether.

Know when to design reporting tables. If your users constantly access the same aggregate data (like grouping sales data by month or by salesperson, or constantly recalculate a profit percentage) then you need to recognize those trends in the end user queries, design a pre-calculated set of reporting tables, and train the users to access the data that way.