Create a Data Mart

Use this wizard to create a data mart by using workload analysis.

Data marts contain a subset of the tables in a data warehouse database that are required to respond to the warehouse queries. Data marts can also contain a subset of the columns in a table. A data mart is loaded with a snapshot of the data from the database.

Workload analysis gathers information about warehouse queries as they run and analyzes the resulting data to determine which queries are good candidates for acceleration. The columns, tables, and joins used by the queries that can be accelerated are included in the data mart definition.

Record a query workload or use an existing recording

To record a workload, name the workload and click Record. Then run the workload as you usually do. You can close the wizard while the workload is recorded.

SQL Tracing Settings

Number of traces

Specify the number of SQL statements to trace before reusing the resources. The range is 500 - 2147483647. The default is 10000.

Trace size (KB)

Specify the maximum size of the data that is collected for an SQL statement. The range is approximately 512 - 102400 bytes. The low and high limits vary slightly depending on the operating system where the database server is installed. The default is 32 KB. Data that exceeds the maximum trace size for a statement is discarded.

User

Specify that tracing is for only the named user. Leave this field blank to trace the SQL statements that are run by all users.

Review the SQL Statements

The table shows the SQL statements and indicates whether they can be accelerated.

Create the Data Mart

Accelerator

Select the accelerator from the list. An accelerator is required to create the data mart. If Informix Warehouse Accelerator is installed, but no accelerators are created, you can create an accelerator on the Warehouse Accelerator page and run the wizard again with the recorded query workload.

Load data after the data mart is created

Select this option to create and load the data mart. You can also load the data mart or schedule loading the data mart from the Schema Manager page. Select the data mart in the list and on the Data Mart page, click Edit.

Locking level

The Locking level protects the data against changes during the load operation to ensure data consistency.

• NONE: No locking. The data is read from the different tables without locking them. Other user sessions can change the data during the load operation. As a result, the loaded data might be inconsistent. Data inconsistencies might be acceptable if the purpose of the data mart is to create statistics and discover trends, rather than to find the exact values of specific data rows.
• TABLE: Protects only the table that is being loaded against changes. Each table is locked in share mode while it loads with data. The loaded data is consistent within each table, but not necessarily across different tables.
• MART: Protects all the tables that are referenced by the data mart against changes during the load operation. All the tables are locked in share mode until the data mart is loaded. The loaded data is consistent from all of the tables. All of the other user sessions are blocked from changing the data in the tables that are loading.

Keep the recording of the workload trace after the data mart is created

Select this option to keep the table that contains the information that is gathered when the query workload is recorded. The table name is the workload name prefixed by dwa_saved_workloadtab. If you keep the recording, you can create another data mart from it. You can delete the recording from the Schema Manager page. Select the table in the list and click Actions > Tables > Drop Table.