Saturday, June 15, 2013

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.
 

23 comments:

  1. You did a great job.. Thanks a lot for sharing this useful informative post with us.. Keep on blogging like this informative post with us, to develop my career in the right way.
    Android Training in Chennai Velachery

    ReplyDelete
  2. An attention-grabbing dialogue is price comment. I feel that it's best to write more on this subject, it might not be a taboo topic however generally people are not enough to speak on such topics. To the next. Cheers real money casino

    ReplyDelete
  3. After seeing your article I want to say that the presentation is very good and also a well-written article with some very good information which is very useful for the readers....thanks for sharing it and do share more posts like this.
    python training in chennai
    python course in chennai
    python training in bangalore

    ReplyDelete
  4. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    Selenium Online training | Selenium Certification Online course-Gangboard

    Selenium interview questions and answers

    Selenium interview questions and answers

    Selenium Online training | Selenium Certification Online course

    ReplyDelete

  5. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.

    AWS TRAINING IN BANGALORE|NO.1AWS TRAINING INSTITUTES IN BANGALORE

    ReplyDelete
  6. Very nice post here and thanks for it .I always like and such a super contents of these post.
    Excellent and very cool idea and great content of different kinds of the valuable information's.
    data science with python online training

    ReplyDelete

  7. read this above post its very greatful for me thanks sharing this post ,great post.
    Best Ice Fishing Gloves Best Ice Fishing Gloves Best Ice Fishing Gloves

    ReplyDelete
  8. Dr Driving is one of the my favourite game ever and today I am going to share Dr Driving Mod Apk
    https://www.drdrivingmodapk.xyz/

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    Selenium Testing Training
    Selenium Webdriver Tutorial
    Selenium Tutorial

    ReplyDelete
  11. Sarswatienterprises is a trusted Die Set Manufacturers, Power Press Manufacturer, and Flip off Seals Machinery in Delhi, India. For more information visit our website.
    Air Blower Machine Manufacturer in Delhi

    ReplyDelete
  12. Nice Blog. Visit for GeM Helpdesk Helpline, Vendor Assessment on GeM, Tender Information Services, and Gem Consultancy in Delhi NCR. Visit our website for more information in details.
    Tender Information Services

    ReplyDelete
  13. Amazing Blog. Visit AAR Fragrance for Perfumes for Men Online at Best Prices in India, Buy Perfumes for Women Online in India, Davidoff Cool Water For Men EDT 125ml, and Versace Pour Homme Dylan Blue EDT 100ml.
    Buy Perfumes for Women Online in India

    ReplyDelete
  14. Visit Orange Pill Clinic for leading Nursing and Doctor Consultation Services at Home, Home Nursing Services in Delhi NCR, and Nursing Services for Suturing Removal at Home. For more information visit our website.
    Nursing Services for Suturing Removal at Home

    ReplyDelete
  15. Nice information, thank you so much sharing with us. Visit Amfez for Thakur ji ke vastra, kanha ji poshak, and krishna dress at an affordable price.
    krishna dress

    ReplyDelete