Creating Ad Hoc Queries in Transportation Intelligence

You can also create and run ad hoc queries against the Transportation Intelligence (TI) data. An Ad Hoc Query (also called Oracle BI Answers) is Oracle Analytics Server (OAS) functionality that is made available for TI. For more details on creating complex ad hoc queries, please refer to the OAS training and documentation.

In this topic we will briefly review how to create and run an ad hoc query in TI.

Ad Hoc Query is an TI-specific term. Ad Hoc Query is the name of the menu item that TI uses to access the Oracle BI Answers page. The page allows you to run queries (or requests) against the TI data. You can also create reports on the fly. The results can be formatted, saved, organized, and shared with others.

To access Oracle BI Answers go to Transportation Intelligence > Ad Hoc Query. You see the Oracle BI Answers page appear in TI.

Note: For more details on creating complex ad hoc queries please refer to the OAS online help, end user documentation, and Oracle University training modules.

Here is an overview of the overall process for creating an ad hoc query:

1. Select the Transportation Intelligence > Ad Hoc Query menu item.
2. Under Analysis and Interactive Reporting, click Analysis.
3. Select the Subject Area of Transportation Intelligence.
4. Add the appropriate TI columns to your query.
5. Format the query columns to adjust the way the data appear.
6. View the query results.
7. Optionally, filter the query results.
8. Optionally, add prompts to your query.
9. Save the ad hoc query.
10. Open the saved ad hoc query.

Selecting a Subject Area

On the Oracle BI Answers page, you see the Recent section which allows you to find and open any existing saved and/or shared queries. Oracle BI Answers calls these queries requests.

To create new queries or use existing query, you must first select the correct Subject Area.

1. Under Analysis and Interactive Reporting, click Analysis.
2. Click Transportation Intelligence.

Adding Columns

Once you click on the subject area of Transportation Intelligence, you see the Oracle BI Answers selection pane on the left-hand side of the page. You now see a list of Transportation Intelligence virtual tables and columns from which you can build your query or request. Each table represents an OTM business object for which data was loaded into the TI database. This data is referred to as TI columns.

You construct an ad hoc query by selecting columns from the tables under the Transportation Intelligence heading on the left-hand side.

1. Click a virtual table folder to see its columns. For example, click the triangle next to Shipment Analysis to see all of the shipment data loaded as TI columns. The TI data typically has two levels of table data before you see a column entry.

1. 1. Click Shipment Analysis.
   2. Click Shipment Facts to see the shipment column that can be added to an ad hoc query.

2. Double-click a column to add it as criteria in a query. For example, double-click Total Shipments to add the Total Shipment columns to the ad hoc query.
3. As you double-click on TI columns they are added to the Columns section. The selected columns appear as criteria in the workspace on the right pane on the Criteria tab.

On the right-hand side of the page, you see the BI Answers workspace. This is where you will view and edit your ad hoc queries. This area contains tabs that are used to work with ad hoc queries which are Criteria, Results, Prompts, and Advanced.

In the right pane (the Oracle BI Answers workspace), each column box has two sections. The upper section displays the name of the virtual table and the lower section shows the name of the column.

Modifying Column Properties

For each column on the Criteria tab, you see several buttons. Click the buttons to view or modify the properties of the columns. You can:

• Sort to determine the sort order (ascending or descending)
• Column Properties allow you to edit column properties to control appearance and layout
• Edit formula to add new functions such as ranking and move averages
• Filter allow you to add filters to limit data that appears in a column
• Delete a column from the ad hoc query
• Save Column As

To change column order, you can click and drag the column.

To see the results of this ad hoc query, click the Results tab.

Viewing Query Results

Click the Results tab to execute the associated SQL statements and displays the resulting table. The data is automatically sorted by the first column.

You can use the buttons at the top of the Results tab to edit the results as follows:

• Modify the results layout
  • Add or edit the section and table titles
  • Add or edit table properties
  • Add chart, pivot table, or other views
  • Add or select filters

Adding Filters

A filter is used to limit the results of an ad hoc query in a report. Based on filter criteria that you define, Oracle BI Answers shows only those results that match your criteria. You can add a filter to your ad hoc query.

Adding Prompts

A prompt is a special kind of filter that restricts the result set returned by an ad hoc query. It can also be used to populate variables. You can add prompts to your ad hoc queries by clicking the Create Prompt button.

Viewing Queries Details on the Advanced Tab

Click the Advanced tab to see the complete details of the Request XML and SQL Issued by Oracle BI.

Saving Your Query

Once you have included the appropriate data and formatted the query, you can save or print it. You can save your ad hoc queries to the folder My Folders. Queries saved to My Folders can only be accessed by this particular user.

If you want your queries or request to be viewed by other users, you can have your TI administrator add them to the folder titled Shared Folders.

Opening a Saved Query

You can view or modify any saved queries that you have created in the past.

1. To access your saved ad hoc queries, go to Transportation Intelligence > Ad Hoc Query.
2. Under Analysis and Interactive Reporting, click Analysis.
3. Select Transportation Intelligence.
4. In the left hand pane, open the My Folders folder.
   
   Saved queries that appear under Shared Folders can be used by all TI users.
5. Click on any of the saved ad hoc queries that you have created in the past.
6. To edit the query, click the Modify button.