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

OBIEE 12.2.1.3 [nQSError: 14025] No fact table exists at the requested level of detail:

Joe_obieeoas

Hi Gurus,

i have two fact tables (Fact A and Fact and two dimension tables (Dim A and Dim in BMM model.

Dim A is joined to Fact A. Dim B is joined to Fact B. Here Dim A or Fact A are not joined to Dim B or Fact B.

Now when i pull a column from Dim A and another column from Dim B, i am getting the error

"[nQSError: 14025] No fact table exists at the requested level of detail:"

Please throw some suggestions on fixing this.

Thanks in advance.

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

You said it yourself: Dim A is not connected in any way to Dim B, not even indirectly via a Fact table.

By selecting a column from Dim A and Dim B in the same analysis: how is the tool supposed to build a query for the 2 tables if they don't have any form of connection?

If Dim A and Dim B can't have any kind of connection, you should not try to use them together in the same analysis because it isn't possible.

What kind of result were you expecting? How a value from Dim A could be on the same row of a value of Dim B?

The answer will tell you what modelling you missed in the RPD. Or confirm that you were doing it wrong and a value of Dim A can't be on the same row of a value of Dim B.

Joe_obieeoas

Thanks Gianni, but i don't see any common columns between the tables Dim A and (fact B or Dim OR Dim B and (Fact A or Dim A) so that i can create joins.

Wonder if adding Dim B as an LTS for Dim A make it work !!??

Thanks.

Gianni Ceresa

You are taking the problem from the wrong side...

Think about the meaning of the data inside Dim A and Dim B : how can a value of Dim A share the same row as a value of Dim B if there is no connection between the 2?

If you had to write a SQL query by hand, what would it be like?

And again, if your answer is that you can't write a SQL for this because it doesn't make sense (because there isn't any connection between the 2), it simply means that what you try to do is wrong. Build 2 analysis and display them as you want, but trying to have values of Dim A and Dim B on the same row doesn't have any meaning and is functionally wrong.

Joe_obieeoas

Now i identified common columns between Dim A and Dim B tables and created a join between them in physical layer. So do i need to create business model again so that it assumes the new join created in physical layer OR is there a way i could save all that rework so that existing business model assumes the new join created in physical layer ?!

Thanks again Gianni..

[Deleted User]

https://community.oracle.com/tech/apps-infra/discussion/4504034/obiee-12-2-1-3-nqserror-14025-no-fact-table-exists-at-the-requested-level-of-detail

Ok let's take three steps back and look at this conceptually again. Take your use case and draw it as a picture of it. Similar to this:

Dim B in the middle is the only one that's conformed, meaning that's the only one where both facts understand how to work with it.

Dim A and C are non-conformed.

Now draw a picture of what you want to be able to do. For example be able to use attributes from Dim A in an analysis together with Fact B with Dim C like this:

That is a logical relationship. NOT a physical join! You will never ever have a physical join since physically they don't have anything to do with each other.

The magic in order to make this work then happens in the fact Logical Table Source Content Levels and the measure Content Levels.

Now you can drag over attributes from Dim A in an analysis with Fact B.

Joe_obieeoas

Thanks Chris, but let me give a quick overview. Below is how my 2 dim tables and 2 fact tables are joined in BMM.

Dim A -----join------- Fact A

Dim B -----join------- Fact B

Now when i pulled columns from Dim A and Dim B, i faced "[nQSError: 14025] No fact table exists at the requested level of detail:"

In the BMM both Dim A and Dim B has multiple tables under LTS. Now i navigated to the two physical tables and created a join between one of the source table of Dim A and a source table of Dim B.

My question at this point is should i created business model again (drag tables from physical layer and drop in BMM) so that this time the join between Dim A and Dim B will be carried to BMM (or) is there a way i can avoid all that rework and still bring that join to the BMM layer.

Hope i am clear now.

Thanks.

[Deleted User]

Please read what Gianni and I have written very attentively. Nobody suggested any joining between the dimensions. That's not how data modelling in the RPD works. Read our inputs. Think about our inputs. Stop trying to force your idea of modelling reality on that RPD.

Gianni Ceresa

In the BMM both Dim A and Dim B has multiple tables under LTS. Now i navigated to the two physical tables and created a join between one of the source table of Dim A and a source table of Dim B.

Are you sure that this new join isn't going to generate different queries with wrong results for existing analysis?

The way you described your problem, Dim A and Dim B had nothing to do together in the same analysis. They weren't supposed to be there together for good reasons: there wasn't any connection between the 2.

You now added a join instead of challenge your wish to get them together: did you evaluate the side effect of that new join on the existing content?

Based on how your described the case, that join should NOT exist. These 2 tables should NOT be in the analysis. If they were meant to be connected, you wouldn't have to look again and again before to find what could maybe be the connection between the 2.

That's why I say that these 2 tables should NOT be in the same analysis.

If you persist in adding a join, carefully analyze the consequences of that and all the possible side effects. Remember the tool generates queries based on the model and nothing in the existing content is preventing the tool from using that new join (that's the power of the 3 layers that fully decouple the pieces across the layers).

Joe_obieeoas

Hi Gianni/Chris,

The logical tables are as follows. Dim A has data about various courses names and the institute that provides them. Dim B has details of the instructor for the courses. So if i navigate to physical layer, i could find a column called course number (6 digit code) that is common between the two tables.

Now what the user is trying is create an analysis with provider name (institute), course name and instructor name. As per the details in above para, definitely the two tables should be related. Problem is the previous person who actually created the business model might have not foreseen this type of request from the user and now that i created that join in physical layer, i am looking for a way on how to make use of that join in business model (should i recreate the model ?! )

Chris's diagrams are useful but in my dimension's LTS, content tab, aggregation content, group by --- is defaultly set to "column" and it is greyed out and also in my fact column properties, "levels" tab is greyed out.

Thanks.

[Deleted User]

I would suggest a training on how the RPD works and what is what in there.

https://docs.oracle.com/middleware/12211/biee/BIEMG/GUID-9BA0DED0-83DD-4326-82E5-4A607B75952A.htm#hpp_l_advanced

"To use a source correctly, the Oracle BI Server has to know what each source contains in terms of the business model."

The FACT logical table source is what you need. You can't tell a dimension "you contain information on level X of another dimension". You can read LTS content levels like a sentence and that sentence has to make sense:

"My fact source for <<Revenue>> contains information on the detail level of the <<Client>> dimension and on the year level of the <<Time>> dimension."

This makes sense.

"My dimension source for <<Time>> contains information on the SKU level of the <<Product>> dimension."

This makes no sense at all.