I am writing today’s post since recently there was a long discussion
among some MVPs regarding a query performance concern in the Tabular database.
They have discovered that when a VertiPaq query performs certain arithmetic
operations on a column of Currency
data type, the calculation is not done directly inside the VertiPaq calculation
engine but rather done through a callback to the DAX calculation engine. For
example, if you issue the following MDX query against AdventureWorks
Tabular Model SQL Server 2012 and capture the VertiPaq SE Query Begin/End events inside SQL Server Profiler, you
would see CallbackDataID in the trace event to indicate a callback from the
VertiPaq calculation engine to the DAX calculation engine.
WITH MEASURE 'Internet Sales'[m] =
SUMX('Internet Sales',
[Unit Price] * [Unit Price Discount Pct])SELECT [m] ON 0,
[Date].[Calendar Year].[Calendar Year].MEMBERS ON 1
FROM [Model]
Some MVPs went on to investigate which combinations of data
types and operations would fall into this category. To save people a lot of
research time and unnecessary guesswork, I’ll list the cases right here.
First let me give you some background information on why
MVPs are interested in this behavior. The VertiPaq calculation engine has a set
of built-in operations it can perform in a very efficient fashion. If an
operation is not natively supported, it calls back to the DAX calculation
engine to perform the operation on its behalf. A callback operation is not as
efficient as a native operation, but more importantly, a VertiPaq query
containing callback operations is not added to the VertiPaq cache. If you run
the above query again, you will not see the VertiPaq SE Query Cache Match event for the VertiPaq query going to
the ‘Internet Sales’ table.
The following table lists cases when VertiPaq calculation
engine calls back to DAX calculation engine to perform arithmetic operations.
|
Arithmetic Operator
|
Left Operand Data Type
|
Right Operand Data Type
|
|
+
|
Currency
|
Non-Currency
|
|
+
|
Non-Currency
|
Currency
|
|
-
|
Currency
|
Non-Currency
|
|
-
|
Non-Currency
|
Currency
|
|
*
|
Currency
|
Currency
|
|
*
|
Currency
|
Real
|
|
*
|
Real
|
Currency
|
|
*
|
Currency
|
Date/Time
|
|
*
|
Date
|
Real
|
|
/
|
Non-Currency
|
Currency
|
|
/
|
Currency
|
Real
|
As you can see, all but one row in the table has at least
one operand of Currency data type and
you may wonder why Currency data type
is out of favor with the VertiPaq calculation engine. As it turns out, VertiPaq
engine stores Currency data as Integers, e.g. $1234.5678 is stored as
12345678. Now you may wonder why Integer
* Real is not on the list but Currency * Real is. The answer lies in DAX rules governing the data types of
the results of arithmetic operations. In DAX, the result of Integer * Real is Real, but the
result of Currency * Real is Currency. So when VertiPaq calculates $12.3456 * 7.8, it does 123456
* 7.8 = 962956.8 and then it has to round the result to 962957 that represents
$96.2957. Currently, VertiPaq calculation engine only supports a subset of
conversions between basic data types and delegates the rest of the type
conversions to DAX calculation engine. The following table lists the basic DAX
data type conversions supported natively in VertiPaq calculation engine.
|
From
|
To
|
|
Boolean
|
Integer
|
|
Boolean
|
Real
|
|
Integer
|
Real
|
|
Date/Time
|
Real
|
|
Boolean
|
Date/Time
|
|
Integer
|
Date/Time
|
|
Real
|
Date/Time
|
|
Integer
|
Boolean
|
|
Currency
|
Boolean
|
|
Date/Time
|
Boolean
|
|
Real
|
Boolean
|
Before we conclude today’s post, I want to add that this is
the behavior of SQL Server 2012 SP1. The Analysis Services team is busy adding
new capabilities to the product. Many of the limitations, such as VertiPaq
queries with callbacks missing VertiPaq cache, is likely to be lifted in a
future release.
