OBIEE performance suffers with cross-database joins. Your best bet is to federate the one database into the other and have a single source.

"membered" ... put user ids into groups and member your EM BI Roles with groups - not individual user IDs ...

set up your web catalog something like /Shared Folders/Sandbox/Department <-- for department level access to stuff that group wants to share among themselves; use BI roles membred by AD groups to control visibility and content contribution permissions example: /Shared Folders/Sandbox/Finance Roles: BIConsumer BIAuthor…

* To use OBIEE effectively you need to be well versed in dimensional modeling * OBIEE is not a data quality tool

"This question hinges to the fact that we are providing clients to have the option to make modifications to fit their own business needs. " It is ok and good to let business users make their own changes ... however without management of those changes you end up in the situation you are in. Why aren't business users making…

"Does the OBIEE front end offers a functionality to determine who(user) changed it and what was changed on the criteria/Report Tab without asking the back end OBIEE developer to provide this info?" Sounds like a lack of development management. How are development tasks being assigned and completed if no one bu the…

+1 to @Joel Acha Don't accept requirements for which the tool cannot satisfy ... then you don't spend any time on them -- researching a 'fix' for something that isn't broken is a gross waste of time. Make sure whomever is vetting requirements understand intimately the cans/can'ts of OBIEE product suite.

Is this what you are referring to: https://www.rittmanmead.com/blog/2010/08/oracle-bi-ee-11g-lookup-tables-sparse-and-dense-lookups/

table stores all records as a snapshot (in time) ... not just records to show on the report. A good command of OBIEE requires understanding physical data structures to optimize the whole user experience. You introduce a new element: domain ... that obviously has to be part of the snapshot data Database has the most…

https://docs.oracle.com/cd/E35287_01/bia.7964/e35272/anyimp_oracle_apps.htm#BABCECJH

I suspect a physical redesign and a simplification effort to avoid the double column join would help this. Fact should maintain relationship between org, product and time ...

Ralph Kimball dealt in physical architecture - not in the logical workings of OBIEE ... so his points are valid - but with caution.

Physically or logically? Logical snowflake in OBIEE will be your undoing. Physical: 1 - snowflake is smaller in terms of size of data (less data more joins) - star is not normalized (redundant key data); business relationships are maintained through referential integrity in the data; tough to bulk load 2 - star is faster…

You have already showed it: CUST_ID CallsPrevMonth# CallsCurMonth# Total PrevCallTime PrevCallReason RecentCallTime RecentCallReason C001 1 2 3 2017-01-02 12:30 PM A 2017-01-02 2:46 PM B C003 1 3 4 2017-01-05 3:20 PM A 2017-01-05 10:40 AM E Set this up as a snapshot of the baas table at a regular interval (seems monthly;…

Why not specify a preferred drill path from Customer Detail Level to Product Detail Level? Maybe I'm missing something ...

You wait until he starts processing and thinking on his own and then it turns into "WHAT IF?" ... lol

"one more question is there any way to run physical SQL in obiee analytics and create report apart from that" Not sure what you are asking, but it is clear you haven't grasped the fundamentals of the enterprise system you have at your disposal ... Direct SQL: * Answers sends SQL to Database; * Database sends records to…

Study Data Visualization principles ... understand them to the point of being able to critique ANY visualization of data. Great thinkers on the subject are Edward Tufte, Stephen Few, etc ... Having done data visualization for 20+ years, I can tell you @Gianni Ceresa, @Andrew Fomin. and @Christian Berg have given you a…

You can still have domain and reason as prompted values ... the value in the physical structure is PERFORMANCE, reuse and ease of maintenance. So you end up with two physical structures that can satisfy the requirements ... the original one gives the complete history, the new one with the most recent items on the same row…

Build your fact table to be able to answer the question the business is asking ... so you have your base table ... just add a routine on your post-load-processing to pivot the data (databases handle data better than the logical smarts of OBIEE) then model this fact into OBIEE. Even a materialized view in your database will…