Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console 11g Release 1 (11.1.1) Part Number E14849-06 |
|
|
PDF · Mobi · ePub |
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:
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."
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.
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.
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.
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.
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.
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".
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 |
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 |
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.