Engine Attribute Role Options

Engine Attribute Role Options is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Engine Attribute Role Options property allows you to share an actual physical table to define multiple schema objects. There are two approaches for this feature:

• The first approach is a procedure called table aliasing, where you can define multiple logical tables in the schema that point to the same physical table, and then define different attributes and facts on these logical tables. Table aliasing provides you a little more control and is best when upgrading or when you have a complex schema. Table aliasing is described in detail in the Project Design Help.
• The second approach is called Engine Attribute Role. With this approach, rather than defining multiple logical tables, you only need to define multiple attributes and facts on the same table. The MicroStrategy Engine automatically detects "multiple roles" of certain attributes and splits the table into multiple tables internally. There is a limit on the number of tables into which a table can split. This limit is known as the Attribute Role limit. This limit is hard coded to 128 tables. If you are a new MicroStrategy user starting with 7i or later, it is suggested that you use the automatic detection (Engine Attribute Role) option.

The algorithm to split the table is as follows:

• If two attributes are defined on the same column from the same table, have the same expression, and are not related, it is implied that they are playing different roles and must be in different tables after the split.
• If two attributes are related to each other, they must stay in the same table after the split.
• Attributes should be kept in as many tables as possible as long as algorithm 1 is not violated.

Given the diversity of data modeling in projects, the above algorithm cannot be guaranteed to split tables correctly in all situations. Thus, this property is added in the VLDB properties to turn the Engine Attribute Role on or off. When the feature is turned off, the table splitting procedure is bypassed.

Correct Usage Example

Fact table FT1 contains the columns "Order_Day," "Ship_Day," and "Fact_1." Lookup table LU_DAY has columns "Day," "Month," and "Year." Attributes "Ship Day" and "Order Day" are defined on different columns in FT1, but they share the same column ("Day") on LU_DAY. Also the attributes "Ship Month" and "Order Month" share the same column "month" in LU_DAY. The "Ship Year" and "Order Year" attributes are the same as well. During the schema loading, the Analytical Engine detects the duplicated definitions of attributes on column "Day," "Month," and "Year." It automatically splits LU_DAY into two internal tables, LU_DAY(1) and LU_DAY(2), both having the same physical table name LU_DAY. As a result, the attributes "Ship Day," "Ship Month," and "Ship Year" are defined on LU_DAY(1) and "Order Day," "Order Month," and "Order Year" are defined on LU_DAY(2). Such table splitting allows you to display Fact_1 that is ordered last year and shipped this year.

The SQL appears as follows:

select a1.fact_1
from FT1 a1 join LU_DAY a2 on (a1.order_day=a2.day)
 join LU_DAY a3 on (a1.ship_day = a3.day)
where a2.year = 2002 and
a3.year = 2003

Note that LU_DAY appears twice in the SQL, playing different "roles." Also, note that in this example, the Analytical Engine does not split table FT1 because "Ship Day" and "Order Day" are defined on different columns.

Incorrect Usage Example

Fact table FT1 contains columns "day" and "fact_1." "Ship Day" and "Order Day" are defined on column "day." The Analytical Engine detects that these two attributes are defined on the same column and therefore splits FT1 into FT1(1) and FT1(2), with FT1(1) containing "Ship Day" and "Fact 1", and FT(2) containing "Order Day" and "Fact 1." If you put "Ship Day" and "Order Day" on the template, as well as a metric calculating "Fact 1," the Analytical Engine cannot find such a fact. Although externally, FT1 contains all the necessary attributes and facts, internally, "Fact 1" only exists on either "Ship Day" or "Order Day," but not both. In this case, to make the report work (although still incorrectly), you should turn OFF the Engine Attribute Role feature.

Levels at Which You Can Set This

Database instance only