target is the attribute level at which the metric calculation will group

2.Grouping

None

GROUP BY is often omitted from the SQL pass for the level metric; if there is an unrelated attribute on the template, the level metric will still select and group by that unrelated attribute.

Does not group by the target attribute or any of its children.

Standard

Groups by the attribute level specified in the target

determine how the metric will be aggregated

1.Filtering

none

e.g. the level metric will be calculated from the most detailed fact table, ITEM_EMP_SLS, instead of referencing the higher level fact table, CITY_CTR_SALES, because its target is ITEM and it is defined with None filtering and None grouping.

Points the calculation of the level metric to a particular table

Typically used in combination with None grouping option and referencing a paticular attribute as the target

ignore

Completely ignore any related filtering criteria;不相关的即不在一个schema的不影响；

absolute

Place the filtering criteria that are related to the target in a subquery

Raise the level of the filter to the target level;

not only the ones that are in the filter or in the report template

standard

affects the WHERE clause

commonly used to achieve a percentage contribution compound metric

used to control the level of aggregation of a metric on a report

where, group by, and /or from

Formula

select , and aggregate functions(sum, count, avg, min, max)

Non-aggregate Metrics

A simple metric that does not aggregate across a particular attribute or hierarchy.

e.g. Inventory or stock calculations.

An inventory metric may aggregate across Geography and Product, but not for Time.

Non-aggregatable options

Beginning Value

Sets the aggregation to the beginning on hand values (BOH)

Ending Value

Sets the aggregation to the ending on hand values(EOH)

first or last value in the lookup table, using min() or max() function 比如说在Month_ID上用min或者max函数

Fact table verse Lookup Table

fact table

temporary tables are used

lookup table

subquery is used

sets in Metric Level, Grouping

It's recommended to use hierarchy in the template. e.g. use "Time" hierarchy，然后在metric level中将target设成Time， Grouping设成beginning，就可以使得所有Time related的attributes都成为non-aggregatable的。

Conditional Metrics

Metric definition that contains filtering criteria

只能引用一个filter，这个filter里可以有多个qualification

Transformations

Used for time-series analysis.

e.g. This year Sales compared to Last Year Sales, or Month to Date calculations.

Time-series analysis presents a difficult challenge because it cannot be assumed that each day maps to the same day form the previous year.

examples:

Financial Calendars

Monday to Monday analysis

this is a common scenario in Retail

Floating holidays

Company buy-outs

Expression-based Transformation

Table-based Transformation

which are schema objects

What are the distinguishing parts of SQL for a transformation metric

The transformation table will be referenced in SQL, in the 'FROM" clause, and particular joins will be performed.

Nested Metrics

enable users to break calculations into many levels

each level can have its own dimensionality, conditionality, and transformations.

scenario

How can I find the Minimum Item Revenue for each Category?

schema

Category->Subcate->Item->Fact_Table

Metric: Min(Sum(Revenue){Item}){Report Level}

inner: Item Revenue for each category; outer: Minimum Item Revenue for each category

Metric Join Type

types

Inner Join

Outer Join

can be changed via

metric editor

report data option

for all the metrics in the report， overwrite the settings in metric editor

Significantly reduces the number of SQL passes that the MicroStrategy SQL Engine generates, by

eliminating unused passes

reusing intermediate SQL passes

combining SQL passes

values

level 0: No optimization

level 1: Remove unused and duplicate passes

level 2: level 1 + Merge passes with different SELECT

level 3 : level 2 + merge passes, which only hit DB table, with different WHERE

level 4 : level 2 + merge All passes with different where (this is the default value)

Select/Insert Statements

Tables

Intermediate Table Types

Defines the type of object used to store intermediate results for multipass SQL.

values

permanent table

by using "Create Table"

1 more item...

Derived table

by using sub-query (nested)

true temporary table

create table #zzmd00

common table expression , DB2 UDB only

with a21 as (sub-query for temp table) ; select ** from a21

no "create table"

temporary view

create view zzmd00

MicroStrategy's general recommendation is to use derived tables and common table expressions in place of permanent tables whenever possible,depending on your database platform. Because derived tables and common table expressions don't use 'create table" and therefore no need of "drop table".

FallBack Table Type

If the Intermediate Table Type VLDB property is set to either derived tables, common table expressions, or views, you may run into a scenario where a particular reporting requirement cannot be resolved using that intermediate table type.

In this case, the Engine needs to refer to the Fallback Table Type to know how to store intermediate result sets when resolving the report.

values

Permanent Table

True Temporary Table

Fail Report

Maximum SQL Passes Before Fallback / Maximum Tables in FROM clause before Fallback

If the report exceeds this number of SQL passes, then the FallBack Table Type will be used for intermediate tables instead of Intermediate Table Type .

Designed to increase the number of reports that can use derived tables or common table expressions as the intermediate table types.

basic optimizations

multipass SQL

example scenarios

reports with more than one metric(stored in separate fact tables)

reports using metric qualifications (filtering is based on metric qualification)

reports using custom groups(which use metric qualifications)

reports using metrics defined at different levels(e.g. one in report level, the other defined in a metric qualificaiton)

simulating outer joins on RDBMS platforms that do not support them natively

and others

Composing optimized SQL

composing the SELECT and GROUP BY clauses: use max() on description columns, to avoid them appearing in the GROUP BY clause:因为使用group by的时候是对description column进行排序，而使用max()的时候是对id column进行排序，默认的情况是对id column排序比较快； Optimization technique for attribute descriptions in the select clause;

optimizing the FROM clause: by default, the MSTR engine orders tables in the FROM clause in the following order: (1) Fact tables (2)Metric Qualification(MQ) tables (3)Relationship tables (4)Lookup tables 对多数的现在DBMS，顺序是没有关系的，但是有些数据库是有关系的，在VLDB settings里可以修改；

the SQL engine also tries to optimize how it performs the joins between tables;

If the two tables to join have more than one column in common, the column corresponding to the lowest level attribute in each table will be referenced to perform the necessary joins; e.g. call-center or region? call-center will be chosen because it's the lower level in hierarchy;

how you define the relationships between attributes in MSTR Architect will ultimately affect how the SQL engine performs these joins;