Category Archives: Metrics

Using aggregate tables produce incorrect Year-to-date at the lowest levels.

Applies to:
Business Intelligence Server Enterprise Edition – Version: 7.9.5 [AA 1900] – Release: V7
Information in this document applies to any platform.
Symptoms
The user is modelling Time Series functionality (Todate) with aggregated dimensions and aggregated facts using BI Server 10.1.3.3.2.

You have created aggregate tables. You have both Logical Dimension and Fact tables mapped to the Aggregate tables.
In Answers you have built a report using the Dimension Hierarchy, which has the measures Sales and Sales YTD. When you drilldown in the report, the measures show the correct figures, until you get to the lowest level where the Sales YTD results are incorrect.
Cause

The cause of this incorrect aggregation when you drilldown to the lowest level of the hierarchy has been determined to be an incorrect sql being generated. The incorrect sql generated lacks the
SUM..GROUP BY

The main difference is that when you are at the lowest level of the aggregate table it doesn’t use “SUM..GROUP BY” in it’s core query.

The issue has been determined to be a Bug, as per Engineering who reviewed the issue.

Solution

However a workaround was provided for this specific customer scenario that resolved the issue. With the implementation below which engineering tested and verified the SUM and Group by was generated in the sql when drilling into the lowest level of the hierarchy.


I created a dimension that I just created in the business model and added the additional dimension to the content filter for the problem fact source. That resulted in SQL generation with a SUM clause. My workaround was used just for the problem query submitted. I did not even join the new dimension to the fact table.

So I would recommend creating a virtual dimension in the physical and business model layers. It should not be exposed in the presentation catalog.

Cache persistence

There’re a few well-written articles on about OBIEE caching configuration and strategies already. However, I’m posting a solution that I’ve found while investigating an unexpected behavior with tpl cache files disappearing for no reason (no updates in polling table, no manual purge, no BI services restart). Here’s the solution from Oracle:

The behavior was resolved when customer modified the refresh interval for the Repository variables that exist in their Repository.
When a dynamic repository variable is updated, cache is automatically purged. This is designed behavior. Cache will be invalidated (i.e. purged) whenever the initialization block that populates dynamic repository variable is refreshed. The reason that refreshing a variable purges cache is that if a variable was used in a calculation, and the variable changed, then cache would have invalid data. By purging cache when a variable changes, this problem is eliminated.

Since this is the designed functionality, Change Request 12-EOHPZ3 titled ‘Repository variable refresh purges cache’ exists on our database to address a product enhancement request. The workaround is to go through the dynamic repository variables and verify that the variables are being refreshed at the correct interval. If a variable needs to be refreshed daily, there may be a need to set up a cache seeding .bat file that runs after the dynamic variable has been updated. If the cache seeding .bat file runs prior to the refresh of the dynamic variable refresh, then the cache will be lost.

I think they should try to cover similar thing in OBIEE documentation.

How to create a measure from data that is stored in a dimension table.

Sometimes, we need to define an Aggregated Measure based on a Dimension Table.

If aggregated calculations are performed directly from a dimension logical table field, an error similar to the following will appear:

A general error has occurred. [nQSError: 14026] Unable to navigate requested expression: ). Please fix the metadata consistency warnings.

To resolve this type of error, put the measure indicated by the error message in a fact table object.

To define an aggregated measure of a dimension table, complete the following steps:

– Create a new fact logical table with the physical dimension table as source.

– Include all fields that should be aggregated as a measure of this new fact object.

In the Siebel Analytics version 7.5.3 repository, a good example is the Fact – Campaign Metrics fact table that is based on the W_PRG_CAMP_D physical table: