Logical Tables » Logical tables

Logical tables

Logical tables are MicroStrategy objects that form the foundation of a schema. Physical tables in a data warehouse consist of columns, and logical tables in the MicroStrategy schema relate this column data to attributes and facts. These attributes and facts are part of the report definition that the MicroStrategy Engine refers to when a report is executed.

The types of logical tables are:

Logical table: A logical representation of a physical table that MicroStrategy uses to generate SQL. A logical table is created for each physical table that is imported into a project, using the Warehouse Catalog. This type of logical table maps directly to physical tables in the data warehouse. These physical tables are referenced in the SQL that is generated for the report.

This type of logical table is the most common logical table. Based on these tables, you can create MicroStrategy schema objects, such as attributes and facts. For more information on how to use the Warehouse Catalog, refer to the Help (search for “Warehouse Catalog”).

Logical tables also allow you to support the following configurations:

Defining logical table sizes
Defining the primary key for a table
Logical table alias: A logical table that uses a different name for a physical table. One physical table can have more than one logical table alias. Logical table aliases are used to create attribute roles.

When an attribute plays more than one role, you need to create an attribute in the logical model for each of the roles. To accomplish this, you create multiple logical table aliases pointing to the same physical table and define those logical table aliases as the lookup tables for the attributes in different roles.

For example, if the Customer table is used to represent both Ship to Customer and Bill to Customer, you can create a logical table alias to resolve the double usage case. First, create a logical table alias by copying the existing logical table and giving it a different name; then define the new attributes using the appropriate tables.

For detailed information on attribute roles, refer to Attributes that use the same lookup table: Attribute roles. To create a logical table alias, right-click the logical table name and select Create Table Alias. For step-by-step instructions, refer to the Help (search for “Create a table alias”).

Logical view: A logical table that is created using a SQL statement instead of being a direct representation of a physical table. Once created, the logical view can be used in the same way as any logical table. The logical view is also referenced in the SQL that is generated for the report; the whole SQL query is displayed in the place of physical tables, as is standard for other logical tables. Logical views are created using the Table Editor.

If your project supports data internationalization, you cannot use logical views as lookup tables for attributes that use translated data. For information on supporting data internationalization, see Supporting data internationalization.

Logical views are different from the above-mentioned logical tables and logical table aliases for the following reasons:

Logical views do not map directly to physical tables in the data warehouse, and instead are defined using SQL queries.
Logical views are manually created to return data, from potentially multiple physical tables, as one logical table. Logical tables and logical table aliases can only retrieve data from a single physical table.

However, once logical views are created, they can be used in the same way as logical tables and logical table aliases. This means that you can use the logical views to build attributes and facts, and that you can also create logical table aliases for the logical views.

The biggest benefit of using logical views is that you can model a MicroStrategy schema that cannot be supported with only the physical database structures in the data warehouse. Many common modeling scenarios are easier to manage using logical views. Examples are:

Slowly-changing dimensions
Attribute form expressions from multiple tables
Consolidated dimension tables
Recursive hierarchies

For common usage examples, refer to Logical view examples.

In the MicroStrategy Tutorial, logical tables and all the other schema objects are stored in the Schema Objects folder. Using the Logical Table Editor, you can define your logical view using a SQL statement, as well as view the content of all the logical tables and their associated warehouse tables.

Whenever you create or add logical tables, logical table aliases, or logical views to the project, you need to update the schema. The Update Schema option can be accessed from the Schema menu.