MicroStrategy ONE

Modifying data warehouse connection and operation defaults

You can specify various settings for data warehouse connection and operation defaults using the Warehouse Catalog. Example settings include changing the database instance, changing or assigning default table prefixes and structures, automatic mapping, row calculation, and so on. The settings are available from the Warehouse Catalog, by choosing Options from the Tools menu (see Accessing the Warehouse Catalog for steps to access the Warehouse Catalog). The Warehouse Catalog Options dialog box opens, which allows you to perform the following tasks:

Data warehouse connection and read operations

You can modify the database instance and database login used to connect the data warehouse to a project, as well as change how the database catalog tables are read. You can make these type of modification from the Catalog category, which is divided into the following subcategories:

  • Warehouse Connection: Select the desired database instance to use for the project as well as the custom database login.

    • Database Instance: You can select the primary database instance for the Warehouse Catalog from the drop-down list.

      The primary database instance acts as the main source of data for a project and is used as the default database instance for tables added to the project. Non-database related VLDB property settings are also inherited from the primary database instance.

      If the desired database instance does not appear in the Database Instance box, or if it does but needs to be modified, you can select from the following:

      • Click Edit to modify the selected database instance. The General tab of the Database Instances dialog box opens.

      • Click New to create a new database instance. The Database Instance Wizard opens.

      Refer to the System Administration Help for more information on either of these dialog boxes.

    • Custom Database Login: You can either select the database login or clear the login to use no database login.

      For more information on the database login, see the MicroStrategy Developer Help (formerly the MicroStrategy Desktop Help).

  • Read Settings: You can define how database catalog tables are retrieved:

    • Use standard ODBC calls to obtain the database catalog: This option is available if you connect to a database type that supports ODBC calls to retrieve the database catalog tables. If you select this option, standard ODBC calls are used to retrieve the database catalog tables. If you use a Microsoft Access database, ODBC calls are automatically used. If you select this option and the results are not retrieving the database catalog tables correctly, you can select the SQL statement option listed below to customize how the database catalog tables are retrieved.

    • Use one or more SQL statements that query directly the database catalog tables: This option is available if you connect to a database type that supports ODBC calls to retrieve the database catalog tables. Otherwise, this is option is not shown and only the Settings button is displayed. If you select this option, SQL statements are used to retrieve the database catalog tables.

      You can customize the SQL to read the Warehouse Catalog for every platform (except Microsoft Access), by clicking Settings. This allows you to directly edit the catalog SQL statements that are used to retrieve the list of available tables from the Warehouse Catalog and the columns for the selected tables. The default catalog SQL retrieves a DISTINCT list of tables and columns from all users. You could restrict the information returned, for example, by specifying certain conditions and table owners (see Customizing catalog SQL statements).

    You can also choose from the following options to read the database catalog tables:

    • Read the table Primary and Foreign Keys: Select this option to display, in MicroStrategy, which columns are defined as primary keys or foreign keys in the data source. Primary keys and foreign keys can help facilitate joining tables to create Query Builder reports, as described in the Advanced Reporting Help.

      Displaying primary key or foreign key information in MicroStrategy can also help users designing a project to determine which columns of data may be suitable to serve as the identification columns of attributes. For information on defining the primary key for tables included in a MicroStrategy project, see Defining the primary key for a table.

    • Count the number of rows for all tables when reading the database catalog: Select this option if you want to control whether the Warehouse Catalog should get the number of rows each table has when loading from the data warehouse. This option is helpful when you want to identify fact tables and aggregation tables. If performance is more important than obtaining the row count, do not select this option as it will have a negative effect on performance. By default this option is selected when you open the Warehouse Catalog for the first time.

    • Ignore current table name space when reading from the database catalog and update using new table name space: This option allows you to switch between warehouses found in different database name spaces. For more information, see Ignoring table name spaces when migrating tables of this appendix. By default this option is selected.

    • Display a warning if the column data types are modified when updating the table structure: Select this option if you want to be warned when the data type for a column stored in the project is different from the one read from the data warehouse. The check for the data type change is only performed when updating a table's structure. By default this option is selected.

    • Automatically update information for all Partition Mapping tables when reading the database catalog: Select this option to read the latest information for the partition mapping tables (PMTs) currently present in the project. This setting should be cleared when the number of PMTs in the project is so large that reading their structure is causing performance problems when opening the Warehouse Catalog. By default this option is selected.

    • Column Merging Options: When you add a new table to your data warehouse, it may redefine the data type for a column included in the project. For example, your project includes a table named Table1 that has column C1 of data type char(1). Then a new table named Table2 is added to the project, but it has column C1 set to data type char(4). This example is used to illustrate the options described below. When you update the table structure, the column data types are modified to maintain a consistent schema in one of three ways, depending on the option you select.

      The options below do not handle the merge if the data type has changed to an incompatible data type. For example, a column is changed from data type char to data type integer. If the data type has changed to an incompatible data type, a warning is displayed and you are asked if you want to use the new data type.

      • Use most recent data type: This option updates the column data type to use the most recent column definition. In the example above, the column data type for C1 would be changed to char(4) since Table2 was added after Table1.

      • Use maximum denominator data type: This option updates the column data type to use the data type with the largest precision or scale. In the example above, the column data type for C1 would be changed to char(4), as defined in Table2. This is because char(4) has a higher precision than char(1) defined in Table1. If the data type has been changed to a different compatible data type, the data type with the largest precision or scale is used, as illustrated in the image below.

      • Do not merge: This option renames the column in the newly added table, which allows the columns to have different data types. From the example above, column C1 uses the char(1) data type for Table1. Column C1 in Table2 is defined as a separate copy of C1 and uses the char(4) data type. This option can cause unwanted schema changes and should be used only when necessary.

  • Read Mode: The Warehouse Catalog can be automatically read upon opening the Warehouse Catalog, or restricted to only be read when a read is manually requested:

    • Automatic: This option sets the Warehouse Catalog tables to be read as soon as the catalog browser is loaded.

    • Manual: This option sets the Warehouse Catalog tables to be read only when the read catalog action is selected.

Displaying table prefixes, row counts, and name spaces

You can choose to show or hide table prefixes, row counts, and name spaces, by using the View category. This category is divided into the following subcategories:

  • Table Prefixes: You can specify whether table prefixes are displayed in table names and how prefixes are automatically defined for tables that are added to the project. You have the following options:

    • Display table prefixes in the main dialog: Select this option to display all prefixes in table names, including new tables added to the project. By default this option is selected.

    • Automatically define prefixes for all tables that are added to this project: This setting enables/disables the following options:

      • Set a prefix based on the warehouse table name space or owner (import prefix): When this option is selected, the Warehouse Catalog reads the name space for each table being added, creates a prefix having the same text as the name space, and associates it with the table being added.

      • Set a default prefix: Select this to add a prefix to tables when they are added to a project. This option is only active when the database supports prefixes. You can select the default prefix from the Default prefix box drop-down list or create a new table prefix by clicking Modify prefix list.

      • Modify prefix list: You can create a new tables prefix or delete an existing prefix by selecting this option. The Table Prefixes dialog box opens. For more information on modifying the prefix list, see the online help.

  • Table Row Counts: You can show or hide the number of rows per table, using the check box:

    • Display the number of rows per table: You can show or hide the values calculated for the number of rows for the tables. By default, this option is selected and the number of rows are shown.

  • Table Name Spaces: You can show or hide the name space for each table, using the check box:

    • Display the name space for each table (if applicable): You can show or hide the owner or table name space where the table is located in the warehouse. By default, this option is selected and table name spaces are shown.

Mapping schema objects and calculating logical sizes for tables

The Schema category is divided into the following subcategories:

  • Automatic Mapping: When you add new tables to the Warehouse Catalog, you can determine whether existing schema objects in the project are mapped to these new tables automatically, using the following options:

    • Map schema objects to new tables automatically: Existing objects in the schema automatically map to tables you add to the project.

    • Do not map schema objects to the new tables: Objects in the schema are not automatically mapped to tables you add to the project.

    These automatic mapping methods are only applied to existing schema objects when tables are added to the Warehouse Catalog. For example, the attribute Year with an attribute form mapped to YEAR_ID is included in a project. Then a new table which includes a YEAR_ID column is added to the Warehouse Catalog. With the Map schema objects to new tables automatically option selected, the Year attribute is automatically mapped when the new table is added.

    If the table was added to the Warehouse Catalog first and then the attribute was created, the Warehouse Catalog automatic mapping settings do not determine whether the attribute and table are automatically mapped. Automatically mapping tables to schema objects when adding attributes or facts to a project is controlled by the Attribute Editor and Fact Editor, respectively.

  • Table Logical Sizes: You can select whether the Warehouse Catalog calculates logical sizes for new tables using one of the following options:

    • Calculate the logical table sizes automatically: Logical sizes are automatically calculated for tables you add to the project.

    • Do not calculate table logical sizes: Logical sizes are not calculated for the tables you add to the project.

Ignoring table name spaces when migrating tables

It is a common practice to establish a secondary warehouse with less information than the primary warehouse for development and testing. Before going into production, you change the project to point to the primary warehouse.

Most database management systems (Oracle, DB2, and others) support the concept of a table name space, which is a way of organizing database tables into different storage spaces. This method allows you to repeat the same table name in different table name spaces. For instance, you can have LU_STORE in a table name space called dbo and another table LU_STORE in another table name space called admin. You now have two tables dbo.LU_STORE and admin.LU_STORE. The table name space provides an extra piece of information that uniquely identifies the table.

When you add tables to a project, the Warehouse Catalog saves information to the appropriate table name space. This can cause a problem when you migrate from a warehouse that resides in a certain table name space to another warehouse in a different table name space. The Warehouse Catalog interprets the table as already in the project and not found in the new warehouse. This is because the Warehouse Catalog is looking for a table named dbo.LU_STORE, and the table is actually stored as admin.LU_STORE in the new production warehouse.

To solve this problem, select the Ignore current table name space when reading from the database catalog and update using new table name space check box. You can find this option in the Warehouse Catalog Options dialog box under the Catalog - Read Settings options subcategory. If you select this option, the Warehouse Catalog ignores the current table name space when it reads the catalog information. Thus, the Warehouse Catalog recognizes the two tables as the same table and saves the new table name space information. This setting allows you to migrate much more easily between warehouses. If the check box is cleared, the Warehouse Catalog defaults to identifying the table by both table name space and table name.