THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at https://www.sqlbi.com/blog/marco/ - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Don’t use SUMMARIZE to sum your data–or just be careful #dax #ssas #powerpivot

During the last PASS Summit I and Alberto Ferrari had long discussions at SQL Clinic with some of the developers of our loved tools. Sometime you really have to dig in the internals of certain feature to understand why there are some “unexpected” behaviors for certain functions. One of the discussions was about SUMMARIZE. This function can be very powerful (after all, it’s a way to do a join between related tables…) but also very dangerous, because of the way it implements its logic (especially for the ROLLUP condition).

The rule of thumb, that we already mentioned in the past, is to use SUMMARIZE only as a way to execute a sort of SELECT DISTINCT, and not to create column to aggregate values. Use ADDCOLUMNS for this other job. We previously mentioned mainly performance reasons for that, but now we have a more complete description of why you should avoid SUMMARIZE for computing aggregations: you might obtain a different result than the one expected. The complete discussion of the issue and of the workarounds is included in the new article All the secrets of SUMMARIZE written by Alberto Ferrari.

Published Tuesday, November 18, 2014 9:22 AM by Marco Russo (SQLBI)
Filed under: , ,

Comments

No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Privacy Statement