introduction to dax - part 1
Post on 12-May-2015
2.532 Views
Preview:
DESCRIPTION
TRANSCRIPT
Introduction to Data Analysis Expressions (DAX)
Alan KooPresident & Founder | Puerto Rico PASS
Senior Consultant | Nagnoi, Inc.
Analysis Services 2012 Flavors
• Personal BI: PowerPivot for Excel 2010• Team BI: PowerPivot for Sharepoint• Corporate BI (SSDT):–Analysis Services 2012 Tabular–Analysis Services 2012 Multidimensional
Tasting the Flavors of SSAS 2012 | SQL Saturday Puerto Rico | Alan Koo | www.alankoo.com
What is DAX
• Programming language of SSAS 2012 Tabular and PowerPivot
• Simple, clean, powerful, fast, not easy• Tabular requires simpler data modeling for good
result• Thinking in DAX is a new attitude
BI Semantic Model: The Analysis Services 2012 Foundation
From Personal to Corporate BI
Tasting the Flavors of SSAS 2012 | SQL Saturday Puerto Rico | Alan Koo | www.alankoo.com
DAX MDX
Calculated Columns and Measures
• Calculated Columns– Columns in the database, calculated with DAX– Stored in the database
• Measures– DAX formulas computed on aggregates of values– Computed at query time
• Different usages for different modeling needs
Calculated Columns
• Formula is evaluated for each row• Results are stored back into PowerPivot as any
other column• Columns can be used for both slicing data and
summarizing values• Part of the model, available for use in any
PivotTable • Usable by other AS clients in PowerPivot for
SharePoint
DAX includes 80 Functions from Excel
12
Date and Time Information Math and Trig Statistical TextDATE ISBLANK ABS AVERAGE CONCATENATEDATEVALUE ISERROR CEILING, ISO.CEILING AVERAGEA EXACTDAY ISLOGICAL EXP COUNT FINDEDATE ISNONTEXT FACT COUNTA FIXEDEOMONTH ISNUMBER FLOOR COUNTBLANK LEFTHOUR ISTEXT INT MAX LENMINUTE LN MAXA LOWERMONTH Logical LOG MIN MIDNOW AND LOG10 MINA REPLACESECOND IF MOD REPTTIME IFERROR MROUND RIGHTTIMEVALUE NOT PI SEARCHTODAY OR POWER SUBSTITUTEWEEKDAY FALSE QUOTIENT TRIMWEEKNUM TRUE RAND UPPERYEAR RANDBETWEEN VALUEYEARFRAC ROUND
ROUNDDOWNROUNDUPSIGNSQRTSUMSUMSQTRUNC
Calculated Measures
• Evaluated dynamically per value in PivotTable
• Part of the model, available to other PivotTables
• Usable by any AS client through PowerPivot for SharePoint
13
DAX Aggregation Functions
• SUM, AVERAGE, MIN, MAX, COUNT take column references
• Extensions aggregate an expression over the rows of a table
• SUMX (Table, Expression)
• AVERAGEX (Table, Expression)
• COUNTAX (Table, Expression)
• MINX (Table, Expression)
• MAXX (Table, Expression)
• COUNTROWS (Table)14
Useful Resources
• Data Analysis Expressions (DAX) Overview – MSDN http://msdn.microsoft.com/en-us/library/gg399181.aspx
• DAX in Action! – Alberto Ferrari in Project BotticelliProjectBotticelli.com
• SQLBI.com Articleshttp://www.sqlbi.com/articles/all/
• Microsoft Business Intelligence y más… - Alan Kooalankoo.com
Calculated Columns
• Calculated columns– Stored in the database, computed at process time– On big fact tables can consume a lot of RAM– Required to slice data
• Measures– Computed at query time, do not use RAM
• Prefer measures, whenever possible
Summary
• DAX is fast: its speed of DAX makes simpler modeling techniques shine
• Tabular requires thinking in DAX, less modeling, more complex expressions
• PowerPivot is a great prototyping tool, models created in Excel can be deployed on SSAS Tabular
Credits for this Session
• Ashvini Sharma – Microsoft Corporation• Alberto Ferrari and Marco Russo - SQLBI
Nestor Figueroa | 787.671.8244powerpivotworkshop@nagnoi.com
Authorized Training Center
Designed for professionals who want to use PowerPivot’s full capabilities, the PowerPivot Workshop is a two/three-day course that covers PowerPivot topics,
starting from basic concepts and moving on up to complex data models and DAX formulas with the goal
to facilitate the use of PowerPivot in the real world.
top related