Issue with reporting on dimesion columns only without metrics

2828715

I have 2 fact tables Fact A, Fact B and 5 dimensions ConfDim 1,ConfDim 2,ConfDim 3,ConfDim4,NonConfDim5. Dimensions 1 to 4 are conformed between Facts A and B and Noncomf Dim5 is joined only to FactB.

I have some attribute columns in Fact A and Fact B which I pulled them out in the Bmm layer and created 2 dimensions DimFact A and DimFact B.

All the metrics from fact A are set at total level for NonConfDim5 and DimFactB,so  aggregation for metrics is not an issue and accordingly the design was done for metrics from fact B.

My issue comes up when I am trying to bring in only Dimension columns and without any metrics into my analaysis.

Say my analysis is like

ConfDim 1.colA,ConfDim 2.Colb,ConfDim 3.ColC,NonConfDim 5.ColA,DimFact A.ColA

the analysis fails to return results isuing 2 kinds of errors.

1. . [nQSError: 14020] None of the fact tables are compatible with the query request

Code: 14081. [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references:

2.  A physical query is issued to the database where the query fails throwing the below error.

ORA-01791: not a SELECTed expression

01791. 00000 -  "not a SELECTed expression"

*Cause:   

*Action:

Error at Line: 2 Column: 6

If I remove the distinct from the select clause or try removing the order by clause in physical query it runs fine in DB.

What else can be done to fix the issue.Any suggestions are very much appreciated.

Joel

The dimensions join via the facts in dimensional modelling. If you exclude a fact from your query and just run query against 2 dimensions, the BI Server doesn't know how to join up because there could potentially be multiple paths that could join these 2 dimensions together.

Two ways round this would be to:

1. Add a fact to your query to force the BI Server to use the correct path
2. Add an implicit fact column to your Subject Area in the Presentation Layer which automatically does the above without you adding the column into your query.

2828715

I agree with you and that is the default behavior of the tool. Normally when I am issuing an analysis against dimensions and a metric from each fact table, the Bi server is issuing 2 queries one to each fact and then doing a stitch join to both the result sets. What I am failing to understand is when I am trying to run an analysis using only dimensions, why is the tool unable to throw 2 queries 1 to each fact based on the conformed and non conformed dimensional columns.

I cannot add a implicit fact column as sometimes the results should be coming from Fact A and some should be coming from fact B, but if I am adding implicit fact, I am restricting the query only to 1 fact.

The main problem over here is, the users have adhoc access to the subject area,so they are trying to mix and match all combinations.

Gianni Ceresa

I'm sorry but I would find strange the tool generate 2 queries for a dimensional only query.

What you ask the tool (all the dimensions) doesn't make sense from a logic point of view as you have a multi start model. The tool works at the star level for dimensional only queries, that's why you can define one and only one implicit fact.

For what you are trying to do the correct logical way would be 2 analysis, each with the dimensions of a given star.

Being used for adhoc analysis doesn't change: if users ask something impossible the system will tell them with that error they are asking for something impossible. It's not up to OBIEE to manage that in a way, your users are supposed to know what they have in front of them (or they just retrieve data for fun with no business meaning? I doubt it ...)

In the end you don't have any issue: the data you have allow you do run some queries and some not, "solving" the issue = make queries that are impossible possible would mean to cheat with your data.

Sure you can make a join like 1=1 for all the dimensions to make all of them conformed, all your analysis will work just fine, and all your data will be wrong. I don't think you want that.

So document the model, explain what is possible and what is not possible (from a business point of view) and your users will have to accept it.

nm.Mani

I agree with Gianni, the users definitely understand the flow of business data.

In simple words, we can actually create 2 Subject Areas. one SA with FactA as implicit fact table and the other SA with FactB including non confirmed dim in the same BMM.

If the user wants the data from 2 SAs, they can combine them using "Add/Remove Subject Areas" option in creating the analysis.

Hope this helps,

MM

Thomas Dodds

Dimensional Modeling Laws:

1 - the fact maintains relationship between ALL cross-dimensional attributes

2 - EVERY fact has a dimensional context (grain)

So if you need dimensional browsing (similar to oltp system) then you need an additional fact that provides the maintenance of that relationship -- often referred to as a factless fact (collection of the dimensional keys that specify a relationship between them apart from a 'real' event occuring).  A factless fact preserves the dimensional modeling laws.

Joel

that's a "fact"

[Deleted User]

Aka the "1=1" "solution"