Transcript
- Slide 1
- 17-20 OCTOBER 2011 DURBAN ICC
- Slide 2
- Killer Real-World PowerPivot Examples Kevin Coetzee Online Systems Manager Dimension Data
- Slide 3
- 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
- Slide 4
- Business Intelligence BI - 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
- Slide 5
- What is PowerPivot With a few mouse clicks, a user can create and publish intuitive and interactive self-service analysis solutions
- Slide 6
- Slide 7
- DEMO : SHOULD I GO SWIMMING TONIGHT? 7
- Slide 8
- Slide 9
- DEMO : * HR DEPARTMENT * ATM TRANSACTIONS * AGED PAYMENT ANALYSIS 9
- Slide 10
- DEMO : MAP IT 10
- Slide 11
- Calculated Measures TotalSalesInMillions=CONCATENATE(R", CONCATENATE(FORMAT(CALCULATE(ROUND(SUM (FactSales[SalesAmount])/1000000, 0)), "#,###"), "M")) Creates a string that encloses the sales amount in a Rand sign and an M for millions. The value is rounded to a million. SalesPrevYr=CALCULATE(SUM (FactSales[SalesAmount]), DATEADD(DimDate[DateKey], -1, YEAR)) Calculates the sales amount for the previous year. YoY =IF(SUM(FactSales[SalesAmount]),(IF ([SalesPrevYr],(SUM (FactSales[SalesAmount]) - [SalesPrevYr])/[SalesPrevYr], BLANK())),BLANK()) Calculates the year-over-year growth if the current year and the previous year have sales values. The formula returns BLANK() if the previous or the current year has no value.
- Slide 12
- Slide 13
- Slide 14
- Slide 15
- Slide 16
- TIPS AND TRICKS 16
- Slide 17
- 17 Some Tips and Tricks Tip #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)
- Slide 18
- 18 Some Tips and Tricks Tip #2 : Less Columns / More Rows = Speed
- Slide 19
- 19 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])
- Slide 20
- Slide 21
- Slide 22
- Slide 23
- Slide 24
- Slide 25
- Slide 26
- Slide 27
- Slide 28
- Slide 29
- Slide 30
- Slide 31
- Slide 32
- Slide 33
- Slide 34
- Slide 35
- Slide 36
- Whats new in PowerPivot in Denali?
- Slide 37
- Rich Modeling Capabilities Multiple relationships, hierarchies Parent-child relationships Key performance indicators, drillthrough, perspectives Rich data types, BLOBs, images
- Slide 38
- 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