Use a SQL stored procedure to start a Cognos Planning 10 Macro?

[kivlind]

Is there a command you can use with stored procedures/SQL that will execute a macro in Cognos Planning 10?

I'm trying to put together a proof of concept with my IT folks to convince them to dedicate some resources to improving our current data gathering tactics. I want to be able to take automate taking .txt files and update our access tables as well as automate our data import and prepare steps that are tediously manual right now. Being able to say that we can automate the 'getting of data into the CAC' would be pretty awesome. I know they want to rewrite all our steps using stored procedures with a simple VB app on top that can be easily controlled by us finance folks.

Thanks in advance! :D

[ericlfg]

If you're attempting to automate things within planning and have the ability to manage / update the schedule at will, is there any reason you couldn't use cognos connection with it's scheduling systems to satisfy this requirement?

Seems like at first glance you / the business would be re-inventing the wheel by creating stored procedures and writing apps. that handle the same topics already built into the cognos portal..

:)

As for the specific question, I'm not aware of a way to trigger the start-up of a cognos planning CAC macro -- unless you can write a .bat or .cmd into the SP that will trigger the standalone .exe that calls cac macros.

[kivlind]

Good point. Trying to see if there's a way to get regular data updates of our GL Planning application to as automated as possible. Pretty sure you can call a .bat file from SQL. We could do a .bat file that calls the appropraite macro to update access tables with a the new update files created through the SQL steps, and all of this could be executed from a set of stored procedures. I envision the user only having to select a button if at all possible. I don't see any reason why that woudln't work. Could probably nest some steps and include the GTP.

Trying to justify/simplify going a route which has much fewer, "...and then go here and do this..." type of steps. Looking to make it much less to think about from a Finance user standpoint as we are the group that times and executes the updates of data - and there's not the deepest of technical skills (beyond me) in the group.

Thanks for getting my thought train rolling.

[ericlfg]

For the most part (and if I understand your high level requirements), I believe you can accomplish just about everything you're looking to do by creating the appropriate macros in the CAC.  You then can either set this to run automatically at specific times and frequencies, or you can have the users execute these ad-hoc as required when they know the background files have been updated.

Since you're using version 10 of planning, there are a large number of macro options available.

[ykud]

You can do 100% of  Cognos Planning tasks via macro(s) and you can even call it from Cognos Connection portal just by pressing "play" button.
If there's some previous data-munching in SQL, you can just run xp_cmdshell and call the same macro via bat file.

Try using Cognos Connection portal with macros as Jobs -- it's simplifies maintenance a lot (you'll get auditing, run history, schedules and all other goodies).