Oracle^® Business Intelligence Presentation Services Administration Guide > Integrating Oracle BI Presentation Services into Corporate Environments Using HTTP >

Example of an Oracle Business Intelligence Third-Party SQL Tool Integration

This section illustrates the requirements for integrating a third-party SQL tool with Oracle Business Intelligence by describing an example integration, using Microsoft Access. Because Oracle Business Intelligence is designed as a middleware platform for enterprise data access and integration, common report writers and business intelligence tools can communicate natively with the Oracle BI Server.

Most third-party SQL tools require the user to include join conditions within queries to avoid cross-joins. A cross-join occurs when a request does not have a WHERE clause, which, in turn creates a Cartesian product of the tables involved in the join. The size of a Cartesian product is the number of rows in the first table multiplied by the number of rows in the second table.

To integrate Microsoft Access with the Oracle BI Server, the Oracle BI Server Administrator must expose the keys within the Presentation layer of the Oracle BI Administration Tool.

Example of integrating a third-party SQL tool

1. Drag and drop the keys from the Business Model and Mapping layer to the Presentation layer and save the repository.
2. Open Microsoft Access, select the option Blank Access Database, type the name siebel-analytics.mdb when prompted, and click Create.
3. After creating the new Microsoft Access database, right-click in the white section of the screen and select Link Tables.
4. From the Files of Type drop-down list box, select ODBC Databases.

   The Select a Source Dialog appears, and prompts you for a Data Source Name.

5. Click the Machine Data Source tab, locate the Analytics_Web DNS, and click OK.

   The Oracle BI Server requires a login.

6. Type your user ID and password.

   The Import Objects dialog box appears.

7. Click the Select All button, or highlight the desired logical tables from Oracle Business Intelligence.

   The import may take a while to complete.

8. When the import completes, right-click in the white section of the screen and select Relationships:
   1. Add the desired tables and drag and drop the keys from the dimension tables (Period, Market, Product) to the fact table (Sales Measures).
   2. Drag and drop Period Key over the perkey column, and repeat for each corresponding key to create the joins.

      Now, you can test and run a request.

9. Select Create query in Design view from the Queries button:
   1. Select Markets, Products and Sales Facts.
   2. Add Region, Brand, Units and Dollars, respectively, and then click Run.