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.