Reporting on Multiple Factless fact tables

User_IP06I

Hello All,

I have a scenario where I've to build an analysis using two or more Fact less fact tables and one conformed dimension. These Fact less fact tables doesn't have even single measure which can be used in the analysis. They Just have the Attributes such as Gender, DOB, Effective date and Begin date etc. I Joined the two facts with conformed dimension in the BMM and set the Logical level of facts to detail level for Dimension. Then when I'm creating analysis using one fact and Conformed dimension it's working fine but as soon as I add the column from second fact it's giving me the error.

Error Details

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000.  Code: 10058.  [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed:  [nQSError: 14020] None of the fact tables are compatible with the query request Fact. Please have your System Administrator look at the log for more details on this error. (HY000)

I did search Google for  for fact less fact modeling in obiee but they are all mentioning about single fact less fact. So is there any other way I can model Multiple fact less fact tables?

Thanks.

[Deleted User]

Factless facts or any fact...it's all the same. Fact tables which tie to conformed dimensions. Your model is wrong. Plain and simple.

Same counsel I give everybody:

DRAW that stuff on a piece of paper so that you yourself can comprehend what's going on and then correct your model!

Never underestimate how useful a pen and paper can be. Plus we can't help you without knowing the ERD of your model.

User_IP06I

Thanks for the quick response. However I tried the pen and paper thing before posting this. It's pretty much straight forward if they had the measure in facts. To say exactly, they are degenerated dimensions in a fact. Let me be more clear.

Let's say A is a conformed dimension. B is a Fact1 and C is a fact 2. I joined A,B and A,C in Physical layer. Then in BMM I joined Logical Dim A to Logical fact B and Logical Fact C. For Fact table sources I mentioned the logical level of Dim A as Details in content tab. And If I'm creating analysis on Dim A and Fact B or Dim A and Fact C then the reporting is working but when I add a column from the other fact is breaking. The fields which I'm adding in the analysis from three tables are Descriptive fields like Order date, Gender, Age etc. And when ever I add some aggregation(Max or Min etc) to the fields from both the facts then the report is working Good. That means OBIEE is expecting Just the measures from facts when working with multiple facts?

And the query I can write in DB is as following.

SELECT A.COL1, b.COL1, C.COL1

FROM Dim A

RIGHT OUTER JOIN Fact B ON A.Key=B.Key

RIGHT OUTER JOIN Fact C ON A.Key=C.Key

WHERE A.Key IN 'xxx'

Thanks.

[Deleted User]

3051209 wrote:The fields which I'm adding in the analysis from three tables are Descriptive fields like Order date, Gender, Age etc. And when ever I add some aggregation(Max or Min etc) to the fields from both the facts then the report is working Good. That means OBIEE is expecting Just the measures from facts when working with multiple facts?

Ok so then you're just violating a basic rule of dimensional modeling: There are NO attributes in fact tables except for those used in calculations! Move them out into degenerate logical dimensions!

User_IP06I

So You suggest Creating a Logical dimension and Logical Fact with the Same Physical Alias, right?

And I've couple of other question in this scenario. Let's Say For Fact A we created a Logical Dimension (Degenerated) and moved all the Descriptive columns into it. My questions Here is

1. Do We have to Join the Logical fact and Logical Dimension of Fact A in BMM? If yes what will be the logical level, Detail?

2. How can BI server Understands when I Create a analysis from Degenerated Dimension Col and Conformed dimension when there is no join between them in the BMM?

Thanks.

[Deleted User]

Yes

Yes and yes on the detail level

Because it goes over the logical fact table

User_IP06I

Thanks For the Response.