Many people these days have to work on both traditional “online transactional processing” (i.e. OLTP) systems as well as data warehouses. But just what is a data warehouse? Generally speaking a data warehouse is nothing more than a specialized business reporting system used by mid to senior level management to research and develop a tactical plan and/or true long term strategy. Thus in many cases while these systems may be considered mission critical, they are not really true 24 X 7 systems. Often data warehouses are utilized during normal to extended business hours, with some batch activities for data loading and aggregation/summary calculations.

So what tools does one need for such an environment? In short, you really don’t need many new or specialized tools because a data warehouse is essentially just another database. It’s true that the end users are a little different, as are their reporting needs. But simply think of data warehouses as just a specialized reporting database. And you’ll see that many tools are really the same. Let’s examine the data warehouse development life cycle and what tools one might use.

First as with any database, you’ll need to gather the business requirements and design the physical database. For that, a data modeling tool like Toad Data Modeler (TDM for short) can suffice. TDM offers both logical and physical data modeling, as well as support for just about any database platform you might use. It offers both forward and reverse engineering – plus both model to “model and model” to “model to database” compare and sync capabilities.

Second you’ll need to populate or load the data warehouse. Some people will use specialized Extract Translate and Load (i.e. ETL) tools such as Oracle’s Warehouse Builder.  A promising book that I’m reviewing currently and will soon blog about is “Oracle Warehouse Builder 11G: Getting Started”. But others may well decide to write custom applications to load the data warehouse. For that, a robust tool like Toad for Oracle can be invaluable. No single tool offers DBA’s and developers so much from one simple yet robust graphical user interface. Toad for Oracle is the single best database professionals’ Integrated Development Environment (i.e. IDE) or Rapid Application Development (i.e. RAD) tool.

Finally you’ll need a tool for your end users to query and report upon the data warehouse which helps them to transform the massive amounts of raw data into true business information. Some shops have actually used Toad for Oracle. But the business people tend to favor less technically oriented tools that offer better business workflows and capabilities. For that, a tool such as Toad for Data Analysts (TDA for short) is a fantastic choice. TDA offers a very friendly user interface and streamlined workflow, with task flow automation as well as integration with typical end user tools such as Microsoft Office.

So while you might think of your data warehouse as a special beast and thus be looking for DW vendors per se – remember that Quest offers a complete line of tools to support your DW needs. And with some Toad bundles, you can get all the products mentioned above at one low price J