I am being asked if it is possible to set up a cross-database join in a BI Publisher report/data model. I can't find where this would be available in OBIEE after trying to create something like this, so I was wondering if someone in the community would point me to a feature/functionality that can make this work if it exists.
Note: I have set this kind of cross-database join up in the repository/answers area of OBIEE using the "Complex Join" functionality that is available there. However, I can't find anything analogous to "Complex Join" in the BI Publisher/Report area. The data model appears to limit to a SQL query against a single data source. Note that setting this cross-database join up in answers and using the "Oracle BI EE" option to access the presentation layer is not what is being asked here. The organization wants individual users to be able to set up their own cross-database joins between the hundreds of data sources within the company, and they do not want this to have to be created by an administrator in the repository first.
Is there any functionality in BI Publisher/Report/Data Model area anywhere that allows two physically separate databases to be joined on a field (or multiple fields, etc)? Please note that everything about the databases will be different, including the account names and credentials, the connection string for the database, and even the database type (DB2, SQL, Oracle, MySQL, etc). I know how to do this in the repository/answers, but I can't find anything explaining how to do it in BI Publisher.