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).
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.
Any advice is much appreciated.