If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Users running ETL question

Started by terry_stjean, 13 Aug 2013 07:14:29 AM

Previous topic - Next topic

terry_stjean

We have a number of text file uploads to our data warehouse. These are supplied to I.T. by users and then I.T. has to manually run Data Manager jobs to load the files.
I want to push these text files back to the users to upload themselves.
So, I am working of a poc for this. I have published a Data Manager job to Cognos Connection which uploads on of these text files. The upload works fine from Cognos Connection.
The Data manager job loads the text file to a table, validates the data, then loads to the data warehouse.
The issue I run into is around notification if there are any validation issues.
Ideally, I would sent the user an email if the validations failed. In the Procedure or Email Node, I can "hard code" the To address to a specific person or email group but not have it dynamic enough to just send to the person running the job. So, if John runs it one time and Jane runs it another time, I can set up John and Jane in an email group but then it goes to both. Ideally, I would like the email to go to John if he runs it or Jane if she runs it.
So, then I was thinking, if there are validation errors, I would produce a log file.
In Cognos Connection, you can view the log files for the job run but if I create a new error log file, there doesn't seem to be a way to view this with the other job logs from Cognos Connection.
So, the only solution I have come up with is, if there are validation errors, fail the job and save the error log file to the same folder the original text upload file resides in.

Any other suggestions?

ykud

I'd try using Event Studio and Audit package.
1) Add an event in your Connection job after DM load, change job execution to continue on error
2) In event, check the DM job step status by looking at Audit package RUN_JOBS query subject, you'd see the user running the job there as well
3) If DM job step failed — send mail

Not sure how to automate picking up log file from DM folders, but I'd just include the run history link for the job in the mail and let the user click on details button )

terry_stjean

I came up with a solution which seems to work fine.
I have a secific upload folder which the users save their files to.
They then run the Data Manager job from the Cognos Connection portal.
If there is any validation errors, the job fails and I save a dump of any rows of data with error message to an error log (.csv) and save to the folder their upload file resides at.
If the file has no validation errors, then the job is logged as successful and there is no error log.

I don't sedn out any emails since I can't send it to the person running the job, which could change from time to time.