This chapter explains how to install XBRL Extension to Oracle XML DB.
It covers these topics:
Note:
Refer to "Placeholders in Oracle Database XBRL Extension Developer's Guide" for explanations of the placeholders used here.This section describes the minimal hardware and software requirements for installation and use of XBRL Extension to Oracle XML DB.
There are no extra hardware requirements for XBRL Extension to Oracle XML DB, beyond those for Oracle Database.
Oracle Database 11g Release 2 Enterprise Edition on one of the following platforms:
Linux Enterprise Distribution, from Oracle or Red Hat (either 32-bit or 64-bit)
Microsoft Windows (either 32-bit or 64-bit)
Oracle Solaris Sparc or Solaris on x86-64 (64-bit)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
Database patches as described in the XBRL Extension to Oracle XML DB README.txt file. See "Preparing to Install XBRL Extension to Oracle XML DB".
(Required for demo only.) Oracle Business Intelligence Enterprise Edition (BI-EE) 10.1.3.3, for dashboards and reports. BI-EE is not available on 64-bit platforms.
Third-party XBRL processing engine (XPE) and taxonomy design tools.
This section describes how to install and uninstall XBRL Extension to Oracle XML DB, create, drop, and purge an XBRL repository, and install the sample XBRL repository and GAAP demo.
This section outlines preparatory instructions for installing XBRL Extension to Oracle XML DB and related software.
Download the zip archive for the current release of XBRL Extension to Oracle XML DB from http://support.oracle.com. To find the file name of this zip archive, please visit the Oracle Technology Network XBRL site, http://www.oracle.com/technetwork/database/features/xmldb/index-087631.html.
Extract the contents of the zip archive to a temporary directory, patch_top.
Follow the instructions in patch_top/XBRLReleaseN/README.txt to create directory ORACLE_HOME/rdbms/xbrl_xdb.
Installing XBRL Extension to Oracle XML DB creates database user (schema) XBRLSYS, and it creates Oracle XML DB Repository folder /xbrl as a child of the repository root.
Create an Oracle Database with character set AL32UTF8.
Set the COMPATIBLE parameter to at least 11.2.0.1.0.
Set SHARED_POOL_SIZE to 1G.
Set the tablespace size to at least 3.5 times the size of the data on your file system, for indexed storage. For example, if the data size is 20G then set the tablespace size to at least 70G, for indexed storage.
Go to directory xbrl_xdb/XBRLScripts.
shell>Foot 1 cd XBRLScripts
Create a tablespace and a temporary tablespace for database user XBRLSYS.
SQL+>Foot 2 CREATE TABLESPACE xb_sys_ts . . .; SQL+> CREATE TABLESPACE xb_sys_tmp_ts . . .;
Run script xbrlinstall.sql to install XBRL Extension to Oracle XML DB.
SQL+> @xbrlinstall.sql sys_pass xb_sys_pass - > xb_sys_ts xb_sys_tmp_ts xb_protocols
Check the results of following SQL statements, to verify that XBRL Extension to Oracle XML DB has been successfully installed. The expected results are shown here.
SQL+> CONNECT XBRLSYS/xb_sys_pass SQL+> SELECT OBJECT_NAME FROM USER_OBJECTS - > WHERE STATUS = 'VALID' AND OBJECT_TYPE = 'PACKAGE'; OBJECT_NAME --------------------- DBMS_ORAXBRL_INTERNAL DBMS_ORAXBRLV DBMS_ORAXBRLD DBMS_ORAXBRL_UBM DBMS_ORAXBRLI DBMS_ORAXBRLT DBMS_ORAXBRL 7 rows selected. SQL+> SELECT ANY_PATH FROM RESOURCE_VIEW WHERE equals_path(RES, '/xbrl') = 1; ANY_PATH -------- /xbrl SQL+> SELECT INDEX_NAME FROM USER_INDEXES - > WHERE INDEX_TYPE = 'FUNCTION-BASED DOMAIN' AND STATUS = 'VALID'; INDEX_NAME ---------------- XBRL$SCHEMAIDX XBRL$INSTANCEIDX ORA$XBRLCACHEIDX SQL+> SELECT TABLE_NAME FROM USER_OBJECT_TABLES WHERE STATUS = 'VALID'; TABLE_NAME ---------------- ORA$XBRLINSTANCE ORA$XBRLLINKBASE ORA$XBRLSCHEMA
If there are any error messages in log file xbrlinstall.log, or if the result returned by any of the SQL queries in step 8 is not as expected, then check parameter COMPATIBLE and run uninstall — see "UnInstalling XBRL Extension to Oracle XML DB". Resolve the error, then try installing again (repeat steps 7 and 8).
Use the following query to determine the current version of XBRL Extension to Oracle XML DB.
SELECT VALUE FROM XBRLSYS.ora$xbrlrepprop WHERE NAME='XBRLVERSION';
Perform the following steps to uninstall XBRL Extension to Oracle XML DB. This drops all procedures and system objects created under database user XBRLSYS, and it deletes folder /xbrl from Oracle XML DB Repository.
Drop each XBRL repository – see "Dropping an XBRL Repository".
Run SQL script xbrluninstall.sql.
SQL+> @xbrluninstall.sql sys_pass xb_sys_pass
Check the results of the following SQL statements, to verify that XBRL Extension to Oracle XML DB has successfully been uninstalled.
SQL+> CONNECT SYSTEM/sys_pass
SQL+> SELECT 1 FROM DBA_USERS WHERE USERNAME = 'XBRLSYS';
no rows selected
SQL+> SELECT ANY_PATH FROM RESOURCE_VIEW WHERE equals_path(RES, '/xbrl') = 1;
no rows selected
Perform the following steps to create an XBRL repository. This also creates a database user (schema) with the same name as the repository. You can create any number of XBRL repositories. The repositories are independent of each other.
Create a tablespace and a temporary tablespace for the XBRL repository. Use a redundancy factor of about 3.5 when calculating tablespace size, to account for indexed storage.
SQL+> CREATE TABLESPACE xb_rep_ts . . .; SQL+> CREATE TABLESPACE xb_rep_tmp_ts . . .;
(Optional) If you want to partition the table that stores instance documents, then edit the CREATE TABLE statement for table ORA$XBRLINSTANCE in script xbrlddl.sql to add a virtual-column partition. See "Partitioning XBRL Repositories".
Run SQL script, xbrlcrt.sql, to create the XBRL repository.
shell> cd XBRLScripts SQL+> @xbrlcrt.sql sys_pass xb_sys_pass - > xb_rep xb_rep_pass xb_rep_ts xb_rep_tmp_ts xb_rep_idx_ts
This creates all of the tables and indexes that are needed for XBRL document storage. It also creates an Oracle XML DB Repository folder, under folder /xbrl, that has the same name as the XBRL repository, xb_rep.
Check the results of the following SQL statements, to verify that the repository creation was successful. The expected results are shown here.
SQL+> CONNECT xb_rep/xb_rep_pass SQL+> SELECT OBJECT_NAME FROM USER_OBJECTS - > WHERE STATUS = 'VALID' AND OBJECT_TYPE = 'PACKAGE'; OBJECT_NAME -------------------- XBRL_ASYNC_EVENTS DBMS_ORA_XBRL_EVENTS SQL+> SELECT ANY_PATH FROM RESOURCE_VIEW > WHERE equals_path(RES, '/xbrl/xb_rep') = 1; ANY_PATH ------------ /xbrl/xb_rep SQL+> SELECT INDEX_NAME FROM USER_INDEXES - > WHERE INDEX_TYPE = 'FUNCTION-BASED DOMAIN' AND STATUS = 'VALID'; INDEX_NAME ---------------- ORA$XBRLCACHEIDX XBRL$INSTANCEIDX XBRL$SCHEMAIDX SQL+> SELECT TABLE_NAME FROM USER_OBJECT_TABLES WHERE STATUS = 'VALID'; TABLE_NAME ---------------- ORA$XBRLINSTANCE ORA$XBRLLINKBASE ORA$XBRLSCHEMA
Check for any error messages in log file xbrlcft.log.
If there are any error messages in log file xbrlcft.log, or if the result returned by any of the SQL queries in step 4 is not as expected, then run script xbrldrop.sql to drop the newly created repository — see "Dropping an XBRL Repository". Resolve the error, then create the repository again (repeat steps 3 and 4).
Perform the following steps to drop (delete) an XBRL repository.
SQL+> @xbrldrop.sql xb_sys_pass xb_rep xb_rep_pass
Check the results of the following SQL statements, to verify that the drop was successful.
SQL+> CONNECT SYSTEM/sys_pass SQL+> SELECT 1 FROM DBA_USERS WHERE USERNAME = UPPER ('xb_rep'); no rows selected SQL+> SELECT ANY_PATH FROM RESOURCE_VIEW > WHERE equals_path(RES, '/xbrl/xb_rep') = 1; no rows selected
In case the database schema corresponding to an XBRL repository is dropped, you can perform the following steps to purge an XBRL repository, deleting all dependent objects that were created in Oracle XML DB Repository.
Run SQL script xbrludpurge.sql.
SQL+> @xbrludpurge.sql xb_sys_pass xb_rep
Check the results of the following SQL statements, to verify that the objects corresponding to the XBRL repository have been successfully purged.
SQL+> CONNECT SYSTEM/sys_pass
SQL+> SELECT ANY_PATH FROM RESOURCE_VIEW
> WHERE equals_path(RES, '/xbrl/xb_rep') = 1;
no rows selected
Perform the following steps to install the sample XBRL repository and GAAP demo.
Run SQL script InstallXBRLDemo.sql from directory xbrl_xdb/XBRLScripts. This creates a sample XBRL repository named oraxbrl.
Follow the instructions in "Building and Using a Sample XBRL Application: USGAAP 2008".
Refer to the SQL statements in xbrl_xdb/XBRLScripts, to become familiar with the APIs of XBRL Extension to Oracle XML DB.
This section describes the contents of the xbrl_xdb directory.
Directory XBRLScripts contains the following SQL script files.
Table 5-1 SQL Scripts in Directory XBRLScripts
| SQL Script Name | Description |
|---|---|
|
Create database user |
|
|
Create an XBRL repository and a database schema with the same name. Create all necessary tables, indexes, and procedures. |
|
|
|
Create tables and indexes. This script is run automatically by script If you want to partition the base table for the XBRL instance documents of a given repository, then edit script |
|
Drop a given XBRL repository, including the corresponding database schema, tables, indexes, and procedures. |
|
|
Finish uninstalling. Invoke this after dropping all XBRL repositories. |
|
|
Remove other system objects associated with an XBRL repository. Use this if a database schema corresponding to an XBRL repository is dropped accidentally. |
|
|
Delete resources from Oracle XML DB Repository that are associated with an XBRL repository. Use this if you use Oracle XML DB Repository and you mistakenly delete a document from the XBRL repository. |
|
|
|
Drop the |
|
Register standard XBRL schemas. Must be run before using the tuple APIs. |
|
|
Load error messages in different languages for XBRL Extension to Oracle XML DB. |
|
|
Install XBRL Extension to Oracle XML DB and create an XBRL repository. |
Directory XBRLDemoScripts contains the following files.
demo.sql – Script that loads and queries Bank of America and USGAAP 2008 files.
demo2.sql – Script that loads and queries a tuple demo (files oraclexbrltupledemo.xsd, oraclexbrltupledemo-inst.xml).
Files schema.xml and linkbase.xml, which are used by the USGAAP 2008 demo. These files list the XBRL schema and linkbase files in USGAAP 2008. See "Building and Using a Sample XBRL Application: USGAAP 2008".
You must install an Oracle-certified third-party XBRL processing engine, outside the database. For information about this, consult Oracle XBRL support:
http://www.oracle.com/technetwork/database/features/xmldb/index-087631.html
Oracle Business Intelligence Suite Enterprise Edition (OBIEE) is not included as part of XBRL Extension to Oracle XML DB. You must procure it separately and install it according to the OBIEE instructions. You can install it in any tier. You must install an Oracle client for OBIEE to work properly with XBRL Extension to Oracle XML DB.
XBRL Extension to Oracle XML DB provides a demo package to demonstrate integration with OBIEE. Included in directory xbrl_xdb is a directory Demo-BIFiles, which contains the OBIEE repository file xbrl.rpd and folders and files for a sample dashboard, in zip archive xbrl.zip.
See Also:
http://st-curriculum.oracle.com/obe/fmw/bi/biee/r1013/bi_admin/biadmin.html – Building and managing an Oracle Business Intelligence repository
http://st-curriculum.oracle.com/obe/fmw/bi/biee/r1013/saw/saw.html – Oracle Business Intelligence queries and interactive dashboards
Perform the following steps to configure OBIEE with the demo package:
Establish a connection to the database instance.
Add entry DEMO to file oracle_client_dir\network\admin\tnsnames.ora. In the following, change hostname.domain, port, and sid to the correct values for your database instance:
DEMO = (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = hostname.domain)
(PORT = port)))
(CONNECT_DATA = (SERVICE_NAME = sid)))
Select from the Microsoft Windows Start menu: oracle_client, then Configuration and Migration Tools, then Microsoft ODBC Administrator.
In the Oracle ODBC Driver Configuration dialog box, click the System DSN tab. Click Add. Select oracle_client as the driver. Click Finish.
Enter x02 as the Data Source Name, demo as the TNS Service Name, and oraxbrl as the User ID. Click OK.
Copy OBIEE repository file xbrl.rpd from xbrl_xdb\Demo-BIFiles to obiee_home\server\Repository.
Extract zip archive xbrl.zip to directory obieedata_home\web\catalog.
Open file obiee_home\server\Config\NQSConfig.INI in a text editor, and change the text that follows Star = to make it xbrl.rpd:
[ REPOSITORY ]
Star = xbrl.rpd, DEFAULT;
Open file obieedata_home\web\config\instanceconfig.xml in a text editor, and change the final directory component of the catalog path to make it xbrl:
<CatalogPath>obieedata_home/web/catalog/xbrl</CatalogPath>
Start the Oracle BI services.
Choose from the Microsoft Windows Start menu: Run.
Enter services.msc.
In the Services dialog box, start or restart each of the following services, by selecting the service name, right-clicking, and then selecting Start or Restart: Oracle BI Server, Oracle BI Javahost, and Oracle BI Presentation Server.
Footnote Legend
Footnote 1: Shell examples are indicated here using the promptshell>.SQL+>, and the continuation prompt is shown as >. Hyphen (-) is the SQL*Plus line continuation character.