PowerPivot Presentation

Report
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
ATM Transactions with a little DAX
Payment Age Analysis with DAX and Related Tables
Some Tips and Tricks to take home
3
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
DEMO :
SHOULD I GO SWIMMING TONIGHT?
7
DEMO :
* HR DEPARTMENT
* ATM TRANSACTIONS
* AGED PAYMENT ANALYSIS
9
DEMO : MAP IT
10
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.
TIPS AND TRICKS
16
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
•
•
•
•
•
•
•
•
•
•
•
•
•
•
19
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])
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

similar documents