leveraging powerpivot
DESCRIPTION
MN Microsoft BI user group presentation 3/22/2011 going over PowerPivotTRANSCRIPT
![Page 1: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/1.jpg)
Leveraging PowerPivot
Dan English Business Intelligence Consultant Superior Consulting Services, LLC
![Page 2: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/2.jpg)
Speaker Background
• Developing with Microsoft technologies for over 14 years
• Over 7 years experience with Data Warehousing and Business Intelligence
• Microsoft SQL Server MVP, Microsoft Certified IT Professional (MCITP), and Microsoft Certified Technology Specialist (MCTS)
• Architect and develop dashboard solutions for enterprise reporting and monitoring
• Experienced in ETL and Analysis Services development, requirements gathering, and data modeling
• PASSMN 2009/2010 – Executive Board Chair (President)
• Microsoft MN BI User Group 2011 – Leadership Board
• Twitter – http://twitter.com/denglishbi
• Vimeo Videos - http://vimeo.com/user3952239/videos
Dan English http://denglishbi.wordpress.com
![Page 3: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/3.jpg)
SCS Background
• Founded in 1997, SCS is a technical consulting firm focused exclusively on Microsoft technologies to provide custom solutions
• Headquartered in Burnsville, MN
• Microsoft Gold Certified Partner since 2006
• Provide solutions in the areas of Business Intelligence, Reporting, Application Development, Networking, SharePoint, and Special Projects
• Industry recognized authorities via books, speaker engagements, and direct assistance to Microsoft
![Page 4: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/4.jpg)
Session Outline
• Microsoft Business Intelligence Overview
• What is PowerPivot?
• Comparison of SSAS (UDM) & PowerPivot
• Discuss DAX
• Demo
• What’s coming?
• Questions
![Page 5: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/5.jpg)
Quick Audience Poll
How many are currently using SSAS (UDM) in production?
How many have Excel 2010 deployed?
How many are currently using PowerPivot? In production? With SharePoint?
How many are waiting until v2 of PowerPivot?
![Page 6: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/6.jpg)
Microsoft’s BI Vision
Complete and integrated BI and Performance Management offering
Agile products that adapt to how you need the information
Built on a trusted part of your IT backbone
Improving organizations by providing business insights to
all employees leading to better, faster, more relevant decisions
![Page 7: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/7.jpg)
Microsoft Business Intelligence Overview
Business User Experience
•Analysis Services
•Reporting Services
•Integration Services
•Master Data Services
•Data Mining
•Data Warehousing
•Dashboards & Scorecards
•Excel Services
•Web based forms & workflow
•Collaboration
•Search
•Content Management
•LOB data integration
•Self-Service access & insight
•Data exploration & analysis
•Predictive analysis
•Data visualization
•Contextual visualization
Business Collaboration Platform
Data Infrastructure & BI Platform
![Page 8: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/8.jpg)
What is PowerPivot?
PowerPivot supports files up to 2GB in size. The 64-bit version of PowerPivot enables you to work with up to 4GB of data in memory, and the 32-bit version enables you to work with up to 2GB of data in memory (500 to 700MB files)
Excel 2010 (32-bit or 64-bit) is required to install PowerPivot for Excel.
Office Shared Features must be installed with Excel 2010. .NET Programmability Support, in Microsoft Excel, must also be installed.
PowerPivot for Excel is a free download that significantly expands the data analysis capabilities of Excel.
![Page 9: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/9.jpg)
What can it do?
IW Tool
Microsoft SQL Server
Microsoft Analysis Services
Microsoft PowerPivot
Microsoft PDW
Microsoft SQL Azure
Microsoft Access
Oracle
Teradata
Sybase
Informix IBM DB2
ODBC / OLEDB
ATOM datafeeds
Azure DataMarket
Microsoft Excel
Reporting Services
Text Files
Personal BI
Team BI
DAX
PivotTables
PivotCharts
Self-service
Agile
Slicers
Sparklines
Data Bars
Measures
Calculated Columns
Relationships
![Page 10: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/10.jpg)
What does it provide?
What you do get… • VertiPaq engine (in-memory column store) • External data connectivity • DAX functions – Excel like with intellisense • Excel user interface • PivotTables and PivotCharts
What you don’t get… • Dynamic user level security • Hierarchy support, parent\child, unary operators* • KPIs • Attribute properties and cube actions • Advanced calculation capabilities • Perspectives • Incremental data loads • Robust Enterprise OLAP Solution *There are workarounds with good DAX knowledge and model design
![Page 11: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/11.jpg)
SSAS (UDM) vs. PowerPivot
SSAS (UDM) PowerPivot (v1)
Mature Product New Product
Scalable Personal / light weight
Massive data (TBs) Large data (GBs)
OLAP Engine VertiPaq Engine
Data Source View PowerPivot Window w/ data
Dimensions & Facts Tables
Measures Measures
Indexes / Aggregations No Indexes / Aggregations
Actions (drillthrough, report) No actions
Perspectives No perspectives
Translations No translations
Cube browser Excel for browsing
![Page 12: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/12.jpg)
Loading Comparison
Data mart OLAP Engine
Source Data
Analysis Services (UDM)
Source Data
PowerPivot
VertiPaq Engine
![Page 13: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/13.jpg)
What can PowerPivot build?
![Page 14: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/14.jpg)
Data Analysis Expressions (DAX)
Calculated Columns • Concatenate values • Create new values • Perform lookups
• Examples
• Month =CONCATENATE(FORMAT(MONTH([Datekey]),"00 "),LEFT([CalendarMonthLabel],3))
• Profit =[SalesAmount]-[TotalCost]-[ReturnAmount] • ProductCategory =RELATED(DimProductCategory[ProductCategoryName]) • LifetimeSales =SUMX(RELATEDTABLE(FactSales), FactSales[SalesAmount])
![Page 15: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/15.jpg)
Data Analysis Expressions (DAX)
YTDSales = TOTALYTD(SUM(FactSales[SalesAmount]),DimDate[Datekey])
SalesAmount = SUM('FactSales'[SalesAmount])
CumulativeSales = CALCULATE(SUM(FactSales[SalesAmount]), DATESBETWEEN(DimDate[Datekey], FIRSTDATE(ALL(DimDate[Datekey])), LASTDATE(DimDate[Datekey])))
http://denglishbi.wordpress.com/2011/01/24/calculating-cumulative-values-in-microsoft-bi-tools/
Measures
PY YTD Sales = TOTALYTD(SUM(FactSales[SalesAmount]), DATEADD(DimDate[Datekey],-12,MONTH))
Profit % = Sum([Profit])/Sum([SalesAmount])
![Page 16: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/16.jpg)
Data Analysis Expressions (DAX)
Retail 454 Calendar Calculation PY_SalesAmount = IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Year])) = 1, CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date], IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Quarter_Of_Year])) = 1, IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Month_Of_Year])) = 1, IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Week_Of_Year]))=1, IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Day_Of_Year]))=1, FIRSTDATE(DimRetailDate[PY_Date]), FIRSTDATE(DimRetailDate[PY_Reporting_Week_Start_Date])), FIRSTDATE(DimRetailDate[PY_Reporting_Month_Start_Date])), FIRSTDATE(DimRetailDate[PY_Reporting_Quarter_Start_Date])), FIRSTDATE(DimRetailDate[PY_Reporting_Year_Start_Date])), IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Quarter_Of_Year])) = 1, IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Month_Of_Year])) = 1, IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Week_Of_Year]))=1, IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Day_Of_Year]))=1, FIRSTDATE(DimRetailDate[PY_Date]), FIRSTDATE(DimRetailDate[PY_Reporting_Week_End_Date])), FIRSTDATE(DimRetailDate[PY_Reporting_Month_End_Date])), FIRSTDATE(DimRetailDate[PY_Reporting_Quarter_End_Date])), FIRSTDATE(DimRetailDate[PY_Reporting_Year_End_Date])))) ,BLANK())
http://denglishbi.wordpress.com/2010/10/09/round-2-calculating-previous-year-marketing-calendar-values-with-dax/
![Page 17: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/17.jpg)
POWERPIVOT Demo
![Page 18: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/18.jpg)
PowerPivot Demo Screenshot
![Page 19: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/19.jpg)
Self-service analysis delivered thru Excel
2010
Work with massive amounts of data
![Page 20: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/20.jpg)
PowerPivot Monitoring
Central Administration • Server Health
• Avg Instance CPU • Avg Instance Memory • Query Response Time • Activity • Performance
• Historical Utilization • Users vs Queries
• Usage monitoring • # of Users • # of Queries • Size of workbook
![Page 21: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/21.jpg)
What’s coming?
SQL Server 11 ‘Denali’ • PowerPivot v2 add-in • Business Intelligence Semantic Model (BISM)
• Visual Studio 2010 • Corporate / Enterprise BI solution • Source control integration
• DAX additions – like rank, distinctcount, and hierarchy support
• KPIs – value, target, status • Partitioning • Role-based security • Perspectives • Project ‘Crescent’ – Silverlight reporting • Column store index -> relational database
![Page 22: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/22.jpg)
How do you choose?
Situation Option
Existing SSAS (UDM) corporate BI solution SSAS (UDM)
POC or test out data model change PowerPivot
End users building and deploying solutions PowerPivot
New BI solution Consider PowerPivot/BISM
Scal
abili
ty
Richness
VertiPaq
UDM
Fits
in m
emo
ry
Do
esn
’t f
it in
mem
ory
Static Reporting Ad-hoc Analysis Advanced Calcs
![Page 23: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/23.jpg)
Analysis Services Summary
Personal – simple solution created by user only for user.
Team – rich solution created by user for team / department deployed to server.
Corporate – solution created for
organization with centralized logic and
data which is scalable.
Empower Align
PowerPivot for Excel PowerPivot for SharePoint SSAS - UDM / BISM
![Page 24: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/24.jpg)
Resources
• Microsoft BI Sites - http://www.microsoft.com/bi/ AND http://technet.microsoft.com/en-us/bi/default.aspx
• PowerPivot - http://www.powerpivot.com
• PowerPivot for Excel (Business Intelligence) Portal - http://technet.microsoft.com/en-us/bi/ff604673.aspx
• PowerPivot Info (Vidas Matelis) - http://www.powerpivot-info.com
• PowerPivot Pro (Rob Collie) - http://www.powerpivotpro.com
• PowerPivot Geek (Dave Wickert) - http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx
• Alberto Ferrari & Marco Russo - http://sqlblog.com/blogs/marco_russo AND http://sqlblog.com/blogs/alberto_ferrari
• Kasper de Jonge - http://www.powerpivotblog.nl/
![Page 25: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/25.jpg)
QUESTIONS
![Page 26: Leveraging PowerPivot](https://reader031.vdocuments.us/reader031/viewer/2022013118/549f842aac79594b4c8b48de/html5/thumbnails/26.jpg)
THANK YOU! [email protected]