Level based Hierarchy

[Deleted User]

Kinda happy I was offline for some hours and Thomas took over because....logical star conforming is the way to do it.

ark86

Hello everyone,

Sorry couldn't get a chance to reply to this post.

Bad news is that i'm still suffering to implement 'Level Based Measures'. So here's what I've done. I've made four separate and simple tables to understand the concept and I'll list my steps in detail

I made following 4 tables (2 dimensions and 2 facts)

Then i imported these tables in OBIEE. So my physical diagram looks like this

so D2 is a conformed dimension while D1 is not. Now my target is to set content level of F2 to 'Total' for D1 dimension. So following is my BMM diagram

If you'll say that F2 needs to link to D1 in BMM then my concern is that this is just one simple example. You cannot make single big ideal star schema in a big project where different facts and dimensions don't relate to each other. Level based measures seems to be the answer to me that'll simply display unrelated amount of fact instead of omitting it.

Anyhow, let me show you the level based hierarchical dimensions I made of D1 and D2

Then i set the content level of both facts

For F1

For F2:

I think F2 amounts shouldn't disappear after selecting Total Level of D1 inside F2 LTS. But F2 measure disappeared as soon as i included D1

Sorry for the long post but I just want to be sure that I'm not missing some simple point to nail it.

Thanks.

Regards,

ARK

Thomas Dodds

Your logical model diagram looking like your physical diagram is the problem ...  You should one logical fact table with both physical facts as LTS and set levels appropriately.

"You cannot make single big ideal star schema in a big project where different facts and dimensions don't relate to each other. " .... Oracle BI Apps has ONE BMM (Core) with hundreds of fact (with multiple LTS) and dimension logical tables in addition to hundreds of logical hierarchies. Conformity and non-conformity existing in harmony.  So, yes, it can be done.

[Deleted User]

Why is 1A_F2 not tied to 1A_D1 in the logical model? How should the system ever relate these entities then if you don't tie them together?!

ark86

Hi, Thank you for your reply.

Now i made a perfect star by combining both facts

now both facts are connected to both dimensions logically.

But still F2 amounts disappear when D1 attribute is added.

Thanks.

[Deleted User]

If you combine Logical Table Sources rather than leaving it as separate logical facts then you must set the content level for fact columns as well.