PeopleSoft EPM sites using DataStage on this forum...

[Athorne]

Hate to clutter the forum with topics that aren't helpful to most, but I can't help but be curious if any other PeopleSoft users out there are using this forum. Most of us PeopleSoft EPM users are new to DataStage and I know there will be more of us as the year goes on. PeopleSoft is dropping support of Informatica Powermart when they go to release 8.9 of EPM. Anyone that knows PeopleSoft probably also knows that they will often pull the rug out from under your support early. For any PeopleSoft sites that are using this forum for the first time, let me just say this forum is the best thing I've stumbled across in a long time. I've only posted a few questions and everytime there are tons of DataStage veterans eager to help. The real kicker is that these people actually have good information to give, unlike the PeopleSoft support run around we are used to.

[kduke]

There is a couple of things you could do. You could start a user group based around DataStage and Peoplesoft on this site. The other solution might be to start a new forum on this site. I would use the suggestions area to ask for ways to group these developers together. I would think the SAP users may want similar things.

[ray.wurlod]

I hope that the DataStage/Peoplesoft experience is better. A customized DataStage class for Peoplesoft support folks (DS324PSFT) was developed and has already been delivered in a couple of locations - I delivered the one in Sydney (Australia).

I agree with Kim, though, that a forum here would be a useful thing.

Maybe not a forum just for Peoplesoft/DataStage users, but a generic one for packaged/embedded DataStage applications. We're going to see more of these with the real-time integration (RTI) components - for example, someone doing data entry may have an address validated, or even certified, in real time by a DataStage job running as a service.

The mind begins to boggle at the possibilities!

On second thoughts, maybe a section for packaged applications, with separate fora (PeopleSoft, SAS, SAP, and so on) in the section.

[Athorne]

That's odd, we have not heard a word from PeopleSoft about a DataStage class. We actually got a credit back from PeopleSoft because the folks we sent to their Data Warehouse training earlier this year were trained for Informatica. We basically told PeopleSoft that we wanted to be credited for the class because the announcement had already gone out by then that Informatica was on the way out and Ascential was on the way in. Amazingly enough they gave us the training credit, and I would have figured we would have been notfied of the new training offering. I'll have to do some snooping to see if they are training it here in the states. Our company will send us pretty much anywhere in the US for training, not sure that I could swing a trip to Sydney.

[ray.wurlod]

The class was a customised one for Peoplesoft support staff. That's why I felt that the support story from Peoplesoft might be better for you in the future - at least they've made some effort to have their support staff learn the product.
I had students from Singapore and Japan as well as from Australia. Two weeks earlier the same training was given to students in the US, alas I have no information to whom.

[Athorne]

My bad Ray I misinterpreted what you said. I thought you gave a class on behalf of PeopleSoft to teach customers. I was hoping there was an offering in the States of the same. I imagine they are still a bit off from developing their own in-house class on DataStage. I know that one of our gripes was the lack of PeopleSoft specific documentation on how to install and configure Ascential. They remedied that situation in mid April with a new version of their EPM 8.8 SP2 installation guide. It looks like they are on their way to getting a training offering together. I'll just have to be patient. In the meantime, do you know who or how we could get a forum thread setup for users of PeopleSoft, SAP, etc.. It would be a nice place to address some Application specific issues without the bothersome clutter to the straight DataStage users on the forum. Any information you could provide would be greatly appreciated.

Thanks,
Andy

[ray.wurlod]

The guys who run the site read all posts. So the message will already be there with them. Of course, they're also earning a living, so things aren't always immediate.
Otherwise, scroll down to the Suggestions for the Site forum, and post there.

[Xanadu]

hi Athorne...
nice to know there is another guy working on something similar..
Even i work on customisnig PeopleSoft ASCL jobs..
Good that we can share experiences..ideas....
To start off .....one issue.... Did you notice that the character datatypes in source and target follow different collation types ?
All the target tables use Latin1_General_BIN where as the sources use SQL_Latin1_General_CP1_CI_AS. This sometimes causes problems in user defined queries when doing the joins. (Collation conflict..)
Any other similar experiences ?

~Xanadu

Hate to clutter the forum with topics that aren't helpful to most, but I can't help but be curious if any other PeopleSoft users out there are using this forum. Most of us PeopleSoft EPM users are new to DataStage and I know there will be more of us as the year goes on. PeopleSoft is dropping support of Informatica Powermart when they go to release 8.9 of EPM. Anyone that knows PeopleSoft probably also knows that they will often pull the rug out from under your support early. For any PeopleSoft sites that are using this forum for the first time, let me just say this forum is the best thing I've stumbled across in a long time. I've only posted a few questions and everytime there are tons of DataStage veterans eager to help. The real kicker is that these people actually have good information to give, unlike the PeopleSoft support run around we are used to.

[Athorne]

Unfortunately, most of my work to date has centered around the install and configuration of DataStage for use with PeopleSoft. We haven't actually entered the development phase of the project yet. As a matter of fact we only just had two developers attend training at the end of July. We are getting close to a point where we can begin to help drive out some common issues. I really wish I was in a state where I could be of some immediate assistance to you. Until that time comes, please continue to post your questions and concerns and hopefully some other EPM users will come into the light and help us with our quest to customize PeopleSoft. =)

It is very nice to know that we aren't alone in our task.

Andy

