Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
None of the fact tables are compatible with the query request
Hello OBIEE Gurus,
OBIEE 11.1.1.7.160119
I am working with the below business model. The joins from PP_FP_Contacts & PP_FP_Class_Codes to PP_FP_Funding_Projects are many-to-one i.e. for a PP_FP_Funding_Projects row, there could exist multiple rows in Contacts & Class Codes table. Similar relationship on the Work_Order side. Funding_Projects to Work_Orders is one-to-many i.e. one Funding_Project could have multiple Work_Orders.
The reports work when I include columns from FP_Funding_Projects, FP_Contacts, FP_Class_Codes & WO_Work_Orders. If I include any column from WO_Contacts or WO_Class_Codes, I get the below error.
I also get the below error when I switch from WO to FP i.e. the reports work when I include columns from WO_Work_Orders, WO_Contacts, WO_Class_Codes & FP_Funding_Projects. If I include any column from FP_Contacts or FP_Class_Codes, I get the below error.
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 PP_FP_Contacts.Project_Manager. (HY000)
Do I have the model wrong? Am I not having the right joins? Is something missing in the model?
Regards.
Answers
-
Question is: What is your logical model? What are your facts and what are your dimensions? Which physical objects are mapped to which logical table source in your logical model?
0 -
As Christian said the answer is in your logical model. You have a physical snowflake, so depends on how you reduced it to a star in the logical model ...
0 -
Snowflake and/or dual-fact model because orders pretty assuredly are facts as well as playing a role as a dimension for the projects and vice versa.
0 -
Hi Christian, note that he said the diagram was a picture of his business model....so I think you're looking at it. Looks like lots of issues here - not a proper star schema, not using business names instead of physical names, etc.
Scott
0 -
Hey Scott. If that really IS his business model, then consider me speechless regarding all the violations of any good practice or common sense with regards to modeling :-/
0 -
If it's really the business model then the OP maybe did a full drag&drop of the physical layer to the BMM layer and ignored almost all the rules of OBIEE. If it's the case the easiest would be delete the full model and start again paying attention to what is done.
0 -
Obviously
0 -
@Christian Berg, @Gianni Ceresa, @SPowell42
Christian, Gianni, Scott,
Thank you all for your valuable inputs and criticism. We all truly appreciate your contribution to this OBIEE community.
I should have mentioned OBIEE is being used to report on transactional data. Hence probably folks who implemented this didn't have a true facts & dimensions model. So now to deal with whatever model I have, is it possible for me to report without running into this error? Or are you suggesting I should convert this model into a fact/dimension model and then try again?
Thanks again guys for your answers. Please don't deter and get frustrated after reading such questions/models :-)
Regards.
0 -
Physically it doesn't matter what you have. But logically it must be a star.
So your screenshot above - is that really the business model?
If yes than it's wrong. a) it must be a star and b) it should represent a nice human-readable entity relationship diagram (yes, ERDs are still important)
0 -
Yes, that's the business model. The screenshot is from the OBI Admin tool.
0
