DAX ( Data Analysis eXpressions - expressions for data analysis ) is a functional functional query language , developed and supported by Microsoft , for constructing expressions and extracting data that are located in the tabular model - a data representation model ideologically similar to the multidimensional OLAP model. A query or an expression on DAX resembles the syntax of Excel formulas, in which certain functions of a language are called, but, unlike Excel, it operates either directly with entire columns of a data table, or with part of these columns. Unlike Excel, addressing data in a cell of a data table using DAX is impossible, which brings the language closer to SQL or MDX .
DAX | |
---|---|
Semantics | formulary, functional , subject-oriented |
Language class | |
Appeared in | 2010 |
Author | and development team |
Developer | Microsoft |
Release | September 2018 (monthly releases) (2018) |
Type system | Dynamic |
Basic implementations: | Microsoft Power BI Desktop , Microsoft Analysis Services , Microsoft Excel |
Experienced influence | Microsoft Excel |
Influenced by | Microsoft Power BI , Microsoft Analysis Services , Microsoft Excel |
License | n / a |
Platform | x86-64, x86-32 |
OS | Microsoft Windows |
It is one of the three key elements of the concept of building BI- systems according to Microsoft, along with ETL- means and the visualization subsystem . Use of DAX is free of royalties.
Content
Syntax
Since the creators of DAX were inspired by the syntax of Excel formulas [1] , then any request for DAX can be represented as a string. Carriage returns, spaces, tabs between query elements are ignored, and multiline queries are used only to simplify the reading of program code.
An example of creating a new measure (analogous to a new function in terms of programming languages), which returns yesterday's date:
Вчерашняя дата = TODAY () - 1
In the DAX implementation in PowerPivot for Excel, as an infix for an assignment, instead of the “=” sign in the definitions of measures, “: =” is used; The rest of the syntax remains unchanged. The DAX request for SSAS is preceded by the EVALUATE
service word. In other implementations (for example, PowerPivot for Excel, Power BI), the service word EVALUATE
, as well as a number of other service words ( DEFINE
, MEASURE
, GROUP BY
, ORDER
etc.) are not used in the user interface and are applied automatically.
Functional composition
The result of the query or expression can be a table or a single, scalar value. More than two hundred built-in functions of the language are divided into the following groups:
- functions with a table value
- filter functions
- aggregate functions
- time logic functions
- scalar functions
- date and time functions
- statistical functions
- logical functions
- filter functions
- mathematical and trigonometric functions
- text functions
- hierarchy functions
- time logic functions [2] .
Localization
The language syntax has partial localization: language function names are always written in English ( ABS, AVERAGE, BLANK, NOW, TODAY, SUM
, etc.), and the decimal separator and function separator depend on the operating system locale , for example, in the English version the dot is used in as a decimal separator, and a comma as a separator of functions (if there are more than one function in a request), and in Russian localization a decimal separator is used by default, and to separate functions within a single query or function parameters and a semicolon ( ';').
Productivity
The peculiarity of the language is to ensure high performance of queries to data and focus on resident computing ( English in-memory computing ), due to which acceleration of query execution is achieved and it becomes possible to interact with the elements of BI reports based on this data, to build with functions of interactive loading of details ( drill-down ), question-answer interfaces and some other similar means.
History
When implementing the technology of column data storage in memory, which is used in the xVelocity “engines” (vertiPaq), a query language was required to provide manipulation with the data stored in the columns of tabular data (as in the MDX dimensions). On the other hand, the language would have to be syntactically similar to the Excel formula language, which by 2008–2009 was familiar to the overwhelming majority of business users involved in data analysis. However, the similarity of DAX to the non-localized, English-based Excel formula language is more of a marketing ploy than a real need for single-line queries.
Since queries were made to in-memory data and I / O operations were not required, the new language could ignore a number of approaches to optimize queries used in MDX / SQL [3] , thereby achieving query compactness, faster development speed and, potentially, higher performance than MDX / SQL.
In 2010, DAX along with the tabular data model was integrated into SSAS version 2012 [4] and a supplement for Microsoft Excel 2010 Professional was released, the version of which became known as Professional Plus.
The experiment was recognized as a success because Microsoft later incorporated DAX support into all of its Microsoft Analysis Services Enterprise and Business Intelligence product line (SQL Server 2014, SQL Server 2016, SQL Server 2017) [5] and advanced versions of Microsoft Excel for the Windows platform (Excel 2013, Excel 2016), as well as cloud implementations on the Azure platform [6] [7] .
At the beginning of 2015, the first product of the Power BI line, Power BI Designer, was released in which all Power technologies were integrated as additions or otherwise integrated with Excel, PowerPivot, Power Query, Power View and Power Maps. At the end of 2015, this integrated product changed its name to Power BI Desktop, and since that moment it is free from royalties for personal use.
Notes
- ↑ Interview with DAX developer (Jeffrey Wang) . (German / English)
- ↑ Types of DAX functions . The appeal date is October 13, 2013.
- ↑ SQL Explore. Extreme Analysis Service .
- ↑ SQL Server 2012 Online . Microsoft .
- ↑ Features supported by various editions of SQL Server 2014 .
- ↑ Creating a model on the Azure portal . Microsoft . The appeal date is June 3, 2018.
- ↑ How to setup a tabular data model in SSAS Azure . The appeal date is May 1, 2017.
Literature
- Practical PowerPivot & DAX Formulas for Excel 2010. - , 2012. - ISBN 9780071746854 .
- Alberto Ferrari, Marco Russo. DAX Patterns 2015 . - , 2014. - ISBN 978-1-5056-2363-5 .
- Matt Allington. Learn to Write DAX . - , 2015. - ISBN 978-1-61547-041-9 .
- Daniil Maslyuk. Exam Ref 70-778 Analyzing Data by Using Microsoft Power BI . - , 2018. - ISBN 978-1-5093-0702-9 .
Links
- General Information on Data Analysis Expressions (DAX)
- Data Analysis Expression Reference (DAX)
- Data Analysis Expressions (DAX) Reference
- Introduction to DAX
- An Introduction to DAX as a Query Language
- The official community of Microsoft (English-speaking) . DAX questions are most often discussed in the Desktop and Developer sections.
- powerBiForever Russian-speaking and one of the largest professional communities in Excel and Power BI. Unrelated to Microsoft