June 18, 2003 at 10:06 pm
We have a data warehouse at work and there is some data we need to get out of the data warehouse and put back into a single table in an OLTP system.
There are 5 Million rows of data in the table and basically there is nothing that makes it unique except all of the columns that make up the table. A real issue for updates.
This data must be up to date in the OLTP system and it must comes from the data warehouse and it must be available 24/7
What is the best way to look at maintaining this data?. How do we update changed data and add new data? or should we just import all of the data from the data warehouse every day?
The data warehouse is Oracle driven and the OLTP is MS SQL driven.
If you were to import the whole 5 million every day is the following a good or bad method to use.
Existing table is Table A in the OLTP system.
1) create a new table; TABLE B in the OLTP system and perform a non logged import of the data
2) Add correct access rights to "Table B"
2) Add an index to one of the columns in "Table B" (required for searching)
3) Update SQL stats on Table B
4) Rename "Table A" to something like "Table OLD"
5) Rename "Table B" to "Table A"
6) Truncate "Table OLD"
7) Drop "Table OLD"
This is the first time I have had this type of proposal put to me and I really am not sure of the best method to use here.
Any ideas would be much appreciated. At the end of the day we can not modify the data warehouse data and we must have the system live.
cheers
Chris Crowe
June 19, 2003 at 12:21 am
Is there an option of leaving the 5m rows in Oracle and accessing it from SQL Server as a linked server?
Cheers,
- Mark
June 19, 2003 at 12:40 am
i would suggest you add two more new columns to your source table DateCreated and DateModified. first time you dump all the records from source to destination. later you can store the maxdate of datecreated column destination table somewhere and look for records that are added after that date in the source . sameway you can query the source with datemodified and update the corresponding records.
HTH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply