从 PowerBI 引擎之父的专访深入理解PowerBI

以下内容转自Lars的博客，参考原文。

Power BI 背后的团队：Jeffrey Wang

If you are dealing with Power BI/Power Pivot, it doesn’t take long before you encounter the DAX language for the first time. Jeffrey Wang is Principal Software Engineer Manager at Microsoft and is considered the father of DAX and the VertiPaq engine behind it.

如果你正在使用Power BI或者Power Pivot，你很快就会发现DAX语言。Jeffrey Wang是工作在背后的微软高级软件工程师，并被称为DAX / VertiPaq 引擎之父。

Lars: Jeffrey, I’m so glad you agreed to answer my questions. ? Would you like to briefly describe what your main tasks at Microsoft are?

Lars：Jeffrey,我很高兴你可以接受我的采访，首先可以简短地描述下您在微软的主要工作吗？

Jeffrey: I work on the Power BI team and am in charge of the development of the DAX engine.

Jeffrey：我在Power BI团队工作，负责DAX引擎的开发。

Lars: In the community your name is often labelled „the father of DAX„. How would you describe your role in the invention of DAX?

Lars：社区中都称你是DAX之父，那么您自己会怎么看您在DAX产生过程中的角色？

Jeffrey: The invention of DAX was a joint effort of many people. I was just one of the inventors, not even the most important ones. My contribution to DAX became more prominent later on when I became the manager of the development team. In my effort to spread DAX knowledge, I started to present at community meetings and some organizers began to introduce me that way in order to get more people to come to my talks ?

Jeffrey：DAX的诞生是很多人工作的结果，我只是其中一员，甚至并不是最重要的角色。只是在我负责DAX开发团队后，我的贡献似乎更明显了。在我传播DAX知识过程中，我参与了很多社区会议然后有的组织会介绍一些伙伴来听这些分享。

Lars: Why did you think you had to invent a new language then? What can DAX do that the existing languages could not?

Lars：当时你是怎么想到要创建一种新的语言？是不是有什么功能是当时其他语言无法实现而必须要创建一门新的语言了？

Jeffrey: We needed a language that can elegantly express core BI concepts, is friendly to business users without formal programming training, and can be easily implemented to handle billions of rows of data efficiently. We had considered three existing programming languages: Excel formulas, SQL, MDX. Excel formulas are the language of choice for business analysts, the audience we were targeting for self-service BI, but it lacked basic constructs to deal with relational database operations on top of structured data stored in tables and columns. SQL is a great language to express operations on structured data of any size. It just lacks the core BI concept of measure and is too verbose to express common BI query patterns. Some BI vendors chose to extend SQL to support BI measures. They all ended up being very hacky and confusing since they kind of broke SQL by making SQL-like expressions return un-SQL-ly results. Finally, MDX was a successful niche language in the BI market but it’s too hard for business people since it required users to learn too many multi-dimensional concepts, such as dimensions, attributes, hierarchies, etc. before they could understand the language. We built DAX on top of simple relational database concepts like tables, columns, and relationships which, unlike multi-dimensional ones, are intuitively understood by business analysts, and then we added the core BI ingredient of measures as a first-class citizen, and adopted the friendly format of Excel formulas along with a lot of Excel functions with which business users are already familiar.

Jeffrey：我们的初衷是设计一种更贴近业务人员使用的语言，而不需要正统的编程基础，这门语言可以很优雅地表示核心的BI概念并可以很容易驾驭多达数十亿行数据。当时我们参考了三种市面已有的语言：Excel公式，SQL，MDX。商业分析师（BA）一般会选择使用Excel，我们定位这类用户为自助BI用户，但Excel公式无法直接处理在数据库中的关系型数据。SQL是一种可以处理任意规模的很强大的语言，但是SQL并没有包含核心的BI概念（例如：度量值），而且用SQL处理常见的BI任务时在写法上并不是很高效。有些BI厂商扩展了SQL的能力来支持BI度量值这些概念，这从整个设计上来讲，这与SQL混在一起总是会导致一些混乱，不够清晰。而第三种MDX则是已被印证的定位于BI市场很成功的语言，但是MDX太难了，业务用户是很难掌握的，他们必须要学大量多维建模的概念，包括：维度，属性，层级等，才有可能理解MDX。因此，我们想到只在简单的表，列和关系的基础上设计一个语言，相比MDX，这更容易被业务用户自然地理解。随后，我们将度量值作为非常重要的概念加入DAX并用和Excel公式一样的形式表现出来，因为业务用户一般已经很习惯于使用Excel函数的感觉了。

Lars: If you had to describe DAX to someone who’d never heard of it: How would that look?

Lars：如果你向一个从来没有接触过DAX的人来介绍什么是DAX，那你会怎么说呢？

Jeffrey: To business users, I’ll tell them that DAX is a programming language that has Excel formula-like syntax and empowers you to define business metrics, also known as measures in BI jargon, which are reusable across many business reporting and analytics. To BI professionals, I’ll tell them that DAX is a programming and query language that combines core features of SQL and MDX in an Excel formula-like syntax to enable BI developers to define a feature rich BI semantic layer and to unleash the full power of the Vertipaq Engine, the in-memory columnar database.

Jeffrey：看情况。如果是业务用户，我会告诉他们：DAX是一种编程语言，写法上有点像Excel公式，但这种公式允许用户定义业务逻辑，例如：度量值，这样可以在很多业务报告及分析中复用。而如果是BI专家的话，我会告诉他们DAX是一种编程语言，同时也是一种查询语言，它整合了SQL和MDX的核心特性并以类似Excel公式的形式提供出来，让BI开发人员可以充分利用名叫Vertipaq的内存列式数据库构建丰富的BI语义层。

Lars: When you designed DAX back then, which potential users were you looking for at that time? Has this picture changed in the meantime?

Lars：在一开始设计DAX的时候，那时定位的使用者是哪类人群，现在是否有什么变化？

Jeffrey: The goal back then was to build a set of core technologies that can serve the diversified needs of users across the spectrum of self-service BI, team BI, and corporate BI, therefore we needed a programming language that’s simple enough for business analysts to use in their daily work yet powerful enough to express complex modeling concepts as required by large enterprise applications. It was an ambitious goal that is still relevant today and we have seen a steadily increasing number of business users as well as BI specialists adopt the technology as we had hoped.

Jeffrey：当时是希望可以构建一种核心技术，可以满足包括self-service BI，团队BI以及企业BI的各种需求，因此定位于打造一种足够简单的编程语言，使业务分析人员也可以直接用于他们的日常工作，而且这种语言不能因为简单而丧失可以构建复杂模型的强大性，因为它同时要能构建大型企业级的应用。即使是从今天来看，当时的定位也是有点大的，但从使用这项技术的不断增长的业务用户以及BI专家来看，它基本满足了我们当时的预期。

Lars: Recently, the Excel team announced that Power Pivot will soon be available in all Excel versions for Windows. In the foreseeable future all Excel users will have access to the DAX Engine! Do you hope that this will make DAX even more popular?

Lars：最近，Excel团队已经宣布未来Power Pivot会整合进入Windows中Excel的所有版本。如果是这样的话，所有的Excel用户都可以使用DAX了，这将使DAX更加流行吗？

Jeffrey: I certainly hope so. I even hope one day Excel team will add DAX queries to pivot tables and pivot charts to unleash the full power of the Power Pivot engine.

Jeffrey：我当然希望如此。我甚至希望有一天Excel团队可以把DAX查询功能加入到透视表和透视图的原生功能以充分释放Power Pivot引擎的能力。

Lars: DAX has many „hidden“ characteristics and concepts. Starting with filter and row contexts, via internal conversions (implicit CALCULATE statements, etc.), context transitions, etc. The language appears very simple at the beginning, but then becomes relatively fast increasingly complex. I know that many DAX users are unsettled by these „hidden“ concepts. An example: You already described on your old blog in early 2011 that there are three different forms in DAX how the date column can be referenced within a Time Intelligence function. Internally, however, these three forms are interpreted differently by DAX, which can have certain side effects. I am sure that in the development of DAX it was necessary to build the language exactly as you did. Nevertheless, could you please give the interested reader an insight into why there are so many hidden characteristics and special cases?

Lars：DAX有很多“隐藏”特性和概念。从筛选上下文以及行上下文开始，到隐式CALCULATE在内，上下文转换等。在一开始学习DAX时感觉是比较简单的，但是很快就会发现DAX并不简单。例如，你在你之前的博客（2011年）描述过DAX内部在处理时间智能函数实际是三种不同的形式。我知道写DAX时应该准确知道自己到底在做什么，就要理解每个细节。那你可以给爱好者们一些建议或启发，为什么DAX有这么多隐藏得很深的特性吗？

Jeffrey: A simple DAX expression often belies complex calculations under the hood. The appearance of simplicity of some common DAX expressions is a double-edged sword. On the one hand, syntactical simplicity and flexibility enable beginners to write powerful calculations relatively easily and quickly. On the other hand, the behind-the-scene complex calculations often have side-effects when users move beyond basic scenarios without a deeper understanding of a breakdown of steps underlying a calculated result. The original design goal was to make the syntax as simple as possible in common usage patterns while conforming to a coherent and semantically sound language model. For example,

Jeffrey：任何一个表面简单的DAX表达式实际在内部都可能有着复杂的计算逻辑。常见的DAX表达式看上去简洁确实是双刃剑。一方面，在一定程度上，简洁和灵活可以让初学者很快就学会写出强大的计算；而另一方面，一旦用户度过了基础概念区，如果不真正理解DAX就会发现很多计算结果并不是表面那么简单，例如：

= CALCULATE ( [Total Sales], Product[Color] = „Red“ )

is syntax sugar for 其实就是一个语法糖（编译器将上述内容直接翻译成下面内容）：

= CALCULATE ( [Total Sales], FILTER ( ALL ( Product[Color] ), Product[Color] = „Red“ ) )

The simpler form is more intuitive for beginners who often think of filters as Boolean expressions which return true or false for a given row. In reality, all DAX filters in filter context are table expressions on one side of a semi-join operator in relational algebra with the other side open-ended. I don’t think it’s wise to introduce the concept of semi-join to business users on their first day of DAX adventure. On the other hand, the example illustrates that DAX stands on a solid theoretical foundation so advanced users can freely combine any number of interesting features together to formulate arbitrarily complex business logic. On a related note, DAX beginners ran into their first hurdle once they started writing non-trivial DAX because measure, which is a reusable, dynamic expression, is a new concept to both Excel users and SQL developers.

简洁的形式对于初学者更加直观，所以很多人都会认为过滤器就是对于某行返回true或false的布尔表达式。而实际上，所有在筛选上下文中的DAX筛选器都是返回表的表达式，并且满足左外连接的关系代数逻辑（BI佐罗注：扩展表原理）。我认为刚刚接触DAX业务用户去了解这些概念并不合适，另一方面，这个案例也说明了DAX其实是建立在坚实的理论基础之上的，所以高级用户可以用DAX来实现非常复杂的业务逻辑。值得一提的是，DAX初学者只要开始接触度量值（可重用的动态表达式），就很可能会遇到这方面的问题。不论对Excel用户还是SQL开发者，度量值都是一种新概念。

Lars: Is there anything else you want to/ can tell us about the future development of the DAX? Lars：对于DAX未来的开发，有什么可以透露的吗？

Jeffrey: DAX, coupled with rich modeling capabilities, gives Microsoft BI an edge over many competitors’ solutions. We plan to introduce even more flexibilities in DAX and modeling so that BI users of all levels can build analytical and reporting applications they can’t imagine today. Jeffrey：DAX 的丰富建模能力，让微软的在BI领域超越了很多竞争对手。我们计划未来在DAX中引入更加灵活的特性让各种层次的BI用户都可以来做建模，并远远超过现在可想到的能力。 Lars: Jeffrey, thank you very much for answering my questions and I wish you and your team the continued creative power that already made our working lives so much easier. ? Lars：Jeffrey，非常感谢你能回答我的问题，也一如既往地期待你和你的团队能像现在一样给我们带来源源不断的生产力，让我们的工作更加简单。

关于Power BI DAX引擎之父

文中提到的DAX之父 Jeffrey Wang，江湖人称DAX之父，但其却是非常低调的。很少能在PowerBI社区或其他社交网络看到他，当然，专注于技术研发怎么可能有那么多其他时间。这里简单介绍一下 Jeffrey 先生：Principal Engineering Manager，Microsoft Power BI。Jeffrey从2004年加入微软分析服务（Analysis Service）团队，作为软件工程师。后开始参与存储引擎的性能优化工作。后来从事对MDX引擎的改进工作。他是DAX的发明者之一，DAX于2009年正式发布。他目前领导Power BI产品组中DAX引擎以及Direct Query引擎的开发工作。

这还是在去年大神给领域伙伴分享时的照片，如下：

可以看出大神的感觉还是很平易近人的。最为振奋的是：Jeffrey是位华人。这应该是最让人振奋的了。想象一下，我们用的Power BI的引擎的打造者原来和我们这么近。

另外，如果您是DAX的深度用户，你一定看过《The Definitive Guide to DAX:》，Jeffrey为该书做了前言。另外，在2018年最新上市的《Beginning DAX with Power BI》一书中，Jeffrey 是该书的技术评审。

总结

我们从了 DAX 之父的寄语中仿佛看到 Power BI 以及 DAX 在未来巨大的生命力。如果Power BI已经改变我们的工作和生活，并且您和我一样期待 DAX 的发展，让我们也一起期待吧。最后，如果可以对大神说点什么，欢迎您在本文下方留言。