Logical Table Source (LTS) - Content Tab Showing Multiple Hierarchies/Logical Dimensions?

chillychin

Its been quite some time since I last touched OBIEE and I am finding I seem to have either forgotten quite a bit, or I knew a lot less then I thought

In the Business Management (BMM) layer, I was looking at a logical table source.

Specifically I was looking at a logical table source and its "Content" tab

I noticed most logical table sources on the Content tab, there's generally at least one Hierarchy showing up. But I noticed on a fact table, and one other dimension table that there seems to be multiple hierarchies listed under the "Logical Dimension" column as shown below

I was just wondering, how does this "Logical Dimension" column get populated?

I am a bit confused as to how certain tables can have such a long list of Hierarchies, and I'm not sure how these get populated?

Gianni Ceresa

The logical joins drive the list of options you see or not. Because the joins decide what is the role of the logical table: dimension or fact.

Once your dimension has a logical join joining it to a fact table, the content level of the LTS of the dimension only shows the hierarchies of that logical table.

If you look at a fact table you see all the hierarchies built on every logical dimension joined to that fact table.

If you don't have any join, your table doesn't have a defined role and so you see everything there as well. But in this case a consistency check should give you an error as it isn't supposed to exist.

chillychin

Hi @Gianni Ceresa

I think I am still missing something fundamental here

Digging in the RPD I have, I open up a small dimension and its logical table source -> Content tab and I see the following

Its a dimension, so I see the one hierarchy its related to. So its only showing the hierarchies of this logical dimension table.

Going to a Fact table and doing the same navigation I see the following

A much longer list of logical dimension and hierarchies, which I assume is all the logical dimensions joined to this fact table. So far this all makes sense.

But what really confused me is when I check the LTS of one dimension table I am seeing the following

This is labelled as a dimension table, and I dont see the usual # symbol to indicate its a fact table.

But some how even as a dimension its still showing a long list of logical dimension hierarchies?

When I right click on the dimension and go to -> Business Model Diagram -> Selected Tables and Direct Joins I only see it connected to two fact tables on the Many side

So I'm quite confused as to how this one particular dimension is "special" and can some how be connected to so many different hierarchies?

[Deleted User]

"So I'm quite confused as to how this one particular dimension is "special" and can some how be connected to so many different hierarchies?"

It looks like there simply is NO hierarchy for this dimension and the dimension doesn't know just what the halibut it is A logical dimension can only ever have one hierarchy.

Gianni Ceresa

I would change the "can" to a "must": once an hierarchy exists in a business model, all dimensions must have one. You should have a warning if not an error in the consistency check if it isn't the case.

[Deleted User]

https://community.oracle.com/tech/apps-infra/discussion/comment/16777548#Comment_16777548

Yes, true. I meant with regards to "more than one" and that's a "No".

chillychin

https://community.oracle.com/tech/apps-infra/discussion/comment/16777545#Comment_16777545

LOL - Sorry your "dont know what the halibut..." got me 🤣

I double checked the RPD and I think you nailed it on the head.

At first I was thinking like what Gianni mentioned - The dimension table is joined to the fact table, so I would expect only a single hierarchy. So I couldn't figure out what was happening.

But as you said, it looks like this particular dimension is NOT connected to ANY hierarchy - Therefore it was floating around, and was available for any hierarchy to claim. Hence it was showing that long list.

I thought there was some magic setting hidden some where, but looks like it was just not set up at all properly

Thank you!