stored procedure usage in FM

[nsaxena]

Hi All ,

Can anyone suggest me in what cases we should use stored procedure  in FM also how to use it.

Any related document or link will also be helpful. Thanks!!

[MFGF]

Hi All ,

Can anyone suggest me in what cases we should use stored procedure  in FM also how to use it.

Any related document or link will also be helpful. Thanks!!

Hi,

Personally, I'd only use stored procedures if they already exist and perform complex processing it would be difficult to implement in your Framework Manager model.

In another post recently you asked about cardinalities in Framework Manager, and Lynn sent you a link to the Framework Manager User guide. The very same guide has information on defining stored procedure query subjects in Framework Manager - you already have the link from your other post, but in case you lost it, here's the link to the guide:

http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.2.2/ug_fm.pdf

Take a look at Chapter 5 - the section entitled "Stored procedure query subjects"

Incidentally, if you go to Google or any other search engine and type in "Cognos 10.2.2 documentation", the first hit is (unsurprisingly) the main documentation index, from where you can find all documentation for Cognos. You don't have to ask on here for links then wait for someone to answer - you can find information yourself in seconds. It's a marvellous thing, the internet! :)

MF.

[cognostechie]

Hi All ,

Can anyone suggest me in what cases we should use stored procedure  in FM also how to use it.

Any related document or link will also be helpful. Thanks!!

I personally do not like to use Stored Procedures as it just reduces the functionality of the tool and system as a whole. The only reason I would use it would be when I have a scenario which required to pass a parameter value and then execute the code. Even in some of those cases, a prompt macro is useful and can be used with a normal query subject.

I can mention a scenario here when a SP was used. A Client (Intel Corporation) has an option which they give to the users and that is that the users have the ability to refresh the data in the data warehouse. There is a Report Studio report which lets you click a button on a Prompt page which then activates a parameter passed to a SP which then initiates a code to refresh the data. 

[bdbits]

I have never liked using stored procs much for scenarios like reporting. It's a needless layer, in my opinion, and often inhibits user self-service. So it should be no surprise that it has not been unusual for me to have conflicts with DBAs over database abstraction layers, when I could accomplish it much easier or better by having direct access to tables.

It can also be pretty hard to tune Cognos performance if you are using stored procedures.

[nsaxena]

I can mention a scenario here when a SP was used. A Client (Intel Corporation) has an option which they give to the users and that is that the users have the ability to refresh the data in the data warehouse. There is a Report Studio report which lets you click a button on a Prompt page which then activates a parameter passed to a SP which then initiates a code to refresh the data.

Hi Cognos Techie,in scenario you mentioned,the SP should be of type Data Modification instead of Data Query. Is it correct?

[cognostechie]

Probably Yes. I haven't used SP query subjects for a long time so I am not sure. I actually hate to use SPs  :)

[MFGF]

Hi Cognos Techie,in scenario you mentioned,the SP should be of type Data Modification instead of Data Query. Is it correct?

No. Data Modification stored procedure query subjects are only visible/accessible from within Event Studio. The SP would need to be a Data Query stored procedure that performed the changes as well as returning a result set of some sort.

MF.

[nsaxena]

Thank you all for nicely explaining.

If you can give me more real time scenario where SP is useful it will just make picture more clear.

Thanks in advance!