The Definitive Guide to DAX

Business Intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI
1. Auflage 2015
ISBN: 978-0-7356-9835-2
Verlag: Microsoft Press

Buch, Englisch, 557 Seiten, Format (B × H): 189 mm x 229 mm, Gewicht: 969 g

This comprehensive and authoritative guide will teach you the DAX language for business intelligence, data modeling, and analytics. Leading Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimization. You’ll learn exactly what happens under the hood when you run a DAX expression, how DAX behaves differently from other languages, and how to use this knowledge to write fast, robust code. If you want to leverage all of DAX’s remarkable power and flexibility, this no-compromise “deep dive” is exactly what you need. Perform powerful data analysis with DAX for Microsoft SQL Server Analysis Services, Excel, and Power BIMaster core DAX concepts, including calculated columns, measures, and error handlingUnderstand evaluation contexts and the CALCULATE and CALCULATETABLE functionsPerform time-based calculations: YTD, MTD, previous year, working days, and moreWork with expanded tables, complex functions, and elaborate DAX expressionsPerform calculations over hierarchies, including parent/child hierarchiesUse DAX to express diverse and unusual relationshipsMeasure DAX query performance with SQL Server Profiler and DAX Studio

Autoren/Hrsg.

Ferrari, Alberto

Russo, Marco

Inhaltsverzeichnis

Foreword    xviiIntroduction    xixChapter 1: What is DAX?    1Understanding the data model    1Understanding the direction of a relationship    3DAX for Excel users    5Cells versus tables    5Excel and DAX: Two functional languages    8Using iterators    8DAX requires some theory    8DAX for SQL developers    9Understanding relationship handling    9DAX is a functional language    10DAX as a programming and querying language    11Subqueries and conditions in DAX and SQL    12DAX for MDX developers    13Multidimensional vs. Tabular    13DAX as a programming and querying language    13Hierarchies    14Leaf-level calculations    15Chapter 2: Introducing DAX    17Understanding DAX calculations    17DAX data types    18DAX operators    21Understanding calculated columns and measures    22Calculated columns    22Measures    23Variables    26Handling errors in DAX expressions    26Conversion errors    26Arithmetical operations errors    27Intercepting errors    30Formatting DAX code    32Common DAX functions    35Aggregate functions    35Logical functions    37Information functions    39Mathematical functions    39Trigonometric functions    40Text functions    40Conversion functions    41Date and time functions    42Relational functions    42Chapter 3: Using basic table functions    45Introducing table functions    45EVALUATE syntax    47Using table expressions    50Understanding FILTER    51Understanding ALL, ALLEXCEPT, and ALLNOBLANKROW    54Understanding VALUES and DISTINCT    58Using VALUES as a scalar value    59Chapter 4: Understanding evaluation contexts    61Introduction to evaluation contexts    62Understanding the row context    66Testing your evaluation context understanding    67Using SUM in a calculated column    67Using columns in a measure    68Creating a row context with iterators    69Using the EARLIER function    70Understanding FILTER, ALL, and context interactions    74Working with many tables    77Row contexts and relationships    78Filter context and relationships    80Introducing VALUES    84Introducing ISFILTERED, ISCROSSFILTERED    85Evaluation contexts recap    88Creating a parameter table    89Chapter 5: Understanding CALCULATE and CALCULATETABLE    93Understanding CALCULATE    93Understanding the filter context    95Introducing CALCULATE    98CALCULATE examples    101Filtering a single column    101Filtering with complex conditions    106Using CALCULATETABLE    109Understanding context transition    111Understanding context transition with measures    114How many rows are visible after context transition?    116Understanding evaluation order of context transition    117Variables and evaluation contexts    118Understanding circular dependencies    119CALCULATE rules    122Introducing ALLSELECTED    123Understanding USERELATIONSHIP    125Chapter 6: DAX examples    129Computing ratios and percentages    129Computing cumulative totals    132Using ABC (Pareto) classification    136Computing sales per day and working day    143Computing differences in working days    150Computing static moving averages    151Chapter 7: Time intelligence calculations    155Introduction to time intelligence    155Building a Date table    156Using CALENDAR and CALENDARAUTO    157Working with multiple dates    160Handling multiple relationships to the Date table    161Handling multiple Date tables    162Introduction to time intelligence    164Using Mark as Date Table    166Aggregating and comparing over time    168Year-to-date, quarter-to-date, month-to-date    168Computing periods from prior periods    171Computing difference over previous periods    174Computing the moving annual total    175Closing balance over time    178Semi-additive measures    178OPENINGBALANCE and CLOSINGBALANCE functions    184Advanced time intelligence    188Understanding periods to date    189Understanding DATEADD    191Understanding FIRSTDATE and LASTDATE    196Understanding FIRSTNONBLANK and LASTNONBLANK    199Using drillthrough with time intelligence    200Custom calendars    200Working with weeks    201Custom year-to-date, quarter-to-date, month-to-date    204Computing over noncontiguous periods    206Custom comparison between periods    210Chapter 8: Statistical functions    213Using RANKX    213Common pitfalls using RANKX    216Using RANK.EQ    219Computing average and moving average    220Computing variance and standard deviation    222Computing median and percentiles    223Computing interests    225Alternative implementation of PRODUCT and GEOMEAN    226Using internal rate of return (XIRR)    227Using net present value (XNPV)    228Using Excel statistical functions    229Sampling by using the SAMPLE function    230Chapter 9: Advanced table functions    233Understanding EVALUATE    233Using VAR in EVALUATE    235Understanding filter functions    236Using CALCULATETABLE    236Using TOPN    239Understanding projection functions    241Using ADDCOLUMNS    241Using SELECTCOLUMNS    244Using ROW    247Understanding lineage and relationships    248Understanding grouping/joining functions    250Using SUMMARIZE    250Using SUMMARIZECOLUMNS    255Using GROUPBY    261Using ADDMISSINGITEMS    262Using NATURALINNERJOIN    265Using NATURALLEFTOUTERJOIN    266Understanding set functions    267Using CROSSJOIN    267Using UNION    269Using INTERSECT    272Using EXCEPT    274Using GENERATE, GENERATEALL    275Understanding utility functions    278Using CONTAINS    278Using LOOKUPVALUE    280Using SUBSTITUTEWITHINDEX    283Using ISONORAFTER    284Chapter 10: Advanced evaluation context    285Understanding ALLSELECTED    285Understanding KEEPFILTERS    294Understanding AutoExists    304Understanding expanded tables    307Difference between table expansion and filtering    315Redefining the filter context    316Understanding filter context intersection    318Understanding filter context overwrite    320Understanding arbitrarily shaped filters    321Understanding the ALL function    326Understanding lineage    329Using advanced SetFilter    331Learning and mastering evaluation contexts    338Chapter 11: Handling hierarchies    339Computing percentages over hierarchies     339Handling parent-child hierarchies    346Handling unary operators    358Implementing unary operators by using DAX    359Chapter 12: Advanced relationships    367Using calculated physical relationships    367Computing multiple-column relationships    367Computing static segmentation    369Using virtual relationships    371Using dynamic segmentation    371Many-to-many relationships    373Using relationships with different granularities    378Differences between physical and virtual relationships    381Finding missing relationships    382Computing number of products not sold    383Computing new and returning customers    384Examples of complex relationships    386Performing currency conversion    386Frequent itemset search    392Chapter 13: The VertiPaq engine    399Understanding database processing    400Introduction to columnar databases    400Understanding VertiPaq compression    403Understanding value encoding    404Understanding dictionary encoding    405Understanding Run Length Encoding (RLE)    406Understanding re-encoding    409Finding the best sort order    409Understanding hierarchies and relationships    410Understanding segmentation and partitioning    412Using Dynamic Management Views    413Using DISCOVER_OBJECT_MEMORY_USAGE    414Using DISCOVER_STORAGE_TABLES    414Using DISCOVER_STORAGE_TABLE_COLUMNS    415Using DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS    416Understanding materialization    417Choosing hardware for VertiPaq    421Can you choose hardware?    421Set hardware priorities    421CPU model    422Memory speed    423Number of cores    423Memory size    424Disk I/O and paging    424Conclusions    424Chapter 14: Optimizing data models    425Gathering information about the data model    425Denormalization    434Columns cardinality    442Handling date and time    443Calculated columns    447Optimizing complex filters with Boolean calculated columns    450Choosing the right columns to store    451Optimizing column storage    453Column split optimization    453Optimizing high cardinality columns    454Optimizing drill-through attributes    455Chapter 15: Analyzing DAX query plans    457Introducing the DAX query engine    457Understanding the formula engine    458Understanding the storage engine (VertiPaq)    459Introducing DAX query plans    459Logical query plan    460Physical query plan    461Storage engine query    462Capturing profiling information    463Using the SQL Server Profiler    463Using DAX Studio    467Reading storage engine queries    470Introducing xmSQL syntax    470Understanding scan time    477Understanding DISTINCTCOUNT internals    479Understanding parallelism and datacache    480Understanding the VertiPaq cache    481Understanding CallbackDataID    483Reading query plans    488Chapter 16: Optimizing DAX    495Defining optimization strategy    496Identifying a single DAX expression to optimize    496Creating a reproduction query    499Analyzing server timings and query plan information    500Identifying bottlenecks in the storage engine or formula engine    503Optimizing bottlenecks in the storage engine    504Choosing ADDCOLUMNS vs. SUMMARIZE    505Reducing CallbackDataID impact    509Optimizing filter conditions    512Optimizing IF conditions    513Optimizing cardinality    515Optimizing nested iterators    517Optimizing bottlenecks in the formula engine    522Creating repro in MDX    527Reducing materialization    528Optimizing complex bottlenecks    532Index    537

Über Autor(innen)

Marco Russo and Alberto Ferrari cofounded sqlbi.com, where they regularly publish articles about Microsoft PowerPivot, DAX, Power BI, and SQL Server Analysis Services. They also speak regularly at major international conferences, such as TechEd, Ignite, PASS Summit, and SQLBits. Both consult and mentor on business intelligence (BI) and Microsoft BI technologies, having worked with them since 1999. Their books include Microsoft Excel 2013 Building Data Models with PowerPivot, Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model, and Microsoft PowerPivot for Excel 2010: Give Your Data Meaning.