|
Home > Archive > MS SQL Data Warehousing > August 2005 > MS SQL Data Warehousing and Oracle
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
MS SQL Data Warehousing and Oracle
|
|
|
| I'm looking for some advice/tips on implementing DW on SQL server with data
from Oracle databases. What are the common problems, best practices,
thingies like that?
MC
| |
|
| There is nothing special to do
We do this and there is no problems.
We use DTS to load our SQL Server database using Oracle drivers has the
source.
we don't face any specific issue(s).
"MC" <marko_culo#@#yahoo#.#com#> wrote in message
news:OP5sf%23EiFHA.1460@tk2msftngp13.phx.gbl...
> I'm looking for some advice/tips on implementing DW on SQL server with
> data from Oracle databases. What are the common problems, best practices,
> thingies like that?
>
> MC
>
| |
| Thomas C Mueller 2005-07-29, 11:23 am |
| Marko,
Microsoft had a webcast on this very topic a few weeks ago. You can find it
at http://tinyurl.com/ay4zy. It is filled with best practices and things to
think about when pulling data from Oracle databases for use in SQL Server.
If you ever decide to migrate from Oracle to SQL Server you should check out
the SQL Server Migration Assistant (SSMA) at
http://www.microsoft.com/sql/migration/default.mspx.
--
Thomas C. Mueller
Business Intelligence Specialist
Berbee Information Networks Corporation
"MC" wrote:
> I'm looking for some advice/tips on implementing DW on SQL server with data
> from Oracle databases. What are the common problems, best practices,
> thingies like that?
>
> MC
| |
| Peter Nolan 2005-08-01, 3:23 am |
| MC,
the major problem faced moving oracle data to SQL Server is that some
data types do not match up and this can 'take you by surprise'....the
main ones are fields defined as NUMBER (as opposed to NUMBER(x,y)) and
VARCHAR2 fields that are zero length character strings.
NUMBER in Oracle is a FLOAT not your average numeric. Also all NUMBER
fields are real (125,38) or some such value and they do not always fit
into the target SQL Server data types. Sometimes you need to put views
over the oracle table to bring the field out such that it can be placed
into SQL Server. Some packages, like Oracle APPS define ALL numeric
fields to be NUMBER with no scale and precision and this can be a real
pain in the neck to move to DECIMAL (x,y) or some such field in SQL
Server.
Also, be careful when there are more than 15 decimal places in the
oracle numeric field...some tools truncate more than 15 decimal
places....or indeed more than 15 significant digits...we just had that
problem with an ETL tool.
In Oracle a VARCHAR2 field that has a zero length character string in
it is often interpreted as NULL while in SQL Server a zero length
character string and a NULL are two different things....so you want to
keep an eye out for that one too...
Of course, stored procedures and PL/SQL don't migrate at all....but I
guess you know that...
Best Regards
Peter Nolan
|
|
|
|
|