Using export/import process to clone to data warehouse

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Data Warehousing, ETL, and business intelligence Analyzing an index after rebuild Speed of low-cost query Data warehouse based on operational data model Method other than DB link for large data transfer Data migration with ETL Basic steps for implementing a DBMS Importing 8i dump file to 9i Areas of impact while migrating database from 8i to 9i/10g External tables explained Can ETL tools handle complicated business logic? Oracle data warehousing BI strategies of Oracle and the megavendors Exadata: A first look at Oracle's entry into the appliance market Oracle New Year's resolutions, part 2: GRC tips and customer resolutions revealed Oracle's Top 8 stories of 2008 IOUG chief discusses OpenWorld, DBA pay and security Oracle enters the appliance market with Exadata Tip: Exposing logical attributes with virtual columns Oracle users prepare for MDM Data modeling tools no substitute for hard work eHarmony spurns Microsoft, finds match with Oracle 10g

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary multidimensional database  (SearchOracle.com) operational data store  (SearchOracle.com) repository  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

Wow, this is a long question that touches on a number of aspects of the types of architectural decisions that we normally consider when constructing or maintaining a data warehouse, as well as your basic question on whether to use export/import as a cloning method.

Let's start with import and export as a cloning method. First off I can tell you that it works; this is a solution that will work for what you are trying to do. However, not knowing how large your warehouse is, this may not be a viable option. Consider a warehouse that is 1 or 2 terabytes; the selection of export/import would not be viable. Options that we have used depend on the requirements. For example, for an enterprise data warehouse, we use a number of system-level options. We use either Oracle's transportable tablespaces or we use an EMC feature to quick-copy disks. Both of these are effective solutions, since they minimize the impact to the database, but these may be more appropriate to a larger warehouse. Another option is replication, but it has not been one that I generally would use for a test database. As you discuss you are concerned about posting updates; I can understand this as you will now need to build an ETL structure just to update your testing database. Sometimes I think we complicate our lives unnecessarily.

As far as the type of data warehouses I work on, the current one is an enterprise warehouse that integrates multiple lines of business. The warehouse is currently multiple terabytes and has seen significant growth in the past while it resides on an HP 12-way Itanium with an EMC array. We perform all of our loading via Informatica, so we utilize direct database access as well as bulk load methods, depending on the transactions that we are processing. All of our processing is delta processing. We capture changes in the sources and then post them to the warehouse; our load window is now at about five to eight hours depending on the daily volumes. As for backups, I do not have a time on this as I leave that to operations. As far as refreshing our development and test databases, we used export/import the first time and that took us three days. We have since moved to a simple Informatica solution that completes in about 12 hours.

So I hope that helps you. If you have any follow-up questions please let me know as I am happy to help. Good luck.