Report is taking long time to execute

[MANI-1986cognos]

I have a report which will fetch the data from a view. The view has nearly 25 columns. When the view is executed it's fetching the records in 5 seconds. I have created a query subject using this view in the framework manager. When I test that query subject it's taking long time to fetch the sample records and my FM hangs. I already know that if the records are not fetching in the FM level it will not be displayed in the report. But even then I tried it but of no use. In the report level I didn't set any optimization. Also there are no facts in the table and no joins needed since the relation is maintained internally in the view. I have even tired by setting auto group and summarize property to "No" but of no use. Please help me out. Added to it I need to export the report as excel (it will fetch total records at a time).

[bdbits]

Please do not double post. As a new member, you should read through http://www.cognoise.com/index.php/topic,24030.0.html.

If you cannot get a response in Framework Manager, why would you expect the report to work? So first things first.

I hope you are not being forced to use a specialized view for every report. Cognos works best by properly modeling database tables. It's not really a problem to pull data from a view, I have done that myself but only when necessary. You will not be leveraging the capabilities of the tool if a view is required for each report.

25 columns is likely not that big a factor by itself. You say you created a query subject. Is this just a "select * from myview"? When you test it, you say it takes a long time and FM hangs. What does this mean? You never get a response? FM crashes? If you get a response, is it correct?

To be honest, you sound like you barely have any idea how to use Cognos. There are user guides for both Framework Manager and Report Studio that are surprisingly good and have a tutorial approach. It would be best to get some training, as properly using Cognos as a modeler in particular requires a good understanding of business intelligence practices. Barring that at least read through the User Guides.

[raj_aries81]

I have a report which will fetch the data from a view. The view has nearly 25 columns. When the view is executed it's fetching the records in 5 seconds. I have created a query subject using this view in the framework manager. When I test that query subject it's taking long time to fetch the sample records and my FM hangs. I already know that if the records are not fetching in the FM level it will not be displayed in the report. But even then I tried it but of no use. In the report level I didn't set any optimization. Also there are no facts in the table and no joins needed since the relation is maintained internally in the view. I have even tired by setting auto group and summarize property to "No" but of no use. Please help me out. Added to it I need to export the report as excel (it will fetch total records at a time).

Two cents from my side - pls check complexity of the underlying view definitions esp like  if there are any functions that are used in the join conditions (subtring, date functions, case statements). Also, see check the execution time during peak and off-peak hours, it may so happen that there are no DB connections available during peak hours when your scheduled reports/cubes are running.

I'm sure you might have done this, weigh out your options like physicalizing the view so that cognos will run against the it.

Thanks

[New_Guy]

Try using sql against the view you want in reportstudio itself and see if this helps. Check the view definition for any functions being using for data manipulation. I would start selecting couple of columns at a time and check where the issue is.
Thanks
Mike

[bensah]

HI,

Please check if you have any native funcations used in the view query. Other wise please check one by one column and try to access from cognos . You can see which column is creating the problem.

2nd option:-
You can use the view defination sql in the report studio directly and check which column is creating the problem.
If there is any fuction which is not compatible then you need to identify the cognos compatible fuctions in the view.

Thanks,
Benu