Solution approaches for table reformatting in datastage

[ray.wurlod]

I can not envisage that this really strange format would make anything go any faster. Indeed, I would suggest that the processing required to decompose that structure would slow things down. My recommendation, therefore, would be to leave the data structures as they are and to seek some other way to identify and cure bottlenecks in processing. For example, are you using inter-process row buffering?

[mehimadri]

Ray : I could not get your full reply(Not able to see the premium content, could you please give the details otherwise). I can realise too, that this would slow things down , but we are hand-tied, so we need to modify jobs for the new structure... what do you suggest as the possible solution approaches

[ray.wurlod]

Resist the requirement. I don't think it will help, if that's the only reason for implementing it. Or at least get "them" to justify it. Post that justification and we might take another look.

You would have been able to view the complete post if you had a premium memberhip. At less than 30c per day premium membership is not expensive, and helps to defray the hosting and bandwidth costs incurred by DSXchange. I do not propose to set any precedent that would prejudice the existence of DSXchange.

[chulett]

God Lord, who comes up with stuff like this? And what's the point of it? Seriously.

[mehimadri]

this requirement changing was part of a siebel upgrade which is our source. the changes have already been made. and the etl team has to showcase solution approaches which would not effect the performance in a big way.

[mehimadri]

this requirement changing was part of a siebel upgrade which is our source. the changes have already been made. and the etl team has to showcase solution approaches which would not effect the performance in a big way.

[ray.wurlod]

OK. It can be done and fairly effiently. But it's still horrible.

Perform a vertical pivot of your data to get the following columns, using stage variables as the pivoting technique.

Code: Select all

Group FN1 OV1 NV1 FN2 OV2 NV2 FN2 OV3 NV3
----- --- --- --- --- --- --- --- --- ---
B1    C1  a   b   C2  c   d   C3  e   f

Then use Len() function to determine the number of characters, for example

Code: Select all

Len(FN1) : "*" FN1 

will yield 2*C1

Code: Select all

Len(FN2) + 1 : "C*" : FN2

will yield 3*CC2

Build each of these pieces in a stage variable and concatenate them all together.

[ray.wurlod]

But, it occurred to me after I posted the previous post, that if it's Siebel that are changing the rules, then surely you should demand that it's they who should change your jobs. Does that seem fair?

[chulett]

this requirement changing was part of a siebel upgrade which is our source. the changes have already been made. and the etl team has to showcase solution approaches which would not effect the performance in a big way.

Understand that. Forced change that you need to deal with. Good luck.

Previous question was serious even though it may not have seemed to be at the time as I am morbidly curious. Did they give you any idea why this change was made? What purpose is it supposed to serve, what problem does it allegedly solve? Is this a change Siebel made in the source or something brainy people in your organization dreamed up? I'm guess the latter as this really doesn't seem like something a major vendor would want to foist on the world but who knows any more.

[mehimadri]

the reason i was told is, the siebel's functionality for audit trail has changed . this is for the call center module of siebel crm. now they are going to keep a encoded data set of all audit trail and decode it while processing.

but they have managed to keep their own presentation layer intact by using a c++ code to decode the data set back to the older format at their business layer.

the changes they say are being made to limit the table from growing at an unmanageable rate.

this requirement changing was part of a siebel upgrade which is our source. the changes have already been made. and the etl team has to showcase solution approaches which would not effect the performance in a big way.

Understand that. Forced change that you need to deal with. Good luck.

Previous question was serious even though it may not have seemed to be at the time as I am morbidly curious. Did they give you any idea why this change was made? What purpose is it supposed to serve, what problem does it allegedly solve? Is this a change Siebel made in the source or something brainy people in your organization dreamed up? I'm guess the latter as this really doesn't seem like something a major vendor would want to foist on the world but who knows any more.

[mehimadri]

THANKS TO RAY

the reason i was told is, the siebel's functionality for audit trail has changed . this is for the call center module of siebel crm. now they are going to keep a encoded data set of all audit trail and decode it while processing.

but they have managed to keep their own presentation layer intact by using a c++ code to decode the data set back to the older format at their business layer.

the changes they say are being made to limit the table from growing at an unmanageable rate.

this requirement changing was part of a siebel upgrade which is our source. the changes have already been made. and the etl team has to showcase solution approaches which would not effect the performance in a big way.

Understand that. Forced change that you need to deal with. Good luck.

Previous question was serious even though it may not have seemed to be at the time as I am morbidly curious. Did they give you any idea why this change was made? What purpose is it supposed to serve, what problem does it allegedly solve? Is this a change Siebel made in the source or something brainy people in your organization dreamed up? I'm guess the latter as this really doesn't seem like something a major vendor would want to foist on the world but who knows any more.