Large datasets advice

[thart21]

Hi,

Need some general advice on which way I should go with using Cognos as our new reporting tool.  Our developers have our framework in place and we are in the process of converting our first report from an Excel based dashboard with up to 4 dropdowns to a Cognos Report Studio report. We have discovered the following:

1. If we try to re-create the dropdown functionality, Report Studio will re-run the entire dataset each time we try to filter by using a dropdown.- this takes up to 30 minutes each time an option is changed. (We need an almost instantaneous refresh of our data as in Excel)
2. In trying to use Active Reports instead, the initial run time is over an hour on a report with 3 dropdowns and 1 chart.
3. I've created another report with a prompt page that we use to just get a filtered data dump. For around 60k rows it take <2 min to run in Cognos report studio, which is acceptable to our users.

My question is, is this normal behavior dropdown prompts and Active Reports for a dataset which could pull up to 50-100k rows?

Will continue to seach for more information but thought I would throw it out there in hopes that someone else has some experience in this area they could share.

Thanks,

Toni

[wyconian]

Hi

It's hard to say if this is usual behaviour or not.  It depends on a lot of factors.  In any case I would suggest a report that takes 2 minutes or more to run isn't good.

Is your fm model based on a datawarehouse or straight from a database? 

What does the drop down prompt do?  It sounds like you're returning the full dataset and then trying to filter it.  If you add some relevant prompts to a prompt page in the report the users will be prompted to select the data they want to see before the report is run.

I'd suggest you should be wary of trying to replicate the functionality you have in excel.  The reporting studios are pretty powerful so there will be a way of achieving the same results as you get in excel but probably in a different way. One thing to bear in mind is excel is a static data source but when you run something in one of the reporting studios and then change the filters (by using the drop down prompts) the query will be passed back to the database each time.  It's going to be faster to prompt for the filters before you run the query.

Can you give more details of what your report is doing?  or check the SQL being generated by the query to make sure it is using indexes etc correctly.

[thart21]

Thanks for the response, our data is coming out of Teradata and as far as I know has had the proper indexes applied, will validate with our IT dept.

To give you a typical example of a report we need. We are trying to go from a custom based report system across our depts to a single consistent dashboard for all users to access. This is why our dataset will be so large as yes, it will need to query on a lot of data.  Our dropdown prompts will be filtering on subjects such as Division, Month, Customer and Type of Order. We will be pulling in up to 5 months of data, which for one of our customers is about 250k rows.
I've thought of replacing some of the dropdown prompts with drill through features, tabbed pages or conditional rendering that could help out somewhat, ie. instead of having a dropdown for Division, I would create separate queries for each of the 4 then show/hide based on the prompt value selected, is that a viable option?  Thanks!

[pricter]

Could you make it clear if you use a star schema?

If this is the case you can use to fill the drop down prompt from the peripheral queries and the not from the fact one.

[blom0344]

I think pricter may be right. It feels like a bad DB structure you are working with.  You may have 250k records for a given customer, but a proper dimension table would probably store a single record regarding this customer. Performing the query 'outside -> in' would - with proper indices -  mean very good selectivity on the facttable and good performance