Not able to refresh metrics

[Former Member]

We have metrices that are already refreshed for the time period 1/1/2007 to 01/12/2008.

Now we are trying to refresh them for extended time up to 01/12/2009.

We are facing lot of problem while refeshing the metrics (all connection is proper , and repository connection is proper checked)

• while refreshing some of the metrics, it not giving but not locking the time period(gray out time period)

• some of throwing Oracle error like "ORA 00978", "ORA 00978" but the there no problem in universe level . object generating query sucessfully.

• some time metrics refreshing successfully but data is not showing in analytics , (data is avilable in database)

• some time giving invalid identifier for the "date column" but there is no problem in column name and data type.

Kindly suggest the resolution for these immediately. This is very important.

[Former Member]

Hi Devendra,

If you are getting the following error message while refreshing the metric then the solution mentioned below might be helpful for you to resolve the issue.

When trying to refresh a metric in Performance Management (PM), the following error message appears:

"Cannot create query on repository for probe ORA-00978 Nested group by function without group by"

The reason is an issue with how you have designed the universe for aggregation of the measure.

In PM, when you want to create an aggregation like Sum of Cost you can either choose the aggregation in the metric creation user interface (UI) or select sum(cost) in the universe designer itself.

If using the latter, you need to make sure that in the DESC field of the measure you provide AGGR=SUM. This will gray out the front end selection of aggregate functions in the UI by indicating to PM that aggregation is done at the universe level. Failing to indicate this in the DESC field will make PM perform an aggregate of an aggregate which will create SQL errors.

Solution:

1. Import the universe in Universe Designer.

2. Double-click the measure which you are using to create the metric.

3. In the DESC field, enter AGGR=SUM (It may be SUM, AVG, COUNT etc).

4. Save and Export the universe.

5. In PM, click Set up > Universes tab then select the Update metrics and control charts option.

6. On the Metrics tab, click Refresh.

If you still see the error message, delete the metric and recreate it.

I hope this will help you.

Regards,

Sarbhjeet Kaur

[Former Member]

Hi Devendra,

Following solution might help you to resolve the issue.

An aggregate count is defined at the universe level but the object description field does not have AGGR=COUNT defined. This is necessary to indicate that the aggregation is not required to be done at the EPM level.

Resolution

Define AGGR=COUNT in the object description field.

Regards,

Sarbhjeet Kaur