nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Level based measure query formation issue

S Vamsi

Created a level based measure which is present in two logical hierarchies when this level based measure is used in report along with some columns which only belongs to one logical hierarchy but still report query is showing that the level based measure is over partitioned by key columns from both logical hierarchies instead of only one hierarchy from which columns are used in the report.How to restrict the report query to use the only key columns of logical hierarchy from which columns are used in the report along with level based measure?

Find more posts tagged with

Accepted answers

All comments

Robert Angel

If I think you are asking what I think you are asking then you need to use sum ... by .. in your calculation as an alternative to the level based measure.

SUM("Sales Facts"."Amount Sold" BY Customers."Cust Id")

Note: level based measure only guarantees that the measure is "always calculated to a specific level of aggregation".

Note also: if you can provide some more detail on your business model then we might be able to provide a better solution

S Vamsi

Thanks Robert Angel for your response.

INVENTORY_ITEM_ID is key column for Dim Category Hierarchy and ORGANIZATION_ID is a key column for Dim Organization Hierarchy.ITEM COST is the level based measure which is present in both these hierarchies at its respective granularity level of key column defined in hierarchies.

Columns used for building report are ITEM COST and some columns from Dim Organization so what i am expecting is ITEM COST should show data at ORGANIZATION_ID granularity level but OBIEE 11g report query is showing as below.

SUM(T25276.ITEM_COST) over (partition BY T567772.INVENTORY_ITEM_ID, T567772.ORGANIZATION_ID)

OBIEE 12c Report query is showing as expected below.

SUM(T25276.ITEM_COST) over (partition BY T567772.ORGANIZATION_ID)

Robert Angel

And what was the level based dimension / level of granularity that the measure should be reflecting? - from your comment - if I understand you correctly - organization id??

Robert Angel

And am I understanding you correctly, is your measure created along these lines?

https://www.kpipartners.com/blog/bid/185891/Using-Level-Based-Measures-in-OBIEE

S Vamsi

And what was the level based dimension / level of granularity that the measure should be reflecting? - from your comment - if I understand you correctly - organization id??

yes, ITEM COST should show data at organization id level of granularity only as report is build on columns from Dim Organization only.

ITEM COST measure column is having logical level set for Dim Category and Dim Organization.What i am expecting is if i use any column from Dim Category in report then this measure should show only at granularity level of INVENTORY ID similarly if i use any column from Dim Organization in report then this measure should show only at granularity level of ORGANIZATION ID but instead this measure is using both ORGANIZATION ID and INVENTORY ID in the report query even though i use columns from only one of the dimensions in 11g OBIEE.

Is it a bug in 11g OBIEE?

Robert Angel

No, more probably something is amiss in your business modelling, what is the content level set for on that measure?

- is it detail against everything?

- is there any row level security that might force item id into the query?

S Vamsi

- is it detail against everything?

Yes

- is there any row level security that might force item id into the query?

Please help me out to solve this issue.

Robert Angel

What about the physical joins, if you select the fact folder and the dimension folder that has the organisation in it and then use the rpd query tool to see the underlying physical tables, what is the result and what are the joins, is item 'in the picture at all'?