Model v/s pass through sql

[san_challa]

Hi all –

I started working on Cognos BI tool recently and have few questions.

They have few models in the firm I work for but they too use a lot of sql (like over 2500 lines of sql code for each query subject).Is this necessary ? Can't this be simplified? Does the DB structure play any role in simplifying or making it complex? What is the best db structure ?

How is Cognos report studio different from Excel export if you have to write lots of sql (pass –through sql when you cannot use the existing models ) to achieve the data items you want to display on the report ?

Pl. share your thoughts. Thanks!

[charon]

Hi

I started working on Cognos BI tool recently and have few questions.

-> welcome

They have few models in the firm I work for but they too use a lot of sql (like over 2500 lines of sql code for each query subject).Is this necessary ? Can't this be simplified? Does the DB structure play any role in simplifying or making it complex? What is the best db structure ?

-> thats a lot of sql :S and im not a huge friend of freehand sql because in case the database architecture changes, theres a lot of work that has to be done manualy
-> Db structre does play a role
-> there is no answer for "a best" db structure. it depends on what your company requieres..

How is Cognos report studio different from Excel export if you have to write lots of sql (pass –through sql when you cannot use the existing models ) to achieve the data items you want to display on the report ?

Report Studio is a BI-Frontend Tool, excel is a calculation/ planin tool. Two total different things...in small companies (e.g. 10 people) does not need a BI Suite..Cognos offers you Metadatamodelling e.g..

Pl. share your thoughts. Thanks!
[/quote]


Listen to professionels in this forum (and no, im no db professionel ;), they will most likely listen to the requierments first bevor suggesting a solution or best practice for db structure and tools..and the way to achieve this bi goal in general.
therefore...deliver more detail information and you might get a more precisly answer :)
cheerz :P

[tjohnson3050]

There is an IBM Book that describes dimensional modeling (for a data warehouse) in detail.  It is free to download.  It is over 600 pages, but I recommend you take the time to read it in full:

http://www.redbooks.ibm.com/abstracts/sg247138.html

Also, the Kimball Group has some great data warehouse design tips:

http://www.kimballgroup.com/category/design-tips/

[blom0344]

Hi all –

I started working on Cognos BI tool recently and have few questions.

They have few models in the firm I work for but they too use a lot of sql (like over 2500 lines of sql code for each query subject).Is this necessary ? Can't this be simplified? Does the DB structure play any role in simplifying or making it complex? What is the best db structure ?

How is Cognos report studio different from Excel export if you have to write lots of sql (pass –through sql when you cannot use the existing models ) to achieve the data items you want to display on the report ?

Pl. share your thoughts. Thanks!

My advice would be to stay clear of pass-through definitions altogether. If you must, then use native SQL. Query subjects with 2500 lines of code would indicate solving a large part of the reporting demand within the code. You do not want to build generic models against this. Such a solution could also be a nightmare to maintain in case the source tables would change.

It will also create an additional black-box from the report writer who can only fathom what is going on and how to check the report output

We experienced very severe performance issues with pass-through query subjects joined to other model subjects. It triggered Cognos to avoid database joins and overloading the server with combining data there

[san_challa]

Thank you all for your responses.

I will go through the books and take your suggestions and advice. :)