Data source from Oracle 10g problem

Dear Sir,

We met a problem when we move on Oracle 10g from Oracle 9i in the loading process. The scenario is that SQL Server as Data Warehouse, SSIS as ETL tool. But the length of string column as data source table in SSIS grows up 4 times when using Oracle 10g. For example, ABC table x varchar2(8) --> x varchar(32) -->x varchar(8) --> x varchar(8) {Oracle 10g SSIS(source) SSIS(target) SQL Server}

This doesn’t influence the loaded result (I did not find any problem until now), but that influence loading performance and we worry about potential problems.

Do you have any idea or have you met the same problem before?

Thanks,

Jacob

Change your source column in SSIS to be 8 bytes instead of 32.|||

Phil Brammer ,

Thanks for your quick response.

I can't change the length of the column from 8 to 32 by maner because the length of target column is 8. The origal length of the column in Oracle is 8. I guess I should setup some parameters in Oracle side like 'code page'

Thanks,

Jacob

|||No, inside SSIS, set the length of the column in the OLE DB Source to 8 instead of 32.|||

Phil Brammer ,

Thank you very much. This is migration problem. There are more than 100 tables and 1000 columns that ran in Oracle9i very well. But now we have the problem when we want to move on Oracle10g.

Thanks,

Jacob

|||I don't understand the problem then.

Are you asking an SSIS question or an Oracle question?

Please provide more details.

Data Modeling

What's the best Microsoft tool for data modeling of a data warehouse on SQL
Server 2000?
That's just about like asking, "What's the best car?" It depends on
what you will be doing with it. Do you need to model both the logical
and physical? Do you need it to do schema generation for you? Reverse
engineering? Does it need to link into SourceSafe?
The big three that I see used the most are ERwin, Power Designer, and
Microsoft Visio w/ Enterprise Architect. I would suggest that you start
with these, check out the websites, download the free trials, and
compare their functionality and ease of use with what you need.
HTH,
-Tom.
|||ER-Studio is also a very big player
Greg Jackson
PDX, Oregon

Data Modeling

What's the best Microsoft tool for data modeling of a data warehouse on SQL
Server 2000?That's just about like asking, "What's the best car?" It depends on
what you will be doing with it. Do you need to model both the logical
and physical? Do you need it to do schema generation for you? Reverse
engineering? Does it need to link into SourceSafe?
The big three that I see used the most are ERwin, Power Designer, and
Microsoft visio w/ Enterprise Architect. I would suggest that you start
with these, check out the websites, download the free trials, and
compare their functionality and ease of use with what you need.
HTH,
-Tom.|||ER-Studio is also a very big player
Greg Jackson
PDX, Oregon

Data flow task running very slow

Hello,

I developed an SSIS package doing a nightly load into a data warehouse. We have an 8 hour loading window - currently the package takes 16 hours to complete.

I isolated the problem to a Data Flow task where +-35% of the time is spent. This task is pretty straight forward:

- OLE DB source, reading +- 800,000 rows from a SQL server database

- 13 Lookups in sequence, to get surrogate keys from dimension tables. Lookups are all on GUIDS.

- An aggregation

- OLEDB target, fact table in a SQL server database.

It seems unreasonable for the this task to take over 5 hours. It spends the majority of time on the lookups - not so much at target, source and aggregation.

Any comments and advice will be greatly appreciated.

Thanks.

(PS some machine details:

OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name ARK-SQL
System Manufacturer HP
System Model ProLiant DL380 G5
System Type X86-based PC
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
BIOS Version/Date HP P56, 9/18/2006
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone South Africa Standard Time
Total Physical Memory 3,327.30 MB
Available Physical Memory 938.20 MB
Total Virtual Memory 1.10 GB
Available Virtual Memory 2.78 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys)

How many rows are the lookups caching? Are you selecting the whole table (All columns) or are you using a SQL query to specify only the columns you need.

Are you sure it is not your dest that is slow receiving the rows?
If it is, SSIS will slow down the rows retrieved from the source making it appear as if it is something else slowing it down.

Check the lookups. Are as few columns as possible being selected.
Push your rows into nothing such as a Konesans Trash Destination. Does it appear faster?

Also, if your lookup is caching alot of rows and the key you are caching is a GUID, that's a chunk of work to do.|||

You need to find where the bottleneck is. You could start measuring how fast the dataflow 'reads from source'; then how fast it does the transformations (since you have a fair amount of transformations, I would measure a several points); and finally measure the how fast it writes into the destination.

few tips:

The lookups could slow down performance if you are using partial cache. So, limit the number of columns and rows (by providing a select statement with a where clause if possible) and use full cache mode. This approach could generate another problem if memory in server is limited.

See if you can move the aggregation upstream and or limit the number of columns to be used as 'group by'. In general aggregations will perform better if the number of columns with fewer columns in the 'group by'.

If you use an OLE DB destination, try to stick with 'fast load' and set the interval commit to an acceptable range

Use DB profiler tools to measure the performance of each SQL statement use in the data flow (OLE DB source, lookups, etc)

This white paper has some other information

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx|||

I would also highly recommend taking a read of this:

Donald Farmer's Technet webcast

(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx)

-Jamie

|||

Hi Crispin,

Thanks for your response.

I solved the problem last night. The key was in the caching.

I modify the SQL statement in most of my lookups to handle inferred fact entries. I.e. a sale against an unknown customer gets linked to the 'UNKNOWN CUTOMER' dimension table entry. It seems that SSIS sets the caching to partial when you modify the lookup SQL. I assume the lookup had to go back to disk quite often and therefore high cost for big dimension tables (...of which I have quite a few).

I am achieving a +-500% performance increase by keeping the Lookup transform as it is and by setting the cahcing to full. I treat the inferred fact entries at a later stage in stead of at the point of lookup.

|||Good to hear it has approved, however...

The lookup does not change to partial cache when you specify a statement. Not sure why you say it has done this. Was the checkbox checked?

On the partial cache thing, you are correct. The lookup will check it's cache, if the key is found, use it, else run the query against SQL. This can become dog slow. Only time it has been a good thing, for me at least, is when you know your facts are going to be a very small portion of a really large dimension. That way, the first few queries are slow but you don't have to cache 13 million rows only to use 1000.