Cross Drill level based measures..

Hesh

Hi,

OBIEE 12C version - 12.2.1.4.0

I have set of reports which uses measures from two different Fact tables (cross drill) .

Both Fact tables have two dimensions in common (Time--> Date key level & Geo Location).

There is another Dimension exists in both Fact table but at different levels one Fact is at SKU level another Fact is at PRODUCT level.

I have read somewhere that we need to create Level based Measures in order to facilitate the cross drill. in that sense I believe in my case I need to create measures at PRODUCT level for the Fact table which is having data at SKU level. 

https://blog.redpillanalytics.com/obieecross-drilling-where-did-the-full-outer-join-go-31b5622ca381

But the thing what I cannot understand here is, if I create Level based Measures measures at PRODUCT level, those will be static throughout reporting! irrespective of what level of hierarchy you add to the Analysis.

Otherwise what I believe I have to create Level based Measures at each Level of the Dimension? not sure if that make any sense?

Please advise..

Regards

Hesh

[Deleted User]

1.) "analysis". Not "report". A "report" is a BI Publisher object and that's a different product.

2.)

Hesh wrote:I have read somewhere that we need to create Level based Measures in order to facilitate the cross drill.

Where?

It's not "facilitating" it's "making cross-fact queries along non-conformed dimensions possible in the first place."

Hesh wrote:But the thing what I cannot understand here is, if I create Level based Measures measures at PRODUCT level, those will be static throughout reporting! irrespective of what level of hierarchy you add to the Analysis.

a) That's why it's called a "level-based measure"

b) it will still react dynamically to everything around it - everything it can be analyzed by that is. Of course not the non-cornformed dimensions.

For all *non*-conformed dimensions, the measure must be at the "Grand Total" level of said non-conformed dimensions. Also the LTS of the fact table must be set to the "Grand Total" of the non-conformed dimensions.

Hesh wrote:not sure if that make any sense?

That's not the issue. The issue is rather that you may have an incomplete picture of how modelling with multiple stars and handling of non-conformed dimensions works.