Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Unanswered: Oracle Materialized views | Reporting

    I have a requirement as follows:

    Requirement:
    We need to create a Reporting DB to use for Reporting and a Transactional DB will be in separate Host. Basically we need to pull data from a set of tables from Transactional DB and move to Reporting DB schema at proper intervals.


    We might have to get data from different column from different tables and store it in a single table/multiple tables in Reporting DB.


    Solution (Please validate):

    I am planning to use Oracle Materialized views in the same host as Transactional DB to achieve the following part of requirment

    "We might have to get data from different column from different tables and store it in a single table/multiple tables in Reporting DB."

    and then to use Oracle streams to move it in scheduled interval to Reporting DB.

    Is it feasible?
    Or please suggest some solution.

    Thanks,
    Appu

  2. #2
    Join Date
    Sep 2009
    Posts
    39
    for this used ETL (Extract - Transforms - Load)

    you can use Microsoft etl or any other.
    try pentaho.

    Pentaho - Wikipedia, the free encyclopedia

    Extract, transform, load - Wikipedia, the free encyclopedia

    for reporting used a DATAMART or DATAWAREHOUSE.

    summary:

    ETL + (DATAMART or DATAWAREHOUSE)


    In each table added date which have default value NOW().

    then you can extract data every day for day before.
    or you can yet add and time and you can extract data every two hours.

    ETL have possible thatwork automatically.
    if you using ETL (MS) you must use sql server for automaticaly.

    pentaho work with 35 databases.
    speed of load in database is about 25 000 record / s if you have 1:1.

    but you don't then and speed is few record less(maybe 5000 or 10000 records / s).
    Last edited by Fakin; 10-21-09 at 14:58.

  3. #3
    Join Date
    Nov 2003
    Posts
    76
    You can use materialized views. But take into consideration that for scheduling MV's, you'll have to use maetrailized view logs on the source tables to capture net change. And for MV logs you'll need to consider constraints on the source tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •