B Oracle TimesTen Database Guidelines

DAC supports Oracle TimesTen In-Memory Database (TimesTen) as a data warehouse database type. This appendix describes usage guidelines specific to TimesTen databases.

This appendix contains the following topics:

• About TimesTen as a Database Type

• Setting Up an ODBC Connection Between DAC and TimesTen Databases

• Pruning Tables and Columns for TimesTen Databases

• About DAC Support for Indexes in TimesTen Databases

• Customizing How Tables Are Analyzed in TimesTen Databases

• TimesTen Data Types

About TimesTen as a Database Type

Oracle TimesTen In-Memory Database (TimesTen) operates on databases that fit entirely in physical memory (RAM). Therefore, because memory is limited, you should make sure a data warehouse that resides in a TimesTen database holds only the necessary tables and columns and that the columns are of an optimal size. For example, you could store a full set of data warehouse tables in an Oracle database, and then move a subset of the tables to a TimesTen database.

The In Memory check box in the Tables tab and Columns subtab of the Design view is a flag that controls whether DAC designates the tables and columns as In Memory. Only In Memory tables and columns will be created and upgraded in the TimesTen database. If a table is In Memory, by default all of its columns are also In Memory. You can individually deselect a column's In Memory check box if you do not want the column to be In Memory.

In Memory tables and columns are created, updated, and dropped on a TimesTen database in the same way they are for Oracle, SQL Server, and DB2 databases. For instructions on managing data warehouse schemas, including a schema on a TimesTen database, see Chapter 10, "Managing Data Warehouse Schemas."

Setting Up an ODBC Connection Between DAC and TimesTen Databases

DAC communicates with TimesTen databases using an ODBC connection.

• The TimesTen client needs to be installed on the machine where DAC is running.

• The network port of the TimesTen Server is required for the ODBC connection. To determine the network port, open a DOS Command Prompt and go to the <TimesTen>\bin directory. Then, run the ttstatus command on the machine where the TimesTen server is installed.

• Make a note of the Client DSN. This value is required in the ODBC Data Source field of the DAC Physical Data Sources tab when you register the TimesTen database as a data source.

Pruning Tables and Columns for TimesTen Databases

If there are tables or columns that you do not need for analytical reporting, you can prune them in DAC so that they are not created in the TimesTen database.

Pruning Tables

To ensure a table is not created in a TimesTen database, do one of the following:

• Make sure the In Memory check box for the table is not selected.

• Inactivate the table by selecting the Inactive check box in the Tables tab. Note that this option inactivates the table from participating in all ETL processes.

Pruning Columns

You can prune columns for TimesTen databases in the following ways:

• Remove columns

  If a table is designated as In Memory (by selection of the In Memory check box in the Tables tab), by default all of the table columns are also designated as In Memory, which means the columns will be created in the TimesTen database. If you do not want a column created in the TimesTen database, you can deselect the In Memory check box in the Columns subtab of the Tables tab in the Design view or inactivate the column by selecting the Inactive check box.

• Reduce column size

  You can reduce the column size in a TimesTen database to save memory if you know the data that will populate the data warehouse column will fit in a reduced column size. To reduce the column size, go to the Columns subtab of the Tables tab and enter the appropriate values in the In Memory Length and In Memory Precision fields. The values in the In Memory Length and In Memory Precision fields override the existing Length and Precision values for the data type.

  For example, suppose the Number data type has a length of 10, but you know the column will hold data of only five digits, such as a zip code, you could set the In Memory Length to 5, which will avoid allocation of extra memory to the column.

About DAC Support for Indexes in TimesTen Databases

Consider the following points about DAC support for indexes in TimesTen databases:

• DAC supports the hash index type. To specify an index as a hash index, go to the Indices tab in the Design view and select the Is Hash check box.

• You can specify which database types an index is applicable to by going to the Indices tab and double-clicking in the Databases column. The Supported Database Types dialog enables you to select the appropriate database types for the index.

• If you want to create a special type of index that DAC does not natively support, you can use the Actions framework to define a template. For instructions, see "Using Actions to Optimize Indexes and Collect Statistics on Tables".

• If an index is not necessary, you can inactivate it by going to the Indices tab and selecting Inactive.

Customizing How Tables Are Analyzed in TimesTen Databases

The customsql.xml file, located in the <DAC_Config_Location>\CustomSQLs directory, contains the default syntax DAC uses for analyzing tables on all database types, including a TimesTen database. You can edit the customsql.xml file to change the default behavior. For instructions, see "Customizing customsql.xml to Drop and Create Indexes and Analyze Tables".

TimesTen Data Types

When a DAC table column is converted to a TimesTen table column:

• The data types for non-Unicode databases are displayed in Table B-1.

• The data types for Unicode databases are displayed in Table B-2.

Table B-1 Conversion of Types from DAC to TimesTen for Non-Unicode Databases

DAC Data Type	TimesTen Data Type
CHAR	TT_CHAR
VARCHAR	TT_VARCHAR
NUMBER	NUMBER See "About the Number Data Type When Precision Is Zero".
TIMESTAMP	TT_TIMESTAMP
DATE	TT_DATE

Table B-2 Conversion of Types from DAC to TimesTen for Unicode Databases

DAC Data Type	TimesTen Data Type
CHAR	TT_NCHAR
VARCHAR	TT_NVARCHAR
NUMBER	NUMBER See "About the Number Data Type When Precision Is Zero".
TIMESTAMP	TT_TIMESTAMP
DATE	TT_DATE

About the Number Data Type When Precision Is Zero

To reduce memory usage, when the data type is number and the precision is zero, the following conditions apply:

• If the length is less than 5, the data type for the column will be TT_SMALLINT.

• If the length is greater than or equal to 5 and less than 10, the data type of the column will be TT_INTEGER.

• If the length is greater than or equal to 10 and less than 19, the data types for the column will be TT_BIGINT.