DAX-查询计划

DAX-查询计划，有四个：

1. DAX VertiPaq Logical Plan
2. DAX VertiPaq Physical Plan
3. DAX DirectQuery Algebrizer Tree
4. DAX DirectQuery Logical Plan

运算符类型：

 

 

Plan Type	Operator Type	Description
Logical Plan	ScaLogOp	Scalar Logical Operator Outputs a scalar value of type numeric, string, Boolean, etc.
	RelLogOp	Relational Logical Operator Outputs a table of columns and rows.
Physical Plan	LookupPhyOp	Lookup Physical Operator Given a current row as input, calculates and returns a scalar value.
	IterPhyOp	Iterator Physical Operator Given a current row as an optional input, returns a sequence of rows.

 

实际上，物理计划还有第三个： SPOOL，它是执行子树的内存结果节点。

 

共同属性：

1， LOGICAL PLAN - SCALOGOP

DEPENDONCOLS:

它是逻辑运算符依靠的查询树左边的列。用于确认MEASURE是否正确依靠行环境。

DATA TYPE:

6个类型，加 BLANK.

DOMINANTVALUE:

如果它是NONE，就是 DENSE，不是， 就是 SPARSE。类似MDX 的 BLOCK AND CELL-BY-CELL MODE。

2，LOGICAL PLAN - RELLOGOP

DEPENDON\COLS:

同上。

RANGE OF COLUMN NUMBERS:

DAX 使用 BEGIN-COLUMN-NAME END-COLUMN-NAME。

REQUIREDCOLS:

它是 DEPENDONCOLS 和 RANGE COLUMNS 的合集。

3，PHYSICAL PLAN - LOOKUPPHYOP

LOOKUPCOLS:

遍历器提供的列用于计算单值。

DATA TYPE:

同上。

4，PHYSICAL PLAN - ITERPHYOP

LOOKUPCOLS:

同上。

ITERCOLS:

遍历器输出列。

 

一个特殊的属性： SPOOL_ITERONLY<>

它就是一个遍历器，从内存SPOOL中得到行。

 

VERTIPAQ 运算符

 

 

Logical Operators	Description	Example
Scan_Vertipaq	This RelLogOp is the foundation of all other Vertipaq logical operators. It represents a basic Vertipaq query that joins a root table with related tables following many-to-one relationships, determines which rows are retrieved by testing against Vertiscan predicates, and groups the resultset by output columns.	evaluate ‘Product’
GroupBy_Vertipaq	This RelLogOp renames columns and adds rollup columns to a Vertipaq query.	evaluate summarize(‘Product’, rollup(‘Product Category’[Product Category Name], ‘Product’[Product Name]))
Filter_Vertipaq	This RelLogOp adds a Verticalc predicate to a Vertipaq query.	evaluate filter('Product', right([Product Name], 4) = "Tire")
Sum_Vertipaq	This ScaLogOp adds a SUM aggregation to a Vertipaq query.	evaluate row("x", sum('Internet Sales'[Sales Amount]))
Min_Vertipaq	This ScaLogOp adds a MIN aggregation to a Vertipaq query.	evaluate row("x", min('Internet Sales'[Sales Amount]))
Max_Vertipaq	This ScaLogOp adds a MAX aggregation to a Vertipaq query.	evaluate row("x", max('Internet Sales'[Sales Amount]))
Count_Vertipaq	This ScaLogOp adds a COUNT aggregation to a Vertipaq query.	evaluate row("x", countrows('Internet Sales'))
DistinctCount_Vertipaq	This ScaLogOp adds a DISTINCTCOUNT aggregation to a Vertipaq query.	evaluate row("x", distinctcount('Internet Sales'[Due Date]))
Average_Vertipaq	This ScaLogOp adds an AVERAGE aggregation to a Vertipaq query.	evaluate row("x", average('Internet Sales'[Sales Amount]))
Stdev.S_Vertipaq	This ScaLogOp adds a STDEV.S aggregation to a Vertipaq query.	evaluate row("x", stdev.s('Internet Sales'[Sales Amount]))
Stdev.P_Vertipaq	This ScaLogOp adds a STDEV.P aggregation to a Vertipaq query.	evaluate row("x", stdev.p('Internet Sales'[Sales Amount]))
Var.S_Vertipaq	This ScaLogOp adds a VAR.S aggregation to a Vertipaq query.	evaluate row("x", var.s('Internet Sales'[Sales Amount]))
Var.P_Vertipaq	This ScaLogOp adds a VAR.P aggregation to a Vertipaq query.	evaluate row("x", var.p('Internet Sales'[Sales Amount]))
Physical Operators
VertipaqResult	This IterPhyOp iterates over the resultset returned by a Vertipaq query.	You can find this operator in the physical plan tree after running any of the above queries.

 

当执行简单的运算，速度最快，如果复杂的，

需要 DAX FE 的帮助，速度变慢。由于 FE 是单线程的，如果计算使用 VERTIPAQ ENGINE，多线程，批量处理，快很多。

因此，查询计划如果使用VERTIPAQ 运算符，是非常好的。

 

几点注意：

1，SCAN_VERTIPAQ是其他的基础

2，SPOOL 可以被查询，通过： SPOOL LOOKUPPHYOP, SPOOL_ITERONLY, SPOOL_LOOKUPONLY, SPOOL_SLICEINDEX ITERPHYOPS.

一个非常重要的特征就是： #RECORDS, VERTIPAQ ENGINE多少记录返回 ，决定计划优劣的重要标志。

3，如果使用 FE，物理计划树并不体现。

4，SCAN_VERTIPAQ的特殊属性，主要包括：TABLE, BLANK ROW, JOINCOLS, SEMIJOINCOLS,

 

最后，比较两个查询：

 

A:

evaluate
            addcolumns(
                        'Date',
                        "x",
                        sumx(
                                    filter('Internet Sales', [Order Date] <= [Date]),
                                    [Sales Amount]
                        )
            )

B:

evaluate
            addcolumns(
                        'Date',
                        "x",
                        calculate(
                                    sum('Internet Sales'[Sales Amount]),
                                    'Internet Sales'[Order Date] <= earlier([Date]),
                                    all('Date')
                        )
            )

 

那一个好呢？

如果检查查询计划，就会发现：

A： FE 使用 SUMX AND FILTER;

B: FE 仅用 FILTER，SUMX 由 VERTIPAQ ENGINE 的 SUM 完成。

毫无疑问， 计划 B 好于 A.