Reporting implementations on Metric Studio?

[CognosPaul]

I've been tasked with setting up a library of reports on a metrics package. The requirements also include a custom data entry page for the KPIs.

The problem is that the metric package itself seems to be extremely inefficient. When digging into the package I discovered that the business layer is made of views, on top of views, on top of views (some going more than 5 layers). This means that a small report can take over 5 minutes to run.

I've tried building a cube based on the package, but that takes close to 12 hours to run.

Can anyone share how they've dealt with this issue, if at all? Is the standard practice to use only the reports and dashboard objects that come with Metric Studio, or is there some sort of reporting package I'm unaware of?

[RobsWalker68]

Hi Paul,

I usually use the framework model found in cognosinstalllocation\temp\metricstudio\Yourmetricstudioapplication\fmprojecttemplate.cpf and then change that as necessary to create the reporting package I need.

Also you may want to look take a look at the scripts below to see if these with improve your performance

"IBM Cognos 8 provides a script called cmm_update_stats that updates your metric store database indexes, which improves performance. Typically, you use this script before or after loading data when the volume or distribution of data has changed significantly. For example, performance may improve if you run this script after increasing the number of scorecards from 100 to 1000.

Steps
1.Ensure that there is no activity in the metric store database.

2.Go to the following directory:

c8_location\configuration\schemas\cmm

3.Go to the appropriate database directory.

4.Depending on the database type, run one of the following scripts from the command line:

◦For Microsoft SQL Server or DB2:

cmm_update_stats host_name metric_store_name Admin_user_name password

◦For Oracle:

cmm_update_stats metric_store_name Admin_user_name password"

Kind Regards

Rob

[CognosPaul]

Interesting. I'll have to try that.

What I've done so far was analyze the views to determine which tables they use, and recreate the joins as needed in a new package. I discovered that the views include several cross joins. When I removed them the improvement in speed was phenomenal; one particularly complex report went from a 10 minute runtime to ~4 seconds. I also created a new data source connection. Using the original triggered a SP every time a session started.

[RobsWalker68]

Hi Paul,

It's been a while since I've played around with the metric framework but from what I can remember your right there is a lot of chaff you can remove/improve.  You could also consider extracting the data to seperate reporting store if need be and allowing yourself a bit more freedom in setting up appropriate report data structures and indexes.

Kind Regards

Rob