Transcript PowerPivot Presentation
17-20 OCTOBER 2011
DURBAN ICC
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
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
What is PowerPivot
With a few mouse clicks, a user can create and publish intuitive and interactive self-service analysis solutions
7
DEMO : SHOULD I GO SWIMMING TONIGHT?
9
DEMO : * HR DEPARTMENT * ATM TRANSACTIONS * AGED PAYMENT ANALYSIS
10
DEMO : MAP IT
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 YoY Δ
=CALCULATE(SUM (FactSales[SalesAmount]), DATEADD(DimDate[DateKey], -1, YEAR)) Calculates the sales amount for the previous year.
=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.
16
TIPS AND TRICKS
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”) 17
Some Tips and Tricks
Tip #2 : Less Columns / More Rows = Speed
18
• • • • • • • • • • • • • •
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]) 19
What’s new in PowerPivot in Denali?
Rich Modeling Capabilities
• • • • Multiple relationships, hierarchies Parent-child relationships Key performance indicators, drillthrough, perspectives Rich data types, BLOBs, images
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