Level based Hierarchy

ark86

Dear Fellows,

I've seen so many posts related to it but somehow i'm finding it difficult to nail this concept. So kindly help me here

I'm trying to implement following in BMM of OBIEE 11g (made this diagram only for this post)

First I create an analysis involving following

1. Claim_ID

2. Workshop_Name

3. Workshop_Amount

Everything works fine since Claim_Workshop_Details_Fact is connected to both dimensions (i.e. Claim_Dim and Workshop_Dim).

Now i add Amount from Claim_Payment_Details_Fact which disappears since it can't be grouped to Workshop_Name.

To make this work, I created level based hierarchy of Workshop_Dim and Claim_Dim (although they don't contain any hierarchy here) by simply clicking Create Logical Dimension > Dim with Level-Based Hierarchy. Then i set content level of fact tables as following

Claim_Workshop_Details_Fact:

     - Claim_Dim -> Detail Level

     - Workshop_Dim -> Detail Level

Claim_Payment_Details_Fact:

     - Claim_Dim -> Detail Level

     - Workshop_dim -> Total Level

As per my limited knowledge, now it should work fine but it doesn't.

Can anyone help me with this?

Thank you

Regards,

ARK

Felipe_Idalgo

Hi,

Did you refresh your metadata and re-run your analisys after RPD change?

Felipe Idalgo

ark86

Hi,

Yes i did. A number of times in fact.

Let me explain what my level based hierarchy contains

     - Claim_Dim -> Detail Total -> no key

     - Claim_Dim -> Detail Level -> key = Claim_ID

     - Workshop_Dim -> Detail Level -> no key

     - Workshop_Dim -> Detail Level -> key = Workshop_ID

Thanks.

Regards,

ARK

ark86

Dear @Christian Berg,

I asked a similar question before where you helped me. Can you please look into it and comment.

I'll be grateful. Thank you.

Regards,

ARK

[Deleted User]

If that diagram is your actual model then it will not work since your Workship dimension must be tied to your Claim Payment Fact as well in the logical model.

ark86

Thank you for your input. But Workshop is not related to Claim payments at all.

I'm trying to follow below approach as it addresses the exact problem i'm facing

https://blog.daanalytics.nl/2010/05/31/multiple-fact-reporting-on-non-conforming-dimensions/

I don't want to join multiple tables in logical tables as i'm making an information model and it gets very tricky when more business areas are added to RPD.

Please share your thoughts over it.

Regards,

ARK

Thomas Dodds

If this diagram is your logical diagram -- then @Christian Berg is correct ... it won't work.  Go back through the link you are trying to follow and note that your diagram looks like the physical diagram ... the logical one needs to look like a star, else OBI server is going to choke and give you results like you are seeing.  You are close, but you are missing the key step where you force the logical star

ark86

Hi Thomas,

Actually I've already implemented this by forcing logical stars. Now i was trying to learn best practices and thought there might be a better approach.

I think i'm finally getting how this will work. In my semantic layer, I've tables with first level facts joined to second level facts and then joined to third level facts. So, i guess, the right approach is to make one logical table (by adding multiple LTS) joined with all dimensions and then set content level of each LTS correctly.

do I make sense?

Thank you for bearing me and I really appreciate it. You guys have been a great help.

Regards,

ARK

Thomas Dodds

"I've tables with first level facts joined to second level facts and then joined to third level facts" -- not sure I follow you ... you should never have a fact joined to a fact ...

2 basic rules:

1. every measure has a dimensional context

2. all dimensional attributes are related through a fact (for dimensional browsing 'factless' facts are often set up so as to not break this rule)

Thomas Dodds

"Actually I've already implemented this by forcing logical stars. Now i was trying to learn best practices and thought there might be a better approach."

the only practice is to conform to logical stars ... there is no other way.

ark86

I've all the required info and will update this post tomorrow.

A big thanks again

Regards,

ARK