FM with Stored procedures

[dssd]

I havea scenario where we have many stored procedures, which have been formed by joining tables and has calculations/business logic as well. It is being suggested that we import these stored procedures and use them for reports as compared to importing the tables and going the normal way

What could be the pros and cons of both?

[blom0344]

If you simply import existing stored procedures that have been designed to fire off SQL scripts, then you will completely miss the point of building a metadatamodel. A stored procedure in FM should be used if it contains code that cannot be simulated by set-based logic. When you need procedural logic, then you may need to use this feature.
In short you will only use the metadatamodel as a procedure-trigger, which begs the question : Why use a premium BI tool like Cognos at all? You will also miss out on most of the FM features, like building multifact models

[dssd]

Thanks. I would need to justify not going for a stored procedure based approach with concrete eamples. I would agree its not best practice but i would need substantiate it.

Could you tell me more FM features that i cant use in Stored procedure

When you say multi fact model, I can still create a multifact query in Stored procedure, so what part of it do i miss in a stored procedure approach

My Stored procedure has a lot of if then else, Is that a good enough reason

[RobsWalker68]

Hi dssd,

I see your problem you have let a DBA near a computer havent you!!!.  Bad mistake  ;)

I agree with Blom, a stored procedure is something that you would use only very sparingly for unique situations but not as a foundation for your metadata model, otherwise the framework manager metadata modelling guidelines document  would be 1 page long that said implement stored procedures ;D

If I understand correctly from your post, each of your stored procedures will represent the final output that will be used to create a report in its totality so each stored procedure is like its own little report silo.  Is that where your dba's are going with this? 

Or does each stored procedure represent a single fact or dimension table?  Not that you would want to do this just trying to get an idea of the thinking at your site?   

Cheers

Rob
   

[blom0344]

Not to put the blame on the DBA, but a large section of these important individuals would lovingly put their lunch in a stored procedure as well. From their perspective it is perfectly logical to wrap a stored procedure around a set based SQL script , just as a container.  I would probably do the same from an administration point of view.

However, no matter how well thought out, the stored procedure will have limited flexibility. a well thought-out model will enable a report writer to add a certain dataitem to a report based on the availability from a standard model. 

In case of SP's, it will probably be an RFC to change the SP's definition, which will involve the DBA once again.

In case of multifact: Sure, this can be handled with fixed scripts, but it will be an even more rigid structure.

I suspect your DBA worries about what will be released against the DB. When keeping everything in SP's he will be in control.

Keeping everything within SP's will bypass any work by the Cognos server as well. For instance, defining cumulative calculations demands local processing and Cognos usually comes up with a pretty good solution

[cognostechie]

Blom and Rob -

How about Cognos using minimised SQL? If the report needs only 1 column from the Stored Procedure and another column from a table not using the Stored procedure and both are joined in the Fm Model ? Would Cognos still generate minimised SQL or the entire SP would be executed ?

[blom0344]

The SP has zero to do with Cognos, it will just be called from the model. My 2 cents would be : no way minimized SQL would be generated. Pretty good point you have there !!

[Lynn]

I find stored procedures tend to be inflexible sources capable of satisfying very specific and limited reporting requirements. I had a client with a DBA who was in love with the silly things and rather enjoyed being the bottle neck in total control. Frankly, the architecture of their "so called" data warehouse was so poorly designed that it was his method of keeping the cracks in the foundation from showing.

Truly self-service reporting for business authors requires a model that is flexible and capable of answering virtually any question that can be supported by the data available in the package. Further, the answers to these business questions are expected to be provided within a reasonable response time. Stored procedures are not a robust solution for user ad-hoc reporting. Combining results from more than one procedure is not something I even attempted but I suspect local processing would be required (and no minimized SQL as pointed out previously). Use in this way would, very likely, mean a poorer performing approach.

If you rely on an architecture based solely on stored procedures you may be limited to simply using Cognos as a really slick way to format output. If your overall project goals and requirements expect more than this, I'd advise a traditional best practices approach.

