You can use SELECT statements and FULLSELECT
statements in a physical data model of a data design project to generate
materialized query tables (MQTs) in the physical data model.
An
MQT is a table whose definition is based on the result of a running
a query. The MQT typically contains precomputed results that are based
on the data existing in the table or tables that its definition is
based on.
Before you begin
The SELECT statement or FULLSELECT statement must exist
in the SQL Statements folder of a physical
data model in a data design project.
Procedure
To generate an MQT in a physical data model:
- In the Data Project Explorer view,
expand a data design project that contains a physical data model and
then expand the Data Models folder.
- Double-click the physical data model (.dbm) file to open
it.
- Expand the physical data model, the database, and the SQL
Statements folder.
- In the SQL Statements folder, right-click
a SELECT statement or a FULLSELECT statement, and then click on
the pop-up menu. The MQT is generated from the SQL
statement and stored in the physical data model.
- Save the physical data model. When you save
the physical data model, you save all the data objects in the physical
data model that have outstanding changes.
Results
The MQT is selected in the Data Project Explorer view so that
you can locate it easily. You can copy or move the MQT to another
database schema by dragging the MQT or by using commands on the pop-up
for the MQT. The generated MQT is assigned the name "MQT
n,"
where
n is a sequential number that starts at 1, and increases
by 1 for each MQT that you generate. You can use the Properties view
to change the name and other properties of the MQT.