KB41146: How to use the Generic DBMS object to work with an Oracle TimesTen database

Oracle TimesTen is an in-memory relational database and all database operations are performed in memory.
 
NOTE: Oracle TimesTen database is currently not officially supported as a warehouse with Strategy 9.x. The level of support for this database is limited to the extent of support for the Generic DBMS object.
 
Refer to the following Strategy Knowledge Base technical note for instructions on configuring ODBC connectivity to an Oracle TimesTen database if the Intelligence Server is on Linux:
 
KB41147: How to setup ODBC connectivity on Linux to connect to an Oracle TimesTen database
 
Using the Generic DBMS object to connect to an Oracle TimesTen database:

• In Desktop, go to Administration > Configuration Managers > Database Instances and create a new database instance.
• Set the 'Database connection type' to 'Generic DBMS'.
   
  

• Create/configure the 'Database connection' and click 'OK' to save the changes.

Setting up the Warehouse Catalog options for the database instance:
 
NOTE: The catalog SQL provided below is just an example from testing at a specific environment. Strategy does not officially endorse the SQL below.
 
Follow the steps below to setup the warehouse catalog:

1. Launch the Warehouse Catalog in Desktop (located under Schema > Warehouse Catalog).
2. Under 'Select current database instance', choose the newly created Oracle TimesTen database instance.
3. In the Warehouse Catalog, go to Tools > Options > Catalog > Read Settings and click on 'Settings'.
4. Insert the following SQL in the text box for 'The following SQL Statement will be used to retrieve the list of available tables from the Warehouse Catalog'
    
   
   Sample Code/Error
   select
   '<data source name>' NAME_SPACE,
                 TBLNAME TAB_NAME
   from TABLES;
   
   
    
   Note: The <data source name> placeholder must be replaced by the data source name used to connect to the Oracle TimesTen database. For example:
    
   
   Sample Code/Error
   select
   'OracleTimesTen' NAME_SPACE,
                 TBLNAME TAB_NAME
   from TABLES;
   
   
     
   
5. Insert the following SQL in the text box for 'The following SQL Statement will be used to retrieve the columns for the selected table(s)'
    
   
   Sample Code/Error
   select
   '<data source name>' NAME_SPACE,
   a.tblname TAB_NAME, 
   b.COLNAME COL_NAME,
   b.COLTYPE DATA_TYPE,
   b.COLLEN DATA_LEN,
   b.SYS9 DATA_PREC,
   b.SYS10 DATA_SCALE
   from  tables a,
   COLUMNS b
   where a.TBLID = b.ID
   and a.tblname in (#TABLE_LIST#) order by 1,2,3
   
   
    
    Note: The <data source name> placeholder must be replaced by the data source name used to connect to the Oracle TimesTen database. For example:
    
   
   Sample Code/Error
   select
   'OracleTimesTen' NAME_SPACE,
   a.tblname TAB_NAME, 
   b.COLNAME COL_NAME,
   b.COLTYPE DATA_TYPE,
   b.COLLEN DATA_LEN,
   b.SYS9 DATA_PREC,
   b.SYS10 DATA_SCALE
   from  tables a,
   COLUMNS b
   where a.TBLID = b.ID
   and a.tblname in (#TABLE_LIST#) order by 1,2,3
   
   
     
   
6. Click OK to save the changes. 

Third Party Software Installation:
WARNING:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.