Under certain circumstances, it might be beneficial to alter the
generated database artifacts to improve performance. In general, there are
two approaches that can be taken: iterative improvement and ad-hoc improvement
Iterative table space mapping improvement
The number of tables that are created by the Schema Generator depends (among
other facts) on the complexity of the
business measures model.
Thus it is initially difficult to determine how to optimally assign those
tables to the table spaces. The following simple approach can help you to
incrementally improve your table to table space mapping, as defined by the
table space configuration file. For more information about database planning,
refer to
Preparation of database artifacts deployment.
To improve table to table space mapping, you have to perform the following
tasks:
- Run the Schema Generator using the default or any other customized table
space configuration file.
- Extract the generated artifacts into a temporary directory and identify
the number and type of tables that will be created in the database. For each business measures model, tables of the following types
will be created:
- Context and activity: Exist only in the State and Runtime databases
and are accessed by the server and dashboards.
- CD: Exists only in the State and Runtime databases. CD Tables usually
contain a few more columns than there associated context or activity tables.
The size of a particular CD table depends heavily on the number of transactions
against its corresponding context or activity table, the ratio of update to
insert transactions, and how often the associated apply component of a data
movement service reads the transactions from the CD table and inserts them
into a corresponding CCD table, and how often the CD table entries are pruned
by the associated source life cycle component.
- CCD: Exists only in the Runtime and Historical databases. A CCD
table has the exact same structure as its corresponding CD table, and largely
depends upon the same factors to determine its size. The one difference is
that instead of being read by a apply component the transactions are read
by an ETL component and entries are pruned by a target life cycle component.
- RM Internal Tables: Exists only in the Runtime and Historical databases.
These tables use pages with a maximum size of 4 KB.
- Fact and Dimension: Exist only in the Historical database.
- Modify the table space configuration file so that it contains:
- A mapping for each table type
- Multiple table type to table space mappings if a large number of tables
would otherwise be assigned to the same table space
Note: - Do not specify table-space declarations for table types that will not
be created in a database because those table spaces will not be used.
- Metrics map to table columns. The more metrics you define, the larger
a table will be and the larger the page size that will be needed for its table
space.
- Create those table spaces (and buffer pools) before deploying the generated
artifacts: The Schema Generator does not validate whether
the table spaces declared in the configuration file exist because no database
connection has been established. However, deployment will fail if the table
spaces do not exist.
- Re-Run the Schema Generation using the optimized table space configuration
file.
Note: Change the configuration file name in the Schema Generator administrative
console configuration if you created a new table space configuration file.
Ad-hoc improvements
You can modify the generated artifacts by changing the following:
In general, changes allowed do not alter the fundamental table schema
or structure.
When you change the generated scripts, consider the following limitations:
- You cannot change any table name.
- You cannot change any column name.
- You cannot change any column data type.
- You cannot remove any column or any table.
- You cannot add any column to a table
- You cannot change any primary key for a table.
- You cannot change the nullability of a column.
- You cannot change the schema assignment for any table.
- You cannot add any new constraints such as uniqueness constraints or foreign
key constraints.
Important: The changes made to the generated database artifacts
are not taken into consideration the next time schema generation is performed
for the same business measures model. For
example, a user alters the generated artifacts for a business measures model "Finance
Model" prior to deploying them. Subsequently the user alters the business measures model and regenerates all artifacts.
In this case the user will have to modify the newly generated artifacts again
because Schema Generator does not know about the modification made to the
previously generated artifacts.
Note: - The artifacts deployment requires Java™ version 1.4.2 or higher.
- The Java bin directory should be added to the system path
before executing the replication scripts.