SQL warehousing — Acceleration of the generation of physical data models

By default, the system creates a physical data model for the whole database. Therefore, if you are using large databases, reading all database objects and analyzing dependencies between them might be a time-consuming process. You can reduce the runtime of reverse engineering a database and of creating a physical data model by limiting the number of database objects that are scanned.

When you create a new data flow or mining flow in the Design Studio, you must generate a physical data model that describes the database objects that can be referred to in the data flow or the mining flow. You can create the model automatically or manually. However, if there are many objects in the database, for example, tables or views, the time to create the model might take too long. Also, when a model is updated, for example, if you select Data Flow > Refresh Database from the menu bar of the Design Studio, the operation might take too long.

Typically, only a fraction of the database objects is required in a particular data flow or mining flow. Therefore it is sufficient to generate a smaller physical data model that includes only selected objects.

For example, you might have created a connection to the database SAMPLE. The tree in the Data Source Explorer shows the database objects of this database. To limit the objects that are displayed in the Data Source Explorer, you can filter these objects by right-clicking one of the following database objects and specifying particular filter conditions:

Schemas
Table Spaces

If you have filtered these database objects, only the objects that meet the specified filter conditions are replicated in the model when you later create a physical data model for this database connection or when you select this database connection as online database for your flow.

Within a schema, you can specify filter conditions by right-clicking one of the following schema objects:

Aliases
Federated Stored Procedures
MQTs
Nicknames
Sequences
Stored Procedures
Tables
User-Defined Functions
User-Defined Types
Views

On the Filter dialog, you can select or clear specific objects, or you can specify filter conditions.

Schemas, tables, or views are the preferred candidates for a filter condition.

If you must see also all objects in your database, you can create the following two connections to the same database:

A database connection that is based on filter conditions.
With this database connection, you can quickly generate physical data models.
A database connection that shows all database objects.