BI Ltrim\Ttrim on F0911 killing the box

PAULDCLARK

Well Known Member
BI Ltrim\\Ttrim on F0911 killing the box

I know this isn't a Data Warehousing forum, but its always worth a try...

The F0911 is currently being real time replicated (this is a requirement) to a BI Data Warehouse. On that warehouse are a number of applications which via assorted joins query the replicated copy of the F0911.

To get this working the BI joins have a large number of Ltrims and Rtrims to get rid of the padding that JDE has always insisted on adding. This overhead is killing the BI box quite badly.

A suggestion is that a trigger is placed on the replicated table, which does a one off ltrim\rtrim into another copy of the F0911 and the joins run on that instead.

The F0911 is running at 14 million records with a peak load of 300K transactions a day (46K) is more typical, although this will grow.

The disk storage wouldn't be quite double, as the replicated table wouldn't need any of the indexes (other than the clustered one) and the 20 BI indexes would be placed over the table created by the trigger...

My initial thoughts are that we are caught between a rock and a hard place, but the trigger approach would be more efficient in the longer term since the majority of the effort is in removing the blanks on the fly.

Anyone else got any creative solutions or comments?

Thanks

Paul
 
Re: BI Ltrim\\Ttrim on F0911 killing the box

Paul,

We have a similar data warehousing setup. The DBAs have set up replication something like this:

Triggers on JDE tables (not all just the major ones) create before and after images, as appropriate of rows when added, changed or deleted. These are written (with date stamp and a code identifying the action and whether it is the before or after image) to a set of tables (called trigger tables) in the same JDE database, but a different owner (these trigger tables periodically have older rows removed). Then at a predetermined frequency (which can vary depending on the table), the changes stored in the trigger tables are replicated to our data warehouse, removing the space padding in the process.

I hope this helps.
 
Re: BI Ltrim\\Ttrim on F0911 killing the box

Many thanks Peter, I've forwarded that strategy to the BI team.

Paul
 
Back
Top