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.
 

58 comments:


  1. I like the way on how you put up your blogs. Wonderful and awesome. Hope to read more post from you in the future. Goodluck. Happy blogging!

    Bubble
    www.gofastek.com

    ReplyDelete
    Replies
    1. Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. big data projects for students But it’s not the amount of data that’s important.Project Center in Chennai

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Corporate TRaining Spring Framework the authors explore the idea of using Java in Big Data platforms.

      Spring Training in Chennai

      The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  2. The mind is a great philosopher. And life is not a philosophy, life is a reality. And philosophy is an escape from reality; philosophy means thinking. Life is, there is no question of thought. You can simply jump into it. You can simply experience it. Visit my site for more information.Thank you.

    n8fan.net

    www.n8fan.net

    ReplyDelete
  3. In near future, big data handling and processing is going to the future of IT industry. Thus taking Hadoop Training in Chennai | Big Data Training in Chennai will prove beneficial for talented professionals.

    ReplyDelete
  4. 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

  5. Wonderful blog.. Thanks for sharing informative Post. Its very useful to me.

    Installment loans
    Payday loans
    Title loans

    ReplyDelete
  6. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

    Installment loans
    Payday loans
    Title loans
    Cash Advances

    ReplyDelete
  7. 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
  8. I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. Power BI Online Training Bangalore

    ReplyDelete
  9. Thanks for sharing this post. Your post is really very helpful its students. Power BI Online Training

    ReplyDelete
  10. myTectra Placement Portal is a Web based portal brings Potentials Employers and myTectra Candidates on a common platform for placement assistance

    ReplyDelete
  11. 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
  12. 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
  13. I found your blog while searching for the updates, I am happy to be here. Very useful content and also easily understandable providing.. Believe me I did wrote an post about tutorials for beginners with reference of your blog.

    rpa training in chennai |rpa course in chennai|
    rpa training in bangalore | best rpa training in bangalore | rpa course in bangalore | rpa training institute in bangalore | rpa training in bangalore | rpa online training

    ReplyDelete
  14. Its really an Excellent post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog. Thanks for sharing....
    Best Devops online Training
    Online DevOps Certification Course - Gangboard

    ReplyDelete
  15. Useful information.I am actual blessed to read this article.thanks for giving us this advantageous information.I acknowledge this post.and I would like bookmark this post.Thanks
    Data Science training in rajaji nagar | Data Science Training in Bangalore
    Data Science with Python training in chennai
    Data Science training in electronic city
    Data Science training in USA
    Data science training in pune

    ReplyDelete

  16. 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
  17. Thanks For Sharing The Information The Information shared Is Very Valuable Please Keep Updating Us Time Just Went On reading The Article Python Online Training Aws Online Course DataScience Online Course Devops Online Course

    ReplyDelete
  18. 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

  19. 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
  20. Thanks For Sharing The Information The Information Shared Is Very Valuable Please Keep Updating Us Time Just Went On Reading The article Python Online Course Hadoop Online Course Aws Online Course Data Science Online Course

    ReplyDelete
  21. The article really caught my eye. I plan to mark your website and continue to check for new details. In fact, I idolized by reading your diary, valuable data.

    DedicatedHosting4u.com

    ReplyDelete
  22. Nice Blog
    Yaaron Studios is one of the rapidly growing editing studios in Hyderabad. We are the best Video Editing services in Hyderabad. We provides best graphic works like logo reveals, corporate presentation Etc. And also we gives the best Outdoor/Indoor shoots and Ad Making services.
    Best video editing services in Hyderabad,ameerpet
    Best Graphic Designing services in Hyderabad,ameerpet­
    Best Ad Making services in Hyderabad,ameerpet­

    ReplyDelete
  23. 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
  24. I think this is an informative post and it is very beneficial and knowledgeable. Therefore, I would like to thank you for the endeavors that you have made in writing this article. All the content is absolutely well-researched.
    Visit Giant Brand Solutions
    wordpress bundle
    wordpress themes and plugins
    premium wordpress themes and plugins
    wp starter pack
    wordpress theme update
    wordpress update plugins
    wordpress website themes
    worpdress theme sites
    wordpressbundle
    wpstarterpack

    ReplyDelete
  25. 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
  26. Gone through this wonderful coures called Salesforce Certification Training in Dallas who are offering fully practical course, who parent is Salesforce Training in USA and they have students at Salesforce Training classes in Canada institutes.

    ReplyDelete
  27. Can I download WhatsApp Status with the New FMWhatsApp

    ReplyDelete