Metric Store View Maintenance

[steven]

Hi Everyone,

I am having no luck with Cognos Support or with Google so thought I would try and pick your brains.

In the Metric Store there are a number of Views that can be used for reporting off of the Metric Store such as:

• MOD_SCORECARD
• MOD_METRIC_HISTORY
• MOD_METRICS
• MOD_SCORECARD_METRICS
• etc..

These views are built from the underlying Metric Store tables and other views. All views are combined with the USER_SESSION table meaning that their row count grows exponentially as the number of Cognos sessions increases over time. For example in my Metric Store I have 200 scorecards however the MOD_SCORECARD view has 55,000 records as it is combined with USER_SESSION information. The view continues to grow over time even though I have loaded no new Scorecards into the Metric Store. The cause of the growth is user interaction with the Metric Store and is on a per session basis.

I am concerned that these views are going to grow so large that they become unusable or could even grind the database to a halt.

Is anyone familiar with a mechanism for controlling the size of these views? i.e. A setting for stopping user session logging in the Metric Store?

Or am I worrying over nothing and the Metric Store will manage the size of the views itself? i.e delete user session information after a period of time or number of records?

Any help would be much appreciated  :)

Cheers,
Steve

[nickhughes]

Hi Steven mate,

I've been through shed loads of issues around similar things in the last year due to a big project where I've been reporting against the metric store. I've got some workarounds which I working great for us. These views are obviously complex for a reason and work for what they are intended for, browsing in MS, but the reports are crap at performing if you have loads of scorecards, we have over 1000! So what I did was created a view against the key tables just looking at the figures I wanted to pluck off. Try this out for size

create view ANALYTICS.CMS_REPORTING_COALESCE as    (
  select      KCL.OBJECT_NM "METRIC_TYPE_NAME",      tpn.SHORT_PERIOD_NAME,     kpi.SCORECARD_ID,     KPI.HOME_SCORECARD_SID SCORECARD_SID,     coalesce(kph.ACTUAL_VALUE,0) "ACTUAL_VALUE",     coalesce(kph.TARGET_VALUE,0) "TARGET_VALUE",     date(tp.start_time_cd) as START_DATE,     tl.level_id "TIME_LEVEL_ID",     SC.OBJECT_NM "SCORECARD_NAME",    psc.object_nm "PARENT_SCORECARD_NAME",     PSC.EXTERNAL_OBJECT_ID "PARENT_SCORECARD_ID",    PSC.CONTENT_OBJECT_SID PARENT_SCORECARD_SID,    kpi.qualifier_id "QUALIFIER"         
  from KPI_PERIOD_HISTORY kph,       TIME_PERIODS tp, KPI,           (   
  select *     
  from content_object_lv     
  where object_type_cd = 'KPICL' and text_language_cd='EN') KCL,        TIME_LEVELS tl,           (   
  select *     
  from time_period_names     
  where language_cd='EN' and name_type_cd = 'default' and short_period_name not like '%TD' ) tpn    ,         (   
  select *     
  from content_object_lv     
  where object_type_cd = 'SC' and text_language_cd='EN') SC,        (   
  select *     
  from content_object_lv     
  where object_type_cd = 'SC' and text_language_cd='EN') PSC                           
  where   kph.TIME_PERIOD_SID = tp.TIME_PERIOD_SID  and kph.KPI_SID = KPI.KPI_SID  and kcl.external_object_id=kpi.kpi_class_id   and tl.time_level_sid=tp.time_level_sid  and tpn.TIME_PERIOD_SID=kph.time_period_sid  and kpi.home_scorecard_sid=sc.content_object_sid  and sc.parent_object_sid=psc.content_object_sid );
Now running this view on its own isn;t amazingly quick but once you started putting filters around the view in cognos it's pretty damn lightening!

Let me know if this helps, you can email me at nick.hughes@gamesys.co.uk and share ideas, there's a shortage of Metric Studio knowledge out there!! :)

Nick  8)

[steven]

Hi Nick,

Thanks for sharing your solution, it's good to hear that it is not just myself suffering from a poorly designed product. My solution to the problem is very similar to what you have mentioned.

I am using a DB2 Metric Store so I replaced each of the views with materialised query tables (MQTs). This allowed me to apply indexes to the keys and remove all of the redundant ticket and multi-lingual data. The increase in reporting performance was over 95% using this method however it feels like a bit of a hack for something that should be "out of the box" functionality.

I am curious to see just how big the views in the Metric Store will get as they are already in the millions of rows. In the end the growth of the views will be dependant on the number of Cognos Sessions against the Metric Store.

Totally agree with the lack of expertise in the Metric Studio/Store space!

Cheers,
Steve

[rj.sehgal]

Hi Guys

I'm facing a similar problem with one of our clients. They want to do reporting on top of the metric store. I'm a bit new to metric studio so do not have much of an idea about the underlying table structures of metric store.  :-[
Any idea where I can get this sort of information ? Also, is there any alternative to this?
Any help would be much appreciated.  :)

Thanks
Rajat