| Comparing data from two databases – Exception reports [message #158096] |
Wed, 08 February 2006 08:26  |
cpmohanraj
Messages: 1
Registered: December 2005
Location: Bangalore, India
|
Junior Member |
|
|
Hi all,
I am not sure if this is the apt place to post this question. I will define my problem here. Please help me to redirect to the right place. I am working on report development using Business Objects.
One of my report requirements– to compare the data retrieved from the source data to the target data and to report only the exceptions – “Available in source but not in target and vice versa”. Comparison has to be based on two columns from source to two columns in target.
Source data is actually an OLTP system and Target is a data warehouse. Both databases are Oracle and they are two different oracle instances. The data to be compared exist in more than one table (close to 6 tables) in OLTP and n OLAP. The approx number of rows to be compared is 1,000,000.
The option A I have – to bring the data from both databases using two different SQL statements into Business Objects report and do the comparison at the report. This can be done but I am not for it as it brings too much of data to the client which results in heavy report size and takes lot of time to refresh.
The option B I have – to create a DB link in the data warehouse for the source database and create a materialized view to bring the required source data to target data. And join the materialized view with the target tables to retrieve the exceptions. I have not tried this and I am not sure if this method will work.
Is there any other way to achieve this in a simple way?
Thanks in Advance,
Regards,
Mohan
|
|
|
|
| Re: Comparing data from two databases – Exception reports [message #158143 is a reply to message #158096] |
Wed, 08 February 2006 11:57  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
I do have a strange way of solving it but may or may not help you. I do use Toad. Now you run an union query on oltp side:using the toad (since you have a million rows to compare, no problem).
Select * from (select num1, num2 from tab1 union select num1,num2 from tab2) order by num1, num2;
save the query result as a txt file: file1.txt.
on the datamart side:you could run the same:
select num1,num2 from big_table order by num1, num2;
and save it as file2.txt.
Now on Toad menu: file+compare files: it would put the both the files left and right side and compare and make a report.You may convert it to PDF or html and send to your boss. If you dont have Toad, you can download for one month free. Some free tools like raptor can do the text files free too.
You dont need to make datalink or materialized view. you may not have to use Toad if you use some concat funtion to convert the date to delimited charcter type which toad makes and then use unix diff command to make comparison. However I am spoiled with tools.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
