The Currency Data Type and VertiPaq Caching

It’s been a while since I published my last blog post. That’s not for lack of topics to write, but rather for lack of spare time. I switched from a developer role to a development lead role a year and a half ago, while life has always been busy at Microsoft, the extra responsibilities in the new role had put too much on my plate. Moreover there have been a lot of changes here at Microsoft to get the company transition into a “devices and services” company, the flurry of initiatives and refocuses has kept many people extra busy in the past year.

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.