[the6campbells]

I disagree with most of the comments expressed about stored procedures. They and/or table functions enable a parameterised object which may need simple to complex procedural logic that cannot be easily or efficiently defined in a single compound statement. Ultimately, it may be concluded that business analysts cannot construct new procedures and thus moving away from them makes sense if that is a goal

The key points are:

1. the procedure is expected to return a result set
2. multiple result sets are not supported
3. applying additional sql operations (aggregates, joins ....) is supported but will be done locally on a stored procedure while a table function can be used in a query per a table and thus those operations pushed.
4. only procedures marked as for-write are ensured to run in a write-transaction which commits ... do not assume that the other forms are guarenteed to allow write/commits..

[blom0344]

I think that you miss the point of the discussion. Stored Procedures can be valuable if you need to apply procedural logic (i.e. some requirement not met by set-based operations), but in many cases they are just build as wrappers around an otherwise 'normal' set-based script. All too often DBA or programmers that drift into BI territory keep their old habit without taking note of the very sophisticated meta-data modelling opportunities with premium BI-tools (like Cognos / Business Objects)

Building stored procedures is typically a technical exercise and a business analyst may only be associated with based on a functional requirement. The BI modeller should take every effort to build a model using set-based logic and only think of a procedure if all modelling options have been evaluated (and found lacking)

[pravin.cognos]

Dear all

I got the below information from other forum as a reply to my query regarding FM with SPs without a DWH. I believe this will provide additional info for the ppl who are searching for the information.

---------------------------------------------------------------------------------------------------------
From Phil. W (credit to the user who posted this)

"Any action applied to the result of a SP will require local processing at the Cognos server. This includes grouping, sorting, filtering, calculations, joins, unions, etc. The local processing is a significant concern for performance.
SPs will essentially create silo'd reporting applications where a SP tends to be developed for each specific reporting scenario. This leads to a high level of involvement from the DB team. This type of work allocation means that end users will not be able to effectively create their own reports and any changes or new requirements will take a significant amount of time to deploy to the end users.

Personally, I would not recommend using procedures in any extensive way. They are good for very specific point solutions but basing an entire reporting application on stored procedures is going to cause you more problems than it will solve.

If you are not ready to create a proper data warehouse then you might consider a couple of options:
1. Segment the reporting application into subject areas. Set up a data warehouse for the most important section of the reporting application. Then gradually incorporate other subject areas into the data warehouse as time and resources permit.
2. Create views (or materialized views) in the existing database to generate objects which will look like the generic data structures of a dimensional data warehouse. This will allow you to set up the general structure of the future data warehouse but work with the data in the existing state. Using views does not incur the same local processing penalty that you would see with stored procedures. If you design the views in a generic fashion then you can also avoid the problems with silo report development. If you use materialization at the database then you can also segment the load on the existing database tables.
3. Report directly from the existing database tables. Framework Manager can be used to model a transactional system as a set of dimensions and facts as in a data warehouse. There are some challenges to this process but they can be handled with proper modelling techniques (I would recommend attending the IBM Metadata Modelling course). You would also need to allocate additional resources in your current database environment to support the additional reporting application load on your current table structures. This approach has performance concerns due to the extra work which will be necessary to report from a transactional system and the fact that transactional systems are not typically tuned for reporting. "---------------------------------------------------------------------------------------------------------
If you feel that this is not appropriate, feel free to remove this post.

Cheers,
Pravin

[cognostechie]

I completely agree with blom and pravin.cognos . The purpose of using a modelling tool is to provide a way to use the same set of objects multiple times instead of building individual objects for individual reports. If the DW/DM are constucted properly, there shoudn't be any need for a SP. Though we don't live in a perfect worls and there are many times when we cannot achieve what is required , we should still see if we can enhance the model first insetad of taking the route of a SP simply because it is an option available to get the results for the specific requirement. Creating a SP increases redundancies whereas enhancing the model reduces redundancies because it can be used for multiple purposes.