[Xanadu]

Coool Andy..
Let me tell you .. you will find some really interesting issues in the existing jobs...
All the jobs have array size as 32767 and transaction size of 0. i.e. All the 32767 rows are ritten to the database together only once and committed after all millions of rows are written.....hmmm bad design ? I prefer something like 1000 and 500 respectively...
And this one I think we can discuss...How come they never used varchar in the DWH table design. All the tables use char..
I dont see any advantage of using char...any particular reason you can think of ?
And also they have an interesting incremental load logic... say 10 rows are added to already existing 100 rows.. all the 110 rows are first brought into the staging tables (not just the 10 new ones) and from there the new ones are moved to production ( y can't they just move only the new ones to staging tables too ? :-S )

(I am "bookmarking" this conversation ...incase some other felllow_EPM_guy joins us later we can direct him to this )
Or may be we can post these in the general forum if others have any inputs in this regard..

~Xanadu

[vmcburney]

I'm not using PeopleSoft EPM but I find discussion on it very interesting. Ascential probably had to move very quickly to get the standard project for EPM loads set up, plus they have numerous existing customers to convert, you would hope they have a very good set of standards in place.

Regarding the movement of rows into staging, if you look at some of the data warehouse threads in this forum (cross reference dw with ken bland) I remember posters advocating a complete refresh of staging data with warehouse loads. There are certain drawbacks to keeping and maintaining two copies of production warehouse data (in the DW and in the staging tables) and it is more robust to refresh your staging area with each load.

[him121]

hi...
man first i will give u answer of incremental load..we are using loading strategu as "Update or insert" so every time we are comparing TimeStamp with LaslModifiedDatetime which is going to be passed through sequencer then lony new updated 5 rows will pass ..and written into the staging.......

other answer of 32767..this is maximum...araay size ASCL support..we have source table as millions of records update every day...so its better we load maximum records together and commit...insted of 1 row and commited..this will increase the speed of DW Loading...
thats it..

I'm not using PeopleSoft EPM but I find discussion on it very interesting. Ascential probably had to move very quickly to get the standard project for EPM loads set up, plus they have numerous existing customers to convert, you would hope they have a very good set of standards in place.

Regarding the movement of rows into staging, if you look at some of the data warehouse threads in this forum (cross reference dw with ken bland) I remember posters advocating a complete refresh of staging data with warehouse loads. There are certain drawbacks to keeping and maintaining two copies of production warehouse data (in the DW and in the staging tables) and it is more robust to refresh your staging area with each load.

[Xanadu]

ok ...i am not clear what you mean by "we are using" .. u mean you redesigned the loading of staging tables ? because in the original existing EPM jobs when staging tables are loaded...the table is truncated and all the rows in source are reloaded..the incremental logic is used only in the migration of data from staging to production...
and regarding your point 2, even if you have million rows... wouldn't it be efficient if we write fewer records into the database at one time than 32767... wudn't it clog up the network with so many records ?
I dont mean we should rite 1 row at a time...but I think 32767 is high...
I want to hear what others think in this regard..

Thanks for your response Him.

hi...
man first i will give u answer of incremental load..we are using loading strategu as "Update or insert" so every time we are comparing TimeStamp with LaslModifiedDatetime which is going to be passed through sequencer then lony new updated 5 rows will pass ..and written into the staging.......

other answer of 32767..this is maximum...araay size ASCL support..we have source table as millions of records update every day...so its better we load maximum records together and commit...insted of 1 row and commited..this will increase the speed of DW Loading...
thats it..

I'm not using PeopleSoft EPM but I find discussion on it very interesting. Ascential probably had to move very quickly to get the standard project for EPM loads set up, plus they have numerous existing customers to convert, you would hope they have a very good set of standards in place.

Regarding the movement of rows into staging, if you look at some of the data warehouse threads in this forum (cross reference dw with ken bland) I remember posters advocating a complete refresh of staging data with warehouse loads. There are certain drawbacks to keeping and maintaining two copies of production warehouse data (in the DW and in the staging tables) and it is more robust to refresh your staging area with each load.

[Athorne]

Xanadu,
It was my understanding that only the destructive jobs cleared the table before insert of new data. Any incremental load jobs would not truncate the table first. If this is not happening, you may want to check your options setup on the project. I know from PeopleSoft they set the default to 'D' for destructive, you could try setting it to 'I' for the default, or specifically setting it to 'I' for the job you are running.

Also, back to what vmcburney was saying. The projects that PeopleSoft ship for Ascential were no doubt put together quickly. The decision to go with Ascential was a bit of a shock to many EPM customers as Informatica had been the ETL tool of choice for PeopleSoft through several releases. It took them about 5 months after the announcement to get documentation out on the setup of Ascential.

One thing I would note is that PeopleSoft claims the job names have to follow a specific naming convention for them to work with the table definitions they have setup. It sounds like they have some quirky things they had to setup to get everything working correctly. I'm sure there will be room for improvement and customizations as more sites begin development with Ascential and find better ways to run things.

[datastage]

The decision to go with Ascential was a bit of a shock to many EPM customers as Informatica had been the ETL tool of choice for PeopleSoft through several releases. It took them about 5 months after the announcement to get documentation out on the setup of Ascential.

That is why enterprises using Peoplesoft need to hire the expert DataStage consultants found on this forum