MicroStrategy ONE

Customizing catalog SQL statements

In all supported warehouse platforms other than Microsoft Access, MicroStrategy uses SQL statements to query the relational database management system (RDBMS) catalog tables to obtain warehouse catalog information. This information includes catalog tables, columns, and their data types.

These catalog SQL statements vary from platform to platform and can be customized according to the characteristics of the specific warehouse.

Microsoft Access does not have catalog tables, so an ODBC call must be used to retrieve information about tables and columns in Access. By default, a similar ODBC call is used for the Generic DBMS database type, but you can choose to use custom catalog SQL for the generic type if you want.

The MicroStrategy Warehouse Catalog can be configured to read the catalog information in one- or two-pass SQL mode. In two-pass SQL mode, it first reads only the tables from the database. The structure of individual tables is read only when the table is selected. This is the recommended option for interactive warehouse catalog building because no unnecessary catalog information is read from the database, which increases processing speed. One-pass SQL mode, on the other hand, reads all the tables and columns in one SQL statement. This option is recommended only if the catalog SQL is well customized to limit the amount of data returned by it.

The two retrieval options use different catalog SQL, but both can be customized in the Warehouse Catalog Options dialog box. In the following sections, the name Catalog Table SQL refers to the catalog SQL to retrieve the tables in the warehouse; that is, the first SQL used in a two-pass catalog retrieval.

The name Full Catalog SQL refers to the SQL used to read all the tables and columns in one pass.

To customize a catalog SQL, you must understand several important concepts and procedures:

The table name space

In a typical RDBMS platform, a table name does not uniquely identify it in a particular database installation. A table name space is a partition of the database installation in which table names are unique. Depending on the type of RDBMS, this name space can be the name of the database, the owner of the table, or a combination of both database and owner. In both the Catalog Table SQL and Full Catalog SQL, a name space gives each table a unique name. This helps you to avoid confusing tables that share the same table name.

The table name space is optional. A customized catalog SQL can omit the name space if duplicate table names do not present a problem in the warehouse database.

SQL placeholder strings and incomplete catalog SQL

The default system catalog SQL can contain certain placeholder strings that can be resolved at run time or must be completed manually by the user. These placeholders are:

  • #LOGIN_NAME#—This placeholder is automatically replaced at run time with the login name used to connect to the database. You can leave this template in the customized SQL if you want the catalog SQL to yield different results depending on the warehouse login used. Otherwise, this template is replaced with the name of the database user who owns the warehouse tables of interest.

  • #?Database_Name?#, #?Schema_Name?#—This catalog SQL placeholder is an incomplete SQL string that must be completed by the user before it can be executed. The string starts with #? and ends with ?#. The command #?Database_Name?#, used with Teradata, must be replaced with the name of the database containing the database tables. #?Schema_Name?#, used with DB2 AS/400 and MySQL, must be replaced with the name of the schema in which the database tables for the project reside.

Structure of Catalog Table SQL

Catalog Table SQL is expected to return two columns, one identifying the name space of the table and the other the name of the table. If a name space is not provided, only the table name column is required. Each row of the SQL result must uniquely identify a table. Duplicates are not allowed. The column that identifies the table name space uses the SQL column alias NAME_SPACE. The column that identifies the table name has the alias TAB_NAME. The following example is the default Catalog Table SQL for Oracle 8.0:

SELECT DISTINCT OWNER NAME_SPACE, TABLE_NAME TAB_NAME
FROM ALL_TAB_COLUMNS
WHERE OWNER = '#LOGIN_NAME#'

Structure of Full Catalog SQL

Full Catalog SQL is expected to return between five and seven columns, depending on the RDBMS platform and the customization.

The following aliases identify each column returned:

  • NAME_SPACE (optional): the table name space

  • TAB_NAME (required): name of the table

  • COL_NAME (required): name of the column

  • DATA_TYPE (required): a string or a number that identifies the major data type of the column

  • DATA_LEN (required): a number that describes the length or size of the column data

  • DATA_PREC (optional): a number that describes the precision of the column data

  • DATA_SCALE (optional): a number that describes the scale of a floating point column data

Full Catalog SQL must return its rows ordered first by NAME_SPACE, if available, and then by TAB_NAME.

The following example is the default Full Catalog SQL for Microsoft SQL Server 7.0:

SELECT U.name NAME_SPACE, T.name TAB_NAME, C.name COL_NAME, C.type DATA_TYPE, C.length DATA_LEN, C.prec DATA_PREC, C.scale DATA_SCALE
FROM sysobjects T, syscolumns C, sysusers
WHERE T.id = C.id and T.type in ('U', 'V')
AND T.uid = U.uid
ORDER BY 1, 2

Modifying catalog SQL

You can customize and modify the catalog SQL that is run against your database for each project. The catalog SQL can be modified in the Warehouse Catalog options for your project.

To modify the catalog SQL for your project

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

  2. From the Tools menu, select Options. The Warehouse Catalog Options dialog box opens.

  3. Expand the Catalog Category, and select Read Settings. The Catalog - Read Settings options are displayed.

  4. Click the Settings button, the catalog SQL options are displayed as shown below.

    The catalog SQL settings are unavailable if your project is connected to a Microsoft Access database.

The top pane controls the Catalog Table SQL and the bottom pane controls the Full Catalog SQL.

Default catalog SQL

When customizing the catalog SQL that is executed on your database, it is recommended you consult the default catalog SQL that MicroStrategy uses to support different database platforms. You can generate the default catalog SQL in MicroStrategy for the database platform your project connects to.

To generate and view the default catalog SQL

  1. Access the catalog SQL options for your project (see Modifying catalog SQL). A dialog box for the catalog SQL options is displayed.

    • The top pane controls the Catalog Table SQL, which retrieves a list of available tables in the Warehouse Catalog.

    • The bottom pane controls the Full Catalog SQL, which retrieves column information for the selected tables.

Before performing the next step, cut and paste the SQL statements in the two panes into any text editor. This allows you to save any modifications you have made previously to the catalog SQL statements, and then compare them to the default statements you are about to generate.

  1. Generate and view the default catalog SQL for your database platform. Any text in the panes is overwritten with the default catalog SQL statements:

    • To generate and view the default Catalog Table SQL for your database platform, click the upper-most Use Default button.

    • To generate and view the default Full Catalog SQL for your database platform, click the bottom-most Use Default button.

You can use the default catalog SQL statements or compare and combine them with your own customized catalog SQL statements.