Toad allows you to create a connection to an Oracle Business Intelligence Enterprise Edition (OBIEE) data source. OBIEE is one of the business intelligence data sources supported by Toad.
Read this topic to learn how Toad maps Oracle Business Intelligence objects and to review some considerations and limitations before working with OBIEE data sources.
To create a connection to an OBIEE data source, see Create Oracle Business Intelligence Connections.
When you connect to an OBIEE data source, you are connecting to the OBIEE Presentation layer. You are not connecting to the underlying Oracle database objects. The Presentation layer is made up of Subject Areas, categories, and columns.
When you create a new connection to a Business Intelligence or NoSQL data source, Toad automatically maps the data source objects. Toad gathers metadata from these objects which is cached (in a hidden table) and retained between sessions for one week. After one week, all objects are automatically refreshed.
During this initial mapping process, Toad maps only metadata. The actual data in each column is mapped as needed, such as when you execute a query.
Manually refresh object metadata
You can manually refresh object metadata at any time. In the Object Explorer, right-click an object and select Refresh All.
The following considerations apply when working with Oracle Business Intelligence (OBIEE) data sources.
|Table relationships||What you see represented in Toad are OBIEE presentation layer objects, and they do not have exposed keys. The relationships between the source data tables are managed in the background by Oracle.|
|Subject Area representation in Query Builder||After dragging a Subject Area to the Query Builder diagram pane, you will notice that there are no key icons to represent key columns and no connectors to represent the relationships between tables. The relationships between tables are managed through the Oracle back end. When you execute a query, the joins between source data are handled by Oracle in the background. So, you do not need to draw relationships before building a query.|
|Building a query||
To build a query, drag a subject area to the Query Builder and select columns.
|Object Explorer||The Object Explorer displays the OBIEE Subject Areas and Queries you have permission to access for that connection.|
The Database Explorer's Columns tab displays OBIEE columns grouped by category (OBIEE folder and sub-folder).
In the Data tab, categories (folders and sub-folders) are listed in the left pane. Select a category in the left pane to display the data in the right pane.
|Max character limit||Toad maps up to 255 characters per VARCHAR data field.|
|Database Diagram||You can drag a Subject Area to the Database Diagram to view it. But no relationship information is displayed. The relationships between the source data are managed in the background by Oracle.|
|Export data using query||When exporting data from an OBIEE connection using the Export wizard, you can only export data using a query.|
|Delay when executing query||OBIEE columns and data are mapped as needed. Because of this, you may experience a delay when attempting to view columns and data or when executing a query.|
|Using ORDER BY on "sort order" configured columns||Some OBIEE columns may have a “sort order” configured in the server. When querying this type of configured column using ORDER BY, the resulting order is based on the configured column’s “sort order.”|
|Read-only||Oracle Business Intelligence connections are read-only. You cannot create subject areas, columns, or categories using Toad. You cannot import data into OBIEE data sources from Toad.|