1 / 46

Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX (Data Analysis Expressions)

Required Slide. SESSION CODE: BIU302. Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX (Data Analysis Expressions). Ashvini Sharma Lead Program Manager SQL Server Analysis Service. Agenda. PowerPivot overview DAX Introduction and concepts. PowerPivot.

tarika
Télécharger la présentation

Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX (Data Analysis Expressions)

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Required Slide SESSION CODE: BIU302 Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX (Data Analysis Expressions) Ashvini Sharma Lead Program Manager SQL Server Analysis Service

  2. Agenda • PowerPivot overview • DAX Introduction and concepts

  3. PowerPivot PowerPivot For Excel 2010 PowerPivot For SharePoint 2010 Sharing PowerPivot content in SharePoint Usage monitoring by IT SQL Server 2008R2 for SharePoint 2010 Any AS client app can connect, multi-user DAX is invisible here • Authoring PowerPivot content in Excel • Free download for Excel 2010 • Desktop, single user experience • DAX formulas written here

  4. PowerPivot for Excel Quick walkthrough DEMO

  5. PowerPivot Add-In for Excel • Self-Service BI • PivotTable(s) based on multiple tables of data • Ease-of-use: No need to learn Multi-Dimensional anything • Data Analysis Expressions (DAX) • Simple Excel style formulas • Enable Excel users to do powerful data analysis using their existing skills

  6. Sample Data - Contoso

  7. Calculated Columns DEMO

  8. Demo 1 - PowerPivot & Calculated Columns • Launch workbook – see PowerPivot Ribbon • Navigate between Excel & PowerPivot windows • Ten tables from Contoso • Two calculated columns • Margin = [SalesAmount]–[TotalCost] • MarginPct = ([SalesAmount]-[TotalCost])/[SalesAmount] • Show PivotTable field list with multiple tables

  9. 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

  10. Data Analysis Expressions (DAX) • Uses Excel syntax and has many of the Excel functions • Functions refer to columns, not individual cells or ranges [Cost] or Product[Cost] or ‘Product Category’[Category Name]

  11. DAX includes 80 Functions from Excel

  12. Beyond Excel You Are Here

  13. Tables and Relationships DEMO

  14. Demo 2 - RELATED and RELATEDTABLE • RELATED and RELATEDTABLE in calculated columns • RELATED ProductColor = RELATED (DimProduct[ColorName]) StoreCity = RELATED (DimGeography[CityName]) • RELATEDTABLE =SUMX(RELATEDTABLE(FactSales),FactSales[SalesAmount]) Identical formula can be placed in multiple tables!

  15. Following relationships • Related (Column) • Fetches a value from a related table • Works when going from the many-side to one-side of a relationship • Blank returned back for missing values • Can make multiple hops = RELATED(DimStore[StoreName]) = RELATED(DimGeography[ContinentName])

  16. Beyond Calculated Columns Summarizing numeric and non-numeric data in PivotTables DEMO

  17. Demo 3 – Creating Measures • Row & Column Labels • Checkbox or drag/drop places values on axis • Values Area • SUM(numeric) or COUNT(non-numeric) • Two ways to author measures • Choose column from table (and choose aggregation) • Enter custom DAX expression (formula) • Each measure evaluated w/distinct context for each PivotTable cell

  18. Understanding Filter Context DEMO

  19. Demo 3 – Show Filter Context • Show filter context

  20. 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

  21. 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)

  22. Table Functions SUMX, RELATEDTABLE DEMO

  23. Demo 4 – Calculated Column with Table Function • =SUMX(RELATEDTABLE(FactSales),FactSales[SalesAmount])

  24. Some DAX Table Functions

  25. Changing Filter Context • Changing filter context is very common in BI • Sales in all channels vs. Sales in Stores only • Sales of a product vs. Sales of All products • Sales in a region vs. Sales in All Regions • Can enable measures to be resilient to multiple PivotTable layouts • CALCULATE (Expression [, SetFilter1][, SetFilter2]...) = CALCULATE([Sales], DimChannel[ChannelName]="Store") = CALCULATE([Sales], ALL(DimProduct)) = [Sales](ALL(DimProduct)) <- Shortcut for previous statement

  26. Some DAX Table Functions that change Context

  27. Changing Filter Context Using CALCULATE DEMO

  28. Demo 5 – CALCULATE and Measures [Sales] =SUM(FactSales[SalesAmount]) [StoreSales] =CALCULATE([Sales],DimChannel[ChannelName]="Store") = [Sales](DimChannel[ChannelName]="Store") [PctAllRegions] =[Sales] / CALCULATE([Sales],ALL(DimGeography)) =[Sales] / [Sales](ALL(DimGeography))

  29. You Are Here

  30. Quiz! Calculating RANK • Rank: (Number of customers that have more sales than the customer) + 1 DimCustomer[CustomerRank] =                COUNTROWS             ( FILTER ( ALL (DimCustomer [CustomerName]), [CustomerSales] > [CustomerSales] (VALUES (DimCustomer [CustomerName])) )              ) + 1 Sales for Filter Context from PivotTable Sales for current row in the table being filtered

  31. Quiz! Calculating RANK • Rank: (Number of customers that have more sales than the customer) + 1 DimCustomer[CustomerRank] =                COUNTROWS             ( FILTER ( ALL (DimCustomer [CustomerName]), [CustomerSales] > [CustomerSales] (VALUES (DimCustomer [CustomerName])) )              ) + 1

  32. Time Intelligence Functions • Built-in 35 functions that update filter context for time based analysis • Require a Date column in the data joined to a Date table • Work with intervals of days, months, quarters, and years • Functions that return a single date • Functions that return a table of dates • Functions that evaluate expressions over time period

  33. Functions that return a single date • FirstDate (Date_Column) • LastDate (Date_Column) • FirstNonBlank (Date_Column, Expression) • LastNonBlank (Date_Column, Expression) • StartofMonth (Date_Column) • StartofQuarter (Date_Column) • StartofYear (Date_Column [,YE_Date]) • EndofMonth (Date_Column) • EndofQuarter (Date_Column) • EndofYear (Date_Column [,YE_Date])

  34. Functions that return a table of dates • DateAdd (Date_Column, Number_of_Intervals, Interval) • DatesBetween (Date_Column, Start_Date, End_Date) • DatesInPeriod (Date_Column, Start_Date, Number_of_Intervals, Interval) • ParallelPeriod (Date_Column, Number_of_Intervals, Interval) • PreviousDay (Date_Column) • PreviousMonth (Date_Column) • PreviousQuarter (Date_Column) • PreviousYear (Date_Column [,YE_Date]) • NextDay (Date_Column) • NextMonth (Date_Column) • NextQuarter (Date_Column) • NextYear (Date_Column [,YE_Date]) • DatesMTD (Date_Column) • DatesQTD (Date_Column) • DatesYTD (Date_Column [,YE_Date]) • SamePeriodLastYear (Date_Column)

  35. Functions that evaluate expressions over a time period • TotalMTD (Expression, Date_Column [, SetFilter]) • TotalQTD (Expression, Date_Column [, SetFilter]) • TotalYTD (Expression, Date_Column [, SetFilter] [,YE_Date]) • OpeningBalanceMonth (Expression, Date_Column [,SetFilter]) • OpeningBalanceQuarter (Expression, Date_Column [,SetFilter]) • OpeningBalanceYear (Expression, Date_Column [,SetFilter] [,YE_Date]) • ClosingBalanceMonth (Expression, Date_Column [,SetFilter]) • ClosingBalanceQuarter (Expression, Date_Column [,SetFilter]) • ClosingBalanceYear (Expression, Date_Column [,SetFilter] [,YE_Date])

  36. Time Intelligence DEMO

  37. Demo 6 - Time Intelligence [YOY Growth] =[Sales]- [Sales](PREVIOUSYEAR(DimDate[Datekey])) [PrevYearSales] =IF( COUNTROWS(VALUES(DimDate[CalendarYear]))=1 , [Sales](PARALLELPERIOD(DimDate[Datekey],-12,MONTH)), BLANK() ) [PctGrowth] =IF( [PrevYearSales], ([Sales]-[PrevYearSales])/[PrevYearSales], BLANK() )

  38. Call To Action

  39. Summary • DAX formulas use familiar Excel syntax • Calculated Columns (in PowerPivot tables) • Measures (in PivotTable UI) • Functionality includes: • Excel functions (math, statistical, date/time, text, etc.) • Aggregation of any expression over a table • Table functions (Filter(), RelatedTable(), Distinct(), etc.) • Fetch data across relationships (one-many, many-one) • Set or modify calculation context (All, Calculate, etc.) • Time Intelligence functions

  40. 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

  41. Links • DAX Whitepaper and Samples on http://www.microsoft.com/downloads • PowerPivot team blog on http://blogs.msdn.com/powerpivot • Other good resources: • www.powerpivot.com • www.powerpivot-info.com • http://powerpivotfaq.com/ • www.powerpivotpro.com

  42. Required Slide Speakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are related to your session. Related Content • 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.

  45. Beyond Excel – Dynamic Aggregation • DAX has functions to assist with dynamic aggregation of measures in PivotTables • Discovery of Filter Context (e.g. What is current product or year?) • Modification of Filter Context (e.g. switch to all products or years) • Time Intelligence Functions (e.g. compare values to previous period)

  46. Required Slide

More Related