Data marts

Typically, data marts contain a subset of the tables in your database. The data marts can also contain a subset of the columns within a table. This configuration is advantageous when you are using the TCP/IP loopback optimization between Informix® and IWA, because it provides a seamless experience for the customer.

When you create a data mart, you specify the fact table, the dimension tables, and the references between the tables. Data marts do not need to be a duplication of the design of your warehouse fact and dimension tables. For example, you can designate a dimension table in your warehouse schema as a fact table in a data mart. To improve query processing, limit the number of dimension tables, and columns within the dimension tables, in the data mart. Identify only those columns that are necessary to respond to your queries.

If a fact or dimension table is fragmented, each fragment corresponds to a partition in the data mart. If a fact or dimension table is not fragmented, the data mart contains a single partition that contains the data from the table.

A newly created data mart has all of the necessary structures that are defined but is empty and must be filled with a snapshot of the data from the Informix database server. When the data from the database server is loaded in the data mart in the accelerator server, the data is compressed. After the data is loaded in the data mart, the data mart becomes operational.

Data marts must be based on a snowflake or star schema

The following figure shows a sample schema with two fact tables, DAILY_SALES and DAILY_FORECAST. These fact tables are linked to several dimension tables: STORE, CUSTOMER, PROMOTION, PERIOD, and PRODUCT. There are several key references in the fact tables that are used to link to the dimension tables. For example, in the DAILY_SALES fact table, the PRODKEY column is linked to the PRODKEY column in the PRODUCT dimension table.

begin figure description - The figure is described by the surrounding text - end figure — Figure 1: A sample star schema with two fact tables

Using the schema in A sample star schema with two fact tables, you can create two data marts. The first data mart is based on the DAILY_SALES fact table and the dimension tables that it links to, as shown in A data mart with the DAILY_SALES fact table. A second data mart is based on the DAILY_FORECAST fact table and the dimension tables that it links to, as shown in A data mart with the DAILY_FORECAST fact table.

begin figure description - The figure shows the DAILY_SALES fact table which is linked to five dimension tables: STORE, CUSTOMER, PROMOTION, PERIOD, and PRODUCT. The key references in the fact table are used to link to the dimension tables. For example, the STOREKEY column in the DAILY_SALES fact table is linked to the STOREKEY column in the STORE dimension table. The CUSTKEY column in the DAILY_SALES fact table is linked to the CUSTKEY column in the CUSTOMER dimension table. - end figure — Figure 2: A data mart with the DAILY_SALES fact table

begin figure description - The figure shows the DAILY_FORECAST fact table which is linked to three dimension tables: STORE, PERIOD, and PRODUCT. The key references in the fact table are used to link to the dimension tables. For example, the PERKEY column in the DAILY_FORECAST fact table is linked to the PERKEY column in the PERIOD dimension table. The PRODKEY column in the DAILY_FORECAST fact table is linked to the PRODKEY column in the PRODUCT dimension table. - end figure — Figure 3: A data mart with the DAILY_FORECAST fact table

Summary or aggregate tables in data marts

To summarize the granular data in the fact tables and dimension tables, some warehouse databases use other tables that are known as summary tables or aggregate tables. For example, a summary table might contain sales information for an entire month or quarter that is consolidated from fact and dimension tables.

Because Informix Warehouse Accelerator speeds up query processing, it is not necessary to use summary tables to improve query performance. Informix Warehouse Accelerator queries the fact table and dimension tables directly.