Data modeling

[lonas]

 Hi,

I just join to project where data modeling in IBM framework manager is replaced by using procedures which are loading data to temporary tables.

I think this is not good approach because we are loosing BI ideology. For each new report developer needs to create new procedure.

Arguments which gives me person who design this solution is:

"Data should be accessed and precalculated through stored procedures, because this is much faster than processing in Cognos.
Try to return data as it will appear in the report. This avoids any slow data processing and linkage in Cognos
Only return fields that are really used, because the whole result set will be copied into Cognos before rendering"


My experience is on SAP Business Objects so I would like to ask Cognos users about suggestions on this area.

Thanks in advance

[blom0344]

That's utterly insane.  ???  It is just an example of not being able to grasp what a good meta-data model can do for you.

There is no copy action anywhere. The metadata definition will - if modelled right - generate minimized SQL to fetch what you need.  you basically are up against programming principles extended to the BI domain. Usually the problems originate from not being able to think in set-based logic.  A stored prodecure is not by definition the wrong way to go, but you should only use if when you need procedural logic that set-based logic cannot provide.

The principle is very much the same with SAP BO and Cognos, where Cognos is technologically more sophisticated.

It usually takes me just a couple of minutes to detect the programmers touch with badly designed BI solutions. This is very much what you are now coping with

[cognostechie]

The approach of creating stored procedures is probably because the resources are more familiar with Databases and not
familiar with Cognos, it probably has nothing to do with technology.

[lonas]

For me it was also very strange solution. I have expirience in design BI based on SAP Business Objects and for me also design reporting on procedures is wrong approach.

I wanted to ask because maybe there was something which I didnt know about Cognos :)

Thanks for answer!

[lonas]

If any other reasons cames to Your mind fill free to put those here :))

[blom0344]

The concept (rather brilliantly achieved i think) of a metadata model like FM (or - to a much lesser extend BO's Universe Designer)  is to resolve changes in the source structure by applying several layers. Even with more than trivial changes in the Db structure you can compensate these within the model layer.  This is an immensely powerful way to insulate the front-end products from changes in the back-end. It is not the answer to any kind of change, but it at least gives some flexibility (not to mention the solution on how to deal with multifact multigrain scenario's)

Secondly, it offers an intuitive graphical interface, where stored procedures need to be handled as pure code. I like writing code myself, but if one only uses SP's as a wrapper for  using SQL insert statements, then it is more a matter of generating standalone reporting tables for specific reports

[MMcBride]

I have to agree with the other posters here, the desire to use Stored Procedures is probably a result of a lack of understanding on exactly what a semantic layer is used for...

The only time I would even consider this type of approach is if Cognos is not the only method of accessing said data.

You can create Query Subjects in your FM Model and publish those to the end users - or you create a database view, bring the view into Cognos and publsih that. Ultimately they are the same thing (A business representation of the data)

From a Cognos perspective the Query Subject is much easier to deal with and manage, however if this data is going to be sourced elsewhere then having the logic in the Database means you don't have to use Cognos as a source or replicate the business logic found in the FM Model.

The same can be true of Stored Procedures - these are just mini programs within the data, building a solid FM model can accomplish the same results.

Stored Procedures are normally used to Transform the data, but then you are comparing Apples to Oranges... If you have some Stored Procedures to process aspects of the data - leave them in place and look further down stream. We see a lot of different business areas that use Stored Procedures to "fix" or adjust problematic data - I wouldn't want to have to process data that way within Cognos.

When I am normally looking at Stored Procedures I am looking at replacing the logic stored there with our ETL tools prior to loading the Data Mart tables. If you have Stored Procedures in your Data Mart used for reporting I would say your data is still half cooked... Put it back in the "ETL Oven" for a bit longer...

[lonas]

Thanks for answers!

I have one more request - could You please advice, send any documentation, knowledge that building semantic layer and modeling data on Cognos level is way how its should be done and this is recommended good practice?

thanks in advance!

[MFGF]

Hi,

Some interesting reading in this transcript of a webcast by Chris McPherson. Might give you what you need?

ftp://public.dhe.ibm.com/software/data/sw-library/cognos/webcasts/wa_tti_modeling_proven_practices_for_ibm_cognos_framework_manager/wa_tti_modeling_proven_practices_for_ibm_cognos_framework_manager.doc

Cheers!

MF.

[lonas]

thanks :)