1 / 38

17-20 OCTOBER 2011

17-20 OCTOBER 2011. DURBAN ICC. Killer Real-World PowerPivot Examples. Kevin Coetzee Online Systems Manager Dimension Data. Agenda. PowerPivot Overview Tips and Tricks for the Real World Working with PowerPivot for Business Real World Implementations HR Analysis

cassandra-mckee
Download Presentation

17-20 OCTOBER 2011

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. 17-20 OCTOBER 2011 DURBAN ICC

  2. Killer Real-World PowerPivot Examples Kevin Coetzee Online Systems Manager Dimension Data

  3. Agenda • PowerPivot Overview • Tips and Tricks for the Real World • Working with PowerPivot for Business • Real World Implementations • HR Analysis • ATM Transactions with a little DAX • Payment Age Analysis with DAX and Related Tables • Some Tips and Tricks to take home

  4. Business IntelligenceBI - Improving Business Insight “A broad category of applications and technologies for gathering, storing, analyzing, sharing and providing access to data to help enterprise users make better business decisions.” – Gartner

  5. What is PowerPivotWith a few mouse clicks, a user can create and publish intuitive and interactive self-service analysis solutions

  6. Demo : Should I go swimming tonight?

  7. Demo : * HR Department* ATM Transactions* Aged Payment Analysis

  8. Demo : Map it

  9. Calculated Measures

  10. Tips and Tricks

  11. Some Tips and TricksTip #1 • Use && and || in DAX • Instead of nested IF statements • Easier to read IF(Product[Color]=”Blue” && Product[Weight]>6, “Heavy and Blue”, “Everything Else”) IF(Product[Color]=”Blue”,”BlueProduct[Color]=”Red” || Product[Color]=”Yellow”, “Primary Color”, “Blend”)

  12. Some Tips and TricksTip #2 : Less Columns / More Rows = Speed

  13. Create a Date Table • STARTOFMONTH (Date_Column) • STARTOFQUARTER (Date_Column) • STARTOFYEAR (Date_Column [,YE_Date]) • ENDOFMONTH (Date_Column) • ENDOFQUARTER (Date_Column) • ENDOFYEAR (Date_Column [,YE_Date]) • 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])

  14. What’s new in PowerPivot in Denali?

  15. Rich Modeling Capabilities • Multiple relationships, hierarchies • Parent-child relationships • Key performance indicators, drillthrough, perspectives • Rich data types, BLOBs, images

  16. Sophisticated Business Logic • Data Analysis Expressions (DAX), Excel formulas, MDX • Relational operators (Filter, Aggregate, GroupBy, Lookup) • Statistical, time intelligence (YTD, QTD) functions • Rank, TopN, VisualTotals, DistinctCount

More Related