MicroStrategy ONE
Allow Index on Metric
Allow Index on Metric 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 Allow Index on Metric property determines whether or not to use fact or metric columns in index creation. You can see better performance in different environments, especially in Teradata, when you add the fact or metric column in the index. Usually, the indexes are created on attribute columns; but with this setting, the fact or metric columns are added as well. All fact or metric columns are added.
Levels at Which You Can Set This
Database instance, report, and template
Example
Do not allow creation of indexes on fact or metric columns (default)
create table ZZT8L005Y1YEA000 (
CATEGORY_ID BYTE,
REGION_ID BYTE,
YEAR_ID SHORT,
WJXBFS1 DOUBLE,
WJXBFS2 DOUBLE)
insert into ZZT8L005Y1YEA000
select a13.[CATEGORY_ID] AS CATEGORY_ID,
a15.[REGION_ID] AS REGION_ID,
a16.[YEAR_ID] AS YEAR_ID,
sum((a11.[QTY_SOLD] * (a11.[UNIT_PRICE] -
a11.[DISCOUNT]))) as WJXBFS1,
sum((a11.[QTY_SOLD] * ((a11.[UNIT_PRICE] -
a11.[DISCOUNT]) - a11.[UNIT_COST]))) as
WJXBFS2
from [ORDER_DETAIL] a11,
[LU_ITEM] a12,
[LU_SUBCATEG] a13,
[LU_EMPLOYEE] a14,
[LU_CALL_CTR] a15,
[LU_DAY] a16
where a11.[ITEM_ID] = a12.[ITEM_ID] and
a12.[SUBCAT_ID] = a13.[SUBCAT_ID] and
a11.[EMP_ID] = a14.[EMP_ID] and
a14.[CALL_CTR_ID] = a15.[CALL_CTR_ID] and
a11.[ORDER_DATE] = a16.[DAY_DATE]
and a15.[REGION_ID] in (1)
group by a13.[CATEGORY_ID],
a15.[REGION_ID],
a16.[YEAR_ID]
create index ZZT8L005Y1YEA000_i on ZZT8L005Y1YEA000
(CATEGORY_ID, REGION_ID, YEAR_ID)
Allow the creation of indexes on fact or metric columns
This example is the same as the example above except that the last line of code should be replaced with the following:
create index ZZT8L005YAGEA000_i on ZZT8L005YAGEA000
(CATEGORY_ID, REGION_ID, YEAR_ID, WJXBFS1, WJXBFS2)
Index Prefix
This property allows you to define the prefix to add to the beginning of the CREATE INDEX statement when automatically creating indexes for intermediate SQL passes.
For example, the index prefix you define appears in the CREATE INDEX statement as shown below:
create index(index prefix)
IDX_TEMP1(STORE_ID, STORE_DESC)
Levels at Which You Can Set This
Database instance, report, and template