slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Required Slide PowerPoint Presentation
Download Presentation
Required Slide

Loading in 2 Seconds...

play fullscreen
1 / 44

Required Slide - PowerPoint PPT Presentation


  • 272 Views
  • Updated on

Required Slide. Required Slide. SESSION CODE: BIU05-INT. DAX Patterns in PowerPivot. Marco Russo BI Architect & Consultant SQLBI. Who is the speaker. marco.russo@sqlbi.com Independent consultant 15+ years on SQL Server 10+ years on BI & OLAP Book author

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

Required Slide


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
    Presentation Transcript
    1. Required Slide

    2. Required Slide SESSION CODE: BIU05-INT DAX Patterns in PowerPivot Marco Russo BI Architect & Consultant SQLBI

    3. Who is the speaker • marco.russo@sqlbi.com • Independent consultant • 15+ years on SQL Server • 10+ years on BI & OLAP • Book author • Expert Cube Development with Microsoft SQL Server Analysis Services • PowerPivot for Excel 2010:Give your data a meaning • Founder of SQLBI.COM

    4. Agenda • DAX Foundations • DAX Syntax • Calculated Columns, Measures, Evaluation Context • DAX at work • Weight Percent, ABC / Pareto, Time Period Comparisons • Customers / Products (distinct count)

    5. Data Analysis Expressions (DAX) • DAX uses a syntax similar to Excel • TableX [ColY] or [ColY] (fully qualified vs. not qualified) • Cells cannot be referenced (like B23, B12:C15, …) • DAX functions operate with table columns

    6. DAX is More Powerful than Excel Formulas • DAX integrates concepts from relational databases • Multiple tables • Filters, iteration and aggregation of tables/columns • Relationships (references to data in related table) • Dynamic aggregation of measures in pivot tables • Analyze and change Filter Context • “Time Intelligence” functions • DAX is not a replacement for MDX (MDX is used in Analysis Services) • DAX defines calculations in PowerPivot – for End Users • MDX defines multidimensional queries – for IT Pros

    7. Using DAX Calculated Columns • Regular columns populated with data read by external sources • Data is copied into PowerPivot • Expressions in calculated columns evaluated for each row • Result stored during data acquisition • And when calculation is defined first time • Each evaluation uses the “Row Context”

    8. Using DAX Measures (or Calculated Field) • Named expressions for pivot tables • Belong to the PowerPivot model, not to the pivot table • Each cell evaluates formula in a specific “Filter Context” • Some calculations requires measures (i.e. % of total)

    9. Sample PowerPivot Model DEMO

    10. Calculated Columns

    11. Calculated Columns DEMO

    12. Measures - Implicit vs. Explicit

    13. Measures DEMO

    14. EvaluationContext

    15. Functions that Follow Relationships

    16. DAX Aggregation Functions • Aggregation functions require a column reference • Implicit context in DAX / PowerPivot • In Excel there is always an explicit context using cell ranges • Aggregation of an expression evaluated for each row of a table • SUMX (Table, Expression) • AVERAGEX (Table, Expression) • COUNTAX (Table, Expression) • MINX (Table, Expression) • MAXX (Table, Expression)

    17. More than 80 Excel functions in DAX

    18. Table Functions in DAX Differences from Excel

    19. Table Functions in DAX Functions List

    20. Making Calculation by Modifying Filter Context

    21. Measures Examples

    22. Measures with CALCULATE Calculate margin % on Sales Table DEMO

    23. DAX at work Weight Percent ABC / Pareto Time Period Comparison Customers / Products (distinct count) Many-to-many relationships

    24. Weight Percent • Weight Percent calculation requires a DAX explicit measurei = value / rif • DAX formula contains an explicit calculation of the denominator

    25. Weight Percent DEMO

    26. ABC / Pareto

    27. ABC / Pareto SalesAmountProduct • Calculate total sales amount for each product • Version on normalized table • Some changes required for denormalized tables • Better performance with normalized table SalesAmountProduct = SUMX( RELATEDTABLE( Orders ), Orders[Amount] )

    28. ABC / Pareto CumulatedProduct • Calculate the cumulated sales amount for each product • Sort products for sales amount (descendent order) • Sum up sales amount for all products having sales amount >= of «current» product (row context)CumulatedProduct=SUMX(FILTER( Products, Products[SalesAmountProduct] >= EARLIER(Products[SalesAmountProduct] )), Products[SalesAmountProduct] )

    29. ABC / Pareto SortedWeightProduct • Ratio between CumulatedProduct and total sales for all products • Needed to evaluate threshold of ABC classes (70%, 20%, 10%)SortedWeightProduct =Products[CumulatedProduct] / SUM( Products[SalesAmountProduct] )

    30. ABC / Pareto ABC Product • Defines an A/B/C attribute based on SortedWeightProductABC Product =IF( Products[SortedWeightProduct] < 0.7, "A", IF( Products[SortedWeightProduct] < 0.9,"B", "C" ))

    31. ABC / Pareto DEMO

    32. Time Period Comparison Year-To-Date

    33. Time Period Comparison Previous Year

    34. Time Period Comparison Last 12 months

    35. Time Period Comparison DEMO

    36. Number of Unique Customers / Products Distinct Count Measure • There is no DISTINCT COUNT aggregation • It can be built using COUNTROWS and DISTINCTCustomers = COUNTROWS( DISTINCT( Orders[CustomerID] )) • More complex formula required whenever a related table is involvedCities = CALCULATE( COUNTROWS( FILTER( DISTINCT(Customers[City] ), COUNTROWS( RELATEDTABLE(Orders)) > 0 )))

    37. Unique Customers / Products How to implement a Distinct Count calculation DEMO

    38. Many-to-many relationship • Formed by two one-to-many relationships • Natively not supported by PowerPivot • Can be calculated by using DAX formulas based on COUNTROWS over intermediate table (aka bridge table of factless fact table) http://sqlblog.com/blogs/marco_russo/archive/2009/12/07/many-to-many-relationships-in-powerpivot.aspx

    39. Summary

    40. Links • Bloghttp://sqlblog.com/blogs/marco_russo • ABC analysis in PowerPivothttp://sqlblog.com/blogs/marco_russo/archive/2010/01/19/abc-analysis-in-powerpivot.aspx • Time Intelligence Functions • http://blogs.msdn.com/powerpivot/archive/2010/04/12/time-intelligence-functions-in-dax.aspx • Book about PowerPivot from Microsoft Press • Expected for September 2010 - Authors: Marco Russo and Alberto Ferrari • Other good resources: • www.powerpivot.com • www.powerpivot-info.com • http://powerpivotfaq.com/ • www.powerpivotpro.com

    41. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

    42. Required Slide Speakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are related to your session. Related Content • BIU302 – Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAXThursday, June 10  |  9:45 AM - 11:00 AM  |  Rm 244 • BIU02-INT – Budgeting Solution Built with PowerPivotThursday, June 10  |  1:30 PM - 2:45 PM  |  Rm 240 • BIU02-HOL – Defining DAX Calculations with MS PowerPivot for MS Excel 2010

    43. Required Slide Complete an evaluation on CommNet and enter to win!

    44. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.