Level based dimension measure doesn't work when adding most detailed atribute

Jan Pilar

Hi all,

I'm using OBIEE 11g.

Please kindly advise me how to overcome the problem I've encountered (even using some work around solution etc.).

I'm in control of metadata so the proposed solution could be done through some changes in Admin Tool or on report level as well.

The report I'm building contains records of groceries stock level.

So there is one fact table 'STOCK LEVEL' and mutiple dimensions: time dimension (of course), article, merchandise structure and other.

Merchandise structure dimension contains level-based hierarchy which represents tree structure of article in the manner described in the figure # 2 (example: ALL -> GROCERIES -> MEAT -> BEEF -> beef burger).

Following diagram views only relevant objects in business model (figure #1).

figure #1:

On the physical level the Article table and Merchandise structure table are connected.

Article contains link (via key) to the lowest level of Merchandise structure. As for the example 'beef burger' belongs to BEEF group (LEVEL 3).

Update: Even if I'd removed the link beween Article and Merchandies structure table on physical layer the result is the same (in the wrong way).

• I have measure named AVG turnover time (LEVEL 2) which is fixed to LEVEL 2 of Merchandise structure hierarchy (so it's a level based measure).
• There is also measure named AVG turnover time which is not level based so it's calculates average turnover time on any level (from grand total level to most detailed granularity, which is one specific article ex. beef burger).

When I test this behavior in Oracle BI Answers everything works alright (figure #2 - 1st table).

Until I add a the most detailed attribute to the analysis - the article name (figure #2 - 2nd table) because I want to filter one specific article.

Then the value of AVG turnover time (LEVEL 2) measure and the value of AVG turnover time measure are the same:

level based measure is no longer fixed to LEVEL 2 and views values of most detailed detailed granularity.

Is it possible somehow achieve behaviour described in 3rd table of figure #3 ?

So that level-based dimension measure keeps its values regardless more detailed atributes.

figure #2:

Any advice is much appreciated.

Thank you,

Jan Pilar

[Deleted User]

Hi Jan,

Just quickly two things:

1.) Which "11g"? There were more than 50 versions of 11g.

2.) Do you talk about doing a "report" or an "analysis"? "Report" is a BI Publisher object. "Analysis" is am OBI Answers object.

Jan Pilar

To be more specific:

1) I'm using Oracle Business Intelligence 11.1.1.7.140527

2) By "report" I meant Dashboard containing filterable Analysis (OBI Answers object). It has nothing to do with BI Publisher.

Thank you.

[Deleted User]

Ok, then several things:

1.) Your OBIEE version is outdated and no longer under support unless you pay for it. You should upgrade as soon as possible

2.) Good to know, please use the correct wording otherwise you send people trying to respond on a wrong track.