This chapter explains the HTTP methods that you can use to integrate Oracle BI Presentation Services into your corporate environment. Oracle BI Presentation Services provides entry points for most functionality exposed in the system. Calls can be made through HTTP using a simple URL syntax or JavaScript commands. This chapter contains the following topics:
Section 6.2, "Referencing Dashboard Content in External Portals or Applications"
Section 6.3, "Using the Oracle BI Presentation Services Go URL to Issue SQL and Pass Filters"
Section 6.4, "Example of an Oracle Business Intelligence Third-Party SQL Tool Integration"
This section describes how to use the Oracle BI Presentation Services Go URL to incorporate results into external portals or applications. It contains the following topics:
Section 6.1.1, "About the Oracle BI Presentation Services GO URL"
Section 6.2.2, "Structure of the Basic Oracle BI Presentation Services Dashboard URL"
Section 6.1.3, "Optional Parameters for the Oracle BI Presentation Services Go URL"
The Oracle BI Presentation Services Go URL command is for use in incorporating specific Oracle Business Intelligence results into external portals or applications. The Go URL is used when you add a result to your favorites, or add a link to a request to your dashboard or an external Web site. It has a number of forms and optional arguments that can be used to control its behavior.
You can post the Go URL as a Form or issue it as a URL. If you are issuing parameters as part of a URL, they need to be escaped properly. You need to replace spaces with plus ( + ) signs, and so on. For example, to pass East Region as a value, type East+Region.
When called from within an Oracle BI Presentation Services screen, such as a dashboard or an HTML result view, the URL should begin with the following characters:
saw.dll?Go
When called from another screen on the same Web server, the URL should begin with the following characters:
/analytics/saw.dll?Go
When referenced from a screen on a different server (or sent through email, and so on), the URL should begin with the fully qualified server name or IP address:
http://server_name_or_ip_address/analytics/saw.dll?Go
To test these commands, you can enter the fully qualified version into the Address field of the browser.
The basic Go URL command needs the full catalog path to the request to execute. It returns the default result view, which is defined in the request.
For example, the following go URL command returns the default result view as defined in the request, where SB2 is the name of the request to execute.
saw.dll?Go&Path=%2Fshared%2FTest%2FSB2
You can modify the behavior of the Go URL command by adding one or more of the following parameters. If an invalid URL is specified (for example, you type a parameter incorrectly), the browser displays a "The page cannot be found" error with the detailed text of "HTTP 400 - Bad Request."
Note:
In parameter descriptions, SB2 is the name of the request to execute.User ID and Password. The user is prompted for user ID and password if this information has been omitted from the request.
This is the format, where uuu is the user ID and ppp is the password:
&NQUser=uuu&NQPassword=ppp
Example:
saw.dll?Go&Path=/Shared/Test/SB2&NQUser=user1&NQPassword=rock
This logs on as user1 with a password of rock, and executes the request.
Link Options. The results will include links.
This is the format:
&Options=x
The x can be one or more of the following letters:
m = Modify Request
f = Printer Friendly
d = Download to Excel
r = Refresh Results
Example:
saw.dll?Go&Path=/Shared/Test/SB2&Options=md This displays results with the links Modify Request and Download.
Printer Friendly. Results are in a printer-friendly format, without the paging controls, hot links, and so on.
This is the format:
&Action=print
Example:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Print
Application Friendly. Results are displayed in an application-friendly format, such as for Microsoft Excel, without the paging control, hot links, and so on.
This is the format:
&Action=Extract
Example:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Extract
The Extract action also acts as a Navigate action (read Section 6.3.2, "Passing Filters to the Oracle BI Presentation Services Go URL Through a URL (Navigation)") so you can filter the results that are returned by the call.
Specific View. This shows an individual result view rather than the default compound view.
This is the format, where xx is the name of the view:
saw.dll?Go&Path=/Shared/Test/SB2&ViewName=xx
Example:
saw.dll?Go&Path=/Shared/Test/SB2&ViewName=Chart
Assuming that the request contains a Chart view named Chart, this displays just the Chart view.
Specific Style. This shows the results using a specified style. If the style does not exist, the default is used.
This is the format, where xx is the name of the style:
saw.dll?Go&Path=/Shared/Test/SB2&Style=xx
Example:
saw.dll?Go&Path=/Shared/Test/SB2&Style=Lime
Result Format. This controls the format of the results.
This is the format, where xx is XML or HTML:
saw.dll?Go&Path=/Shared/Test/SB2&Format=xx
Example:
saw.dll?Go&Path=/Shared/Test/SB2&Format=XML
This shows results in XML.
There are two ways to display all the records in the table:
Set the Rows per Page property on the Table view to 10,000, and then use the basic Go. This is the easier of the two methods.
Issue the following URL:
saw.dll?Go&Path=/users/Administrator/a&Action=Scroll&P5=-1&ViewID=o:go~r:report~v:compoundView!1~v:tableView!1
This section describes how to use the Oracle BI Presentation Services Dashboard URL. It contains the following topics:
Section 6.2.1, "About the Oracle BI Presentation Services Dashboard URL"
Section 6.2.2, "Structure of the Basic Oracle BI Presentation Services Dashboard URL"
Section 6.2.3, "Optional Parameter for the Oracle BI Presentation Services Dashboard URL"
The Oracle BI Presentation Services Dashboard URL is for use in incorporating or referencing the content of a specific dashboard in external portals or applications. It has a number of forms and optional arguments that can be used to control its behavior.
You can post the Dashboard URL command as a Form or issue it as a URL. If you are issuing parameters as part of a URL, they need to be escaped properly. You need to replace spaces with plus ( + ) signs, and so on. For example, to pass East Region as a value, type East+Region.
When called from within an Oracle BI Presentation Services screen, such as a dashboard or an HTML result view, the URL should begin with this:
saw.dll?Dashboard
When called from another screen on the same Web server, the URL should begin with this:
/analytics/saw.dll?Dashboard
When referenced from a screen on a different server (or sent through email, and so on), the URL should begin with the fully qualified server name or IP address:
http://server_name_or_ip_address/analytics/saw.dll?Dashboard
To test these commands, you can enter the fully qualified version into the Address field in the browser.
The basic Dashboard URL command needs no parameters. It displays the user's default portal after authenticating the user.
This is the format:
http://server_name_or_ip_address/analytics/saw.dll?Dashboard&PortalPath="path of the dashboard"
Example:
http://localhost:9704/analytics/saw.dll?Dashboard&PortalPath=/shared/Paint Demo/_portal/Paint Dashboard
You can modify the behavior of the Dashboard URL command by adding the user ID and password parameters. If the parameters are omitted, then the user is prompted for user ID and password information, unless the user chose the option to have logon information remembered when last logged on. If using a Session ID or Ticket, pass it as the NQUser parameter. In the parameter description, SB2 is the name of the request to execute.
This is the format, where uuu is the user ID, and ppp is the password:
&NQUser=uuu&NQPassword=ppp
Example:
http://localhost:9704/analytics/saw.dll?Dashboard&PortalPath=/shared/Paint Demo/_portal/Paint Dashboard&NQUser=user1&NQPassword=rock
This logs on as user1 with a password of rock, and displays the dashboard.
If an invalid URL is specified (for example, you type a parameter incorrectly), the browser returns the "The page cannot be found" error with detailed text of "HTTP 400 - Bad Request."
This section explains how to use the Go URL command to issue SQL, and how to pass filters to be used for navigation. It contains the following topics:
The Go URL command can be used to issue Oracle Business Intelligence SQL. These forms of the Go URL return tabular results. The basic options from &Style= and &Options= can be used here as well.
To issue Oracle Business Intelligence's simplified SQL, include the escaped SQL as a parameter to the Go URL. For example:
saw.dll?Go&SQL=select+Region,Dollars+from+SupplierSales
where the FROM clause is the name of the Subject Area to query.
Alternatively, the command IssueRawSQL can be used to bypass the Web processing and issue SQL directly against the BI Server.
The Go URL can also be used to pass context such as filters to a destination request. This is done by adding additional parameters to the call. You need to make sure that any columns you are passing are set up in the destination with Is Prompted filters, or specific default filters.
The basic syntax of the navigation command is the same as presented in the section Section 6.2.2, "Structure of the Basic Oracle BI Presentation Services Dashboard URL", but with the addition of the Action=Navigate parameter, and then population of the P1 - Pn parameters, as necessary.
By default, you can add up to 100 parameters to the URL. However, you can adjust the number of parameters by modifying the Prompts/MaxPromptedURLParams setting in instanceconfig.xml. For more information see "Using a Text Editor to Manually Update Oracle Business Intelligence Configuration Settings" in the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
&Action=Navigate
&P0=n where n is the number of columns you wish to filter, currently 1 - 6.
&P1=op where op is one of the following operators.
| Operator | Meaning |
|---|---|
| eq | Equal to or in. |
| neq | Not equal to or not in. |
| lt | Less than. |
| gt | Greater than. |
| ge | Greater than or equal to. |
| le | Less than or equal to. |
| bwith | Begins with. |
| ewith | Ends with. |
| cany | Contains any (of the values in &P3). |
| call | Contains all (of the values in &P3). |
| like | You need to type %25 in place of the usual % wildcard. See the examples that follow. |
| top | &P3 contains 1+n, where n is the number of top items to display. |
| bottom | &P3 contains 1+n, where n is the number of bottom items to display. |
| bet | Between (&P3 must have two values). |
| null | Is null (&P3 must be 0 or omitted). |
| nnul | Is not null (&P3 must be 0 or omitted). |
| &P2=ttt.ccc | In this parameter, ttt is the table name and ccc is the column name. If the table or column contains spaces, it must be quoted with double-quotes. Spaces should be escaped as %20, for example, Measures."Dollar%20Sales". |
| &P3=n+xxx+yyy+...+zzz | In this parameter, n is the number of values, and xxx, yyy, and zzz are the actual values.
Note: If the value of P3 begins with a numeric character, the entire value must be enclosed in quotes. For example: saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2=Customers.Region&P3="7West" |
Note:
The settings for &P1,&P2, and &P3 are repeated for &P4-P6, &P7-P9, &P10-P12, &P13-P15, and &P16-P18 as necessary, depending on the value of &P0.This returns records for the East and Central regions:
Saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=eq&P2=Customers.Region&P3=2+Central+East
This returns records for like Regions E....t:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=like&P2=Customers.Region&P3=1+E%25t
This returns the top two regions by dollars sold:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2="Sales%20Facts".Dollars&P3=1+2
This is an example where the number of arguments is not included in the syntax:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2=Customers.Region&P3=Central
Note:
You can omit the number of arguments only if just one argument value is included.This returns records with between 2,000,000 and 2,500,000 in sales:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2="Sales%20Facts".Dollars&P3=2+2000000+2500000
This returns records for Regions beginning with the letter E:
saw.dll?Go&Path=vate&P0=1&P1=bwith&P2=Customers.Region&P3=1+E
This returns records for Regions containing the letter E and having more than 20 million in sales:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=2&P1=cany&P2=Customers.Region&P3=1+e&P4=gt&P5="Sales%20Facts".Dollars&P6=1+20000000
Oracle BI Presentation Services navigation is currently supported from charts, table and pivot table views, HTML views, and external applications and Web pages. The destination search should have filters defined on columns for which it wants to receive context. These can be specific filters or, usually, the Is Prompted filter. In addition to the Table.Column value specifically referenced in the navigation call, all filters from the source request that have corresponding table.columns in the destination, are applied to the destination. Therefore, the appropriate context from a source can be passed to the destination.
Navigation can currently be accomplished using the custom text/date formatter for a column. The central concept is that you add a column you want to navigate from to your search. You then choose Custom Text Format from the properties for the column, and enter HTML that calls one of the two provided JavaScript functions. This technique can be used to perform many actions, including sorting columns, calling custom JavaScript functions, and so on.
Oracle BI Presentation Services includes two JavaScript functions that enable navigation from Table and Pivot views: GoNav and PortalNav. (These functions are located in ORACLE_HOME/bifoundation/web/app/res/b_mozilla/viewhelper.js.) The former handles navigation to a specific search. The latter handles navigation to a specific dashboard. A description of their syntax follows, along with example Custom Text formats that you can use to implement navigation.
Note:
To control the look of the navigable text using the style sheet, Oracle BI Presentation Services is standardized on the class=Nav.GoNav function
function GoNav(event, sPath, sTbl, sCol, sVal, sTarget)
where:
event = event indicator.
sPath = the catalog path of the destination search.
sTbl = the logical table name to filter.
sCol = the logical column name to filter.
sVal = the value to filter by.
sTarget (optional) = "_blank" to open a new browser window with the results.
Sample Custom Text Format for GoNav Call
The GoNav and PortalNav calls can be wrapped in an HTML statement (include the quotes):
[html]"<font class=nav onclick=\"JavaScript:GoNav(event, '/shared/topaz/performance/transaction details','Transaction','Quality','"@"');\">"@"</font>
Table 6-1 explains the elements of this example.
Table 6-1 Elemental Analysis of a GoNav Call
| Element | Description |
|---|---|
|
[html] |
Tells Oracle BI Presentation Services to interpret the following text as HTML. Note that every "less than" character (<) must be preceded by a double quote (") if the intent is to use it in an HTML tag. |
|
"<font |
An HTML tag that a JavaScript call can be attached to. You could potentially use <div>, <span>, <a>, and so on. |
|
class=nav |
The CSS style class used for formatting of the HTML tag. |
|
onclick=\"JavaScript:GoNav('event, /shared/topaz/performance/transaction details','Transaction','Quality','"@"');\" |
The method to call a JavaScript function. When the user clicks on the contents of this HTML tag, then the JavaScript function is called. |
|
> |
The end of the font tag. |
|
"@" |
Instructs Oracle BI Presentation Services to replace the at sign (@) with the actual column value. When [html] is used, the @ symbol must be enclosed with double quotation marks ("). |
|
</font> |
The closing tag to match the <font> tag. |
This example of GoNav places this HTML on the dashboard:
<a href="javascript:GoNav(event, '/shared/topaz/performance/transaction details','Transaction','Quality','Some value');">Click here to navigate to Transaction Details with ''Some value'</a>
PortalNav Function
function PortalNav(event, sPortal,sTbl,sCol,sVal)
event = event indicator.
sPortal = the catalog path of the destination portal.
sTbl = the logical table name to filter.
sCol = the logical column name to filter.
sVal = the value to filter by.
Sample Custom Text Format for PortalNav Call
Make sure to include the quotes exactly as shown.
"<font class=nav onclick=\"JavaScript:PortalNav(event, '/shared/topaz/_portal/transaction analysis','Transaction','Type','"@"');\">"@"</font>"
This is the same as described in Section 6.3.2.3, "Navigation Using JavaScript", but rather than using a custom formatter, type in the HTML syntax with static values in place of the @ signs.
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 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 BI Server, the BI Server Administrator must expose the keys within the Presentation layer of the Oracle BI Administration Tool.
Drag and drop the keys from the Business Model and Mapping layer to the Presentation layer and save the repository.
Open Microsoft Access, select the option Blank Access Database, type the name oracle-analytics.mdb when prompted, and click Create.
After creating the new Microsoft Access database, right-click in the white section of the screen and select Link Tables.
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.
Click the Machine Data Source tab, locate the Analytics_Web DNS, and click OK.
The Oracle BI Server requires a login.
Type your user ID and password.
The Import Objects dialog box appears.
Click the Select All button, or highlight the desired logical tables from Oracle Business Intelligence.
The import may take a while to complete.
When the import completes, right-click in the white section of the screen and select Relationships:
Add the desired tables and drag and drop the keys from the dimension tables (Period, Market, Product) to the fact table (Sales Measures).
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.
Select Create query in Design view from the Queries button:
Select Markets, Products and Sales Facts.
Add Region, Brand, Units and Dollars, respectively, and then click Run.
You can retrieve links (both bookmark links and prompted links) to dashboard pages by using the JavaScript functions described in Table 6-2 in your custom scripts on your dashboard pages.
Note:
For these JavaScript functions to work, the HardenXSS element must be set to false. For more information, see "Making General Configuration Changes to Oracle BI Presentation Services" in the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise EditionFor more information about bookmark links and prompted links, see "About Creating Links to Dashboard Pages" in the Oracle Business Intelligence Suite Enterprise Edition User's Guide.
Table 6-2 JavaScript Functions for Retrieving Links to Dashboard Pages
| JavaScript Function | Description |
|---|---|
|
|
Sets the value of the variable Set the arguments as follows:
|
|
|
Returns the prompted link as a string. |