Managing warehouse and project tables

The Warehouse Catalog allows you to view tables that have been included in the project, as well as those tables that are available in the warehouse but have not been included in the project. To access the Warehouse Catalog for a project, see Accessing the Warehouse Catalog.

As you make changes to the tables in the warehouse, you need to periodically load the updates into the Warehouse Catalog. You can update it by selecting Read the Warehouse Catalog from the Actions menu.

The Warehouse Catalog has the following sections:

• Select current database instance: From the drop-down list, select the database instance for the data source to view tables for. This option is available as part of the MultiSource Option, which allows you to access multiple data sources in a project, as described in Accessing multiple data sources in a project.

• Tables available in the database instance: Displays tables that are located in the data source for the selected database instance, but have not been included in the project. You can add tables to the project by double-clicking the tables or by selecting the tables and then clicking >.

• Tables being used in the project: Displays tables that have been selected to be part of the project. You can remove tables from the project by double-clicking the tables or by selecting the tables and then clicking <.

You can add or remove all the tables from one section to the other by clicking << and >> buttons.

Warehouse Catalog has the following menu options.

Menu	Description
File
Save	Saves the current settings and status of the Warehouse Catalog.
Exit	Exits the Warehouse Catalog.
Tools
View Partitions	Displays the list of tables referred to by the selected partition mapping table in the Table Partitions dialog box. This option is enabled when a partition mapping table is selected.
Table Structure	Displays the structure of a table selected in the Warehouse Catalog.
Calculate Table Row Count	Calculates the number of rows in the selected tables.
Table Prefix	Allows you to add or remove a table prefix for the selected table.
Table Database Instances	This option allows you to support one of the following: MicroStrategy allows you to specify a secondary database instance for a table, which is used to support database gateways. For information on supporting database gateways, see Specifying a secondary database to support database gateways. If you have the MultiSource Option, you can add tables from multiple data sources into your project. For information on adding tables from multiple data sources into your project with the Warehouse Catalog, see Accessing multiple data sources in a project.
Import Prefix	Allows you to import the prefixes from the warehouse table name space.
Options	Allows you to specify various settings for the Warehouse Catalog such as changing the database instance, changing or assigning default table prefixes and structures, automatic mapping, row calculation, and so on.
Actions
Read the Warehouse Catalog	Allows you to update and reflect the changes done to tables in the warehouse.
Help	Displays MicroStrategy help options

Some of these options are also available through toolbar buttons and through right-click menus for quick access.

Viewing table structure

To view the table structure of a table, right-click any table in the Warehouse Catalog (see Accessing the Warehouse Catalog) and choose Table Structure from the shortcut menu. You can also select Table Structure from the Tools menu. The table structure of the selected table is displayed in the dialog box.

The dialog box displays the columns available in the selected table and the data type of each column. You can also click Update Structure to reflect any recent changes done to that table (see Updating table structure).

When the data type of one or more columns is modified, you get a warning message of this change, which provides the following options:

• Click OK to apply the change to this column in all the tables it appears.

• Click Cancel to undo all data type changes. This action results in no changes being applied to any tables or columns.

The warning message appears only if you have selected the Display a warning if the columns data types are modified when updating the table structure option in the Warehouse Catalog Options dialog box. This option is selected by default.

Updating table structure

Whenever the structure of the warehouse table changes you have to update the table structure in the Warehouse Catalog for the changes to reflect in the MicroStrategy system. Some examples of these type of changes are when you add, delete, or rename a column in a table associated with a project.

To update the structure of a table

1. Access the Warehouse Catalog for your project (see Accessing the Warehouse Catalog). The Warehouse Catalog opens.

2. In the Tables being used in the project list, right-click the table that has changed and select Update Structure.

   If the data type of one or more columns is modified, you receive a message warning of this change. Verify the changes from the information dialog box that opens and click OK to apply the change in this column to all the tables in which it appears.

3. Click Save and Close to close the Warehouse Catalog dialog box.

   • If no object definitions have changed, the warehouse structure gets updated completely with the Update Structure command. For example, this would apply if you rename a column in the table and the column is not being used in any fact expression.

   • If any of the object definitions have changed, the table structure is only partially updated with the Update Structure command. Then, you have to manually update the schema objects that depend on the outdated structure.

     For example, if you rename a column in a table, you have to manually update the facts that use this column. The procedure for manually updating the fact is as follows:

     1. Right-click the fact and select Edit. The Fact Editor opens.

     2. Select the fact expression and click Modify. The Modify Fact Expression dialog box opens.

     3. From the list of source tables select the source table from which the fact has been created. Edit the fact expression and click OK. You are returned to the Fact Editor.

     4. Click Save and Close to save the changes and close the Fact Editor.

     5. From the Schema menu, select Update Schema. The Schema Update dialog box opens.

     6. Click Update.

     7. Repeat the first two steps of this procedure to open the Warehouse Catalog and update the table structure.

     8. Click Save and Close to save the changes and close the Warehouse Catalog dialog box.

Viewing sample data

To view sample data from a table, right-click a table in the Warehouse Catalog (see Accessing the Warehouse Catalog) and choose Show Sample Data from the shortcut menu. You can also select Show Sample Data from the Tools menu. The first 100 rows of the table are returned as sample data in the Values dialog box.

To refresh the table data, click Reload table values.

Removing tables from the Warehouse Catalog that have been removed from their data source

When tables that are included in a project are removed from the data source that they were available in, you can use the Warehouse Catalog to remove these tables from the list of tables included in the project. This allows you to view an accurate list of tables that are included in the project from the selected data source.

The steps below show you how to perform this task using the Warehouse Catalog. To remove these tables using MicroStrategy Architect, see Removing tables.

If tables that were not included in a project are removed from the data source, these tables are automatically removed from the display of available tables in the Warehouse Catalog.

To remove the display of project tables that have been removed from the data source

1. In MicroStrategy Developer, log in to a project.

2. From the Schema menu, select Warehouse Catalog. The Warehouse Catalog opens.

3. From the Warehouse Catalog toolbar, click Check for deleted catalog tables. The Deleted Catalog Tables dialog box opens.

4. Select the Delete check box for a table to remove it from the Tables being used in the project pane.

5. After you have selected all the tables to delete, click OK to return to the Warehouse Catalog.

6. From the Action menu, select Read the Warehouse Catalog. All tables that were selected to be deleted in the Deleted Catalog Tables dialog box are removed from the Tables being used in the project pane.

7. Click Save and Close to save your changes and close the Warehouse Catalog.

Specifying a secondary database to support database gateways

MicroStrategy allows you to specify a secondary database instance for a table, which is used to support database gateways. For example, in your environment you might have a gateway between two databases such as an Oracle database and a DB2 database. One of them is the primary database and the other is the secondary database. The primary database receives all SQL requests and passes them to the correct database. From the perspective of MicroStrategy products in this environment, you need to define two database instances, one for the primary database and another for the secondary database. The default database instance for the project is set to be the primary database. In the Warehouse Catalog, you must set the secondary database instance for any tables that are found in the secondary database. This way, MicroStrategy products know how to generate SQL for each table.

If you use database gateway support, you cannot use the MultiSource Option feature to add tables from multiple data sources into your project. For information on adding tables from multiple data sources into your project with the Warehouse Catalog, see Accessing multiple data sources in a project.

To specify a secondary database for a table

1. Access the Warehouse Catalog for your project (see Accessing the Warehouse Catalog). The Warehouse Catalog opens.

2. Right-click a table being used in the project, (in the pane on the right side) and select Table Database Instances. The Available Database Instances dialog box opens.

3. In the Primary Database Instance drop-down list, select the primary database instance for the table.

4. Select one or more Secondary Database Instances.

   You cannot select the primary database instance as a secondary database instance.

5. Click OK to accept your changes and return to the Warehouse Catalog.

6. From the toolbar, select Save and Close to save your changes and close the Warehouse Catalog.