I have two physical tables that are related on a 1 to 1 basis based on a natural key. One of these tables is already part of my RPD file (actually, it is the W_EMPLOYEE_D from the Oracle BI Applications). The second table contains additional employee attributes from a custom table added to the data warehouse. Unfortunately, I don't seem to be able to display ANY data from this newly added custom table! I'm running on OBIEE 126.96.36.199.
Here's what I've tried to do. Lets call the original table E1 and the new one E2. E1 is part of the repository already, and has functioned perfectly for years.
- In my physical model, I have imported E2 and defined the join between E1 and E2.
- In my logical table for E1, I've mapped E2 to E1 (E2 appears as a source), set up an INNER JOIN in the joins section for E1 and added the attributes from E2 in the folder
- In the SOURCES for this logical table, I've set the logical level of the content for E2 appropriately (detail level, same as E1)
- In my presentation folder for E1, I've copied in the attributes from E2 that were included in my logical table
Consistency check runs smoothly, no warnings or errors. Note: E2 contains hundreds of rows, all of which have matching records in E1.
Now, when I create an analysis that includes only an attribute sourced from E2, I get a single row returned, with a NULL value. If I create an analysis that includes one attrribute from E1 and one from E2, I get all the valid E1 records with data showing, but with NULL for the E2 attributes. Remember, I have an inner join, which means that the query is "seeing" E2 data, it is just choosing not to show it to me! Additionally, I can grab the query from the NQQuery.log file - when I run this SQL in SQL*Developer, I get PERFECT results - both E1 and E2 attributes show up in the SQL - the query engine is generating valid SQL. The log file does not indicate there are any errors either; it does show the correct number of rows being added to cache. If I create a report that includes attributes from E1, E2 and associated fact metrics I get similar results. The reports seem to run fine, but all my E2 attributes are NULL in Answers. I've verified basics, like data types, etc. and when I "Query Related Objects" in the repository, everything looks consistent across all 3 layers and all objects. E2 is located in the same (Oracle) database and schema as E1, and there are no security constraints in effect.
I've experimented with a lot of different things without success, but I expected that the above configuration should have worked. Note that I cannot set up E2 as a new separate dimension, as it does not contain the key value used to join to the facts, nor do the facts contain the natural key that is in both E1 and E2.
Sorry for the long post - just trying to head off some of the questions you might have.
Any ideas welcomed! Many thanks!
I would like you to re-check on the content level settings here as they are the primary causes of this kind of behavior. You could notice that the same information might have written down in the logical plan of the query too.
Also, as per your description
"In the SOURCES for this logical table, I've set the logical level of the content for E2 appropriately (detail level, same as E1)"
I would like to check on this point again, as if you had mapped E2 to E1 in the same logical source with an inner join, you would get to set the content level at E1 levels themselves but not E2 (Now, that E2 would become a part of the E1 hierarchy too). This might be the reason, the BI Server is choosing to elimiate(null) the values from E2 too (even you could see them in the sql client)
The sources tab definitely was an issue earlier, but I did set it prior to posting, I double-checked it just in case and it is definitely what I need it to be. After a server reboot (hardware maintenance, not associated with this issue), I'm getting valid results now. Not sure where the issue really was, but all is well :-) I did end up changing the join from inner to left-outer, but I don't believe that this was the root issue.
Thanks for responding!