Note: The Freeform script and database import options are currently unavailable for Food Service installations.
You can import your data by dragging and dropping tables, selecting columns, defining joins, and specifying filter conditions using the Database page. Once you have imported your data and published it as an Intelligent Cube, you can create reports, documents, and Visual Insight analyses using the published data. For detailed steps to import data from a database, see Importing data from a database.
For background information about importing data, including steps to import data, see About importing data.
To use the Database page, you should understand the following parts of its interface:
You must have Import Data and Import Database privileges.
The following toolbar options are available:
Database View : Click this icon to highlight it and display the Database Connections and Available Tables panels. Click the icon again to remove the panels from display.
Data Preview : Click this icon to highlight it and display the Preview panel. Click the icon again to remove the Preview panel from display.
Filter : Click this icon to define filters to use to select your data. Select the appropriate options in the Filters dialog box.
Data Refresh Options : By default, if the data in an Intelligent Cube needs to be updated, it is re-executed. All the data for the Intelligent Cube is loaded from the data warehouse into Intelligence Server's memory, and the existing data for the Intelligent Cube is overwritten. You can set up incremental refresh settings to update the Intelligent Cube with only new data. This can reduce the time and system resources necessary to update the Intelligent Cube periodically.
Click the Data Refresh Options icon . The Options - Data Refresh dialog box opens. Select one of the following options:
To re-execute your SQL query and replace existing data in the Intelligent Cube with the new results, select Replace existing data.
To update data that has been modified, and include new data rows, select Update existing data and add new data.
To update the data to include new data rows, select Keep existing data and add new data. Data that has already been imported and published is not changed.
You can select the database connection to use to import data, create a new database connection, or edit, delete, duplicate, or rename an existing database connection using the Database Connections panel. A list of available database connections is displayed in the panel.
To define a new database connection, click the New Connection icon , then specify the appropriate options for the connection.
To edit, delete, duplicate, or rename an existing connection, hover the cursor over the name of the connection and click the arrow icon. A pop-up menu is displayed. Select the appropriate options to edit, delete, duplicate, or rename the connection.
You can view a list of the tables for a database connection using the Available Tables panel. You can select a namespace from the drop-down list to display only the database tables within a selected namespace. To view a list of the columns in a table, click the expand icon next to the table name. The name of each column in the table is displayed, along with its data type. To filter the list of database tables by table name, type the name in the search field. The list of tables is updated automatically as you type.
By default, XBRi creates a cache of the database's tables and columns when a database connection is first used. Only the namespaces that are in use are cached. You can hover the cursor over the information icon to display a tooltip containing the time the cache was created. You can click the Refresh icon to update the cache and refresh the list of available tables. The list of namespaces available in the namespace drop-down list is also updated.
To select data from a table to be imported, click and drag the name of the table onto the editor panel on the right. The table is automatically added to the editor panel and displayed. Define the data you want to import, as follows:
To include a data column in your imported data, hover the cursor over the name of each column and click the Add icon . A green check mark is displayed next to the column.
To include all the data columns in the table in your imported data, hover the cursor over the asterisk (*) at the top of the table and click the Add icon . Green check marks are displayed next to each column in the table.
To remove a table and all of its columns from being included in your data, click the Delete icon next to the name of the table.
You can use the editor panel to define joins between tables, define filters, aggregate data, and more. For steps, see Importing data from a database.
Execute SQL : Click this icon to display a preview of your imported data in the Preview panel.
Edit SQL : By default, when you select data to import by dragging and dropping tables, selecting columns, and so on, XBRi automatically generates the SQL query required to import your data from the database. Click this icon to view and edit the query directly and customize the way your data is imported. The query that will be used to select your data is displayed in the editor panel.
Note: If you choose to return to selecting the data that you want to import by dragging and dropping data on the editor panel, your customizations will be lost.
Convert to Query Builder : By default, when you select data to import by dragging and dropping tables, selecting columns, and so on, XBRi automatically generates the SQL query required to import your data from the database. If you have chosen to edit your query directly, you can undo your customizations and return to selecting the data that you want to import by dragging and dropping data on the editor panel. To do so, click the Convert to Query Builder icon . A confirmation message is displayed, notifying you that any changes that you made while manually editing the query will be reverted. Click OK.
Update Tables : Click this icon to update the display of tables placed on the editor panel.
You can view a preview of your data using the Preview panel.
You can designate a data column as an attribute or metric, or specify additional importing options. Hover the cursor over a column, then click the arrow icon. A list of options is displayed. Select one of the following:
To designate the data column as an attribute, point to Attribute, then do one of the following:
To assign a geo role to the data column, select the geo role you want to assign, such as City, State, or Country. Select the check box next to each attribute you want to have automatically generated based on the data column, then click OK.
To assign a shape key to the data column, select Others, then select the shape key you want to assign, such as Countries of the World or States of USA. Click OK. For background information on shape keys, see Creating an Image Layout visualization.
To define the data column without assigning it a geo role, select None, then click OK.
To designate the data column as a metric, select Metric.
To exclude the data column from the published data, select Delete. The data column is displayed as greyed out in the Preview panel.
To rename the data column, select Rename. Type a name for the data column, then press ENTER.
To map a data column to a project attribute, select Map to Project, then select the appropriate options to map the data column. For detailed steps, see Importing data from a database. This option is only available for attributes.
To unmap a data column from a project attribute, select Unmap from Project. This option is only available for attributes.
To change the data type of the column, point to Data Type, then select the data type you want to use.
Click Publish. The Save Cube dialog box opens. Browse to the location to publish the imported data to, then type a name and description for the published data in the Name and Description fields. Click OK. The data is saved as an Intelligent Cube.
Related topics
Best Practices: Importing Data
_____________________________
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Legal Notices