intro to powerpivot
Download
Skip this Video
Download Presentation
Intro to PowerPivot

Loading in 2 Seconds...

play fullscreen
1 / 26

Intro to PowerPivot - PowerPoint PPT Presentation


  • 50 Views
  • Uploaded on

Intro to PowerPivot. Stockholm, Jan 27, 2010 Rob Collie, Sr. Program Mgr , PowerPivot. “The funny thing about Europe”. Agenda. Why PowerPivot ? Demos! Why SharePoint? Demos!. Why PowerPivot ?. Money  Biz logic bottleneck Time Cost Lack of agility Embrace and improve Excel

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Intro to PowerPivot' - elsie


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
intro to powerpivot

Intro to PowerPivot

Stockholm, Jan 27, 2010

Rob Collie, Sr. Program Mgr, PowerPivot

agenda
Agenda
  • Why PowerPivot?
  • Demos!
  • Why SharePoint?
  • Demos!
why powerpivot
Why PowerPivot?
  • Money
  • Biz logic bottleneck
    • Time
    • Cost
    • Lack of agility
  • Embrace and improve Excel
    • Manage and assist, not fight
  • Supplement centralized BI
temp mashup demo
TempMashup Demo
  • Loading Data
  • Simple Pivot
  • Mashup with Internet data
  • Calc columns and relationships
  • DAX Measures
dax measure golden rules
DAX Measure Golden Rules
  • Home Table = Fact Table
    • Wherever the column to be aggregated lives
  • No naked columns!
    • Sum([Quantity]), never just [Quantity]
    • Measures themselves are also ok
  • Calculations happen against the TABLES
    • Ex: Sum of a column in a PowerPivot table
    • NOT a sum of PivotTable cells
    • NOT a navigation of dimensional space
  • Two Phases: Filter, then Aggregate
  • During Filter, think “Cell-by-Cell”
  • Filters flow from One to Many, NEVER vice versa
movie explaining the golden rules
Movie Explaining the Golden Rules
  • Pretty sure I lost people here
  • Probably (hopefully) clearer here:
    • http://bit.ly/ambRrv
    • http://bit.ly/bv7tBo
the great football project
The Great Football Project
  • Real Project! (2006)
  • Professional BI consultant
    • $50k, 3 months
    • Top-notch SSIS/SSAS pro
  • Professional data!
    • $100k license to STATS Inc.
    • “Professional” only meant “Expensive”
the calculate function
The CALCULATE() Function
  • Your new best friend
  • Like SUMIF(), modernized

=CALCULATE(<Aggregation>,<filter1>,<filter2>,…)

why sharepoint
Why SharePoint?
  • XL Pro
    • (Practical)Security
    • No “Save As” hijacking
    • Guaranteed deployment of updates
    • Autorefresh
    • “Professionalized” results
  • DB Pro / IT
    • Transparency
    • Usage tracking
    • Focused point of engagement
sharepoint demos
SharePoint Demos
  • Gallery
  • Autorefresh
  • Usage dashboard
db pro xl pro partnership
DB Pro/XL Pro Partnership
  • DB Proprovides clean, complete, reliable data
  • XL Pro provides biz logic and reports
  • Deputized XL users as ambassadors
    • Provide focused feedback on DW
    • “If we had a table like this, we could do X”
  • DB Pro can proactively offer additional support
    • “I see you are using Y as a data source”
    • “Can we help insulate against outages?”
    • “I better not rename that db after all”
more dax functions you should know about
More DAX Functions You Should Know About
  • ALL()
    • Clears pivot filtering from specified fields
    • Clears filters for measure calculation, not visually
    • Useful for % of total calcs
    • SUM([Sales]) /

CALCULATE(Sum([Sales]),All([Products])

  • Time Series Functions
    • 39 Functions
    • PARALLELPERIOD(), TOTALMTD(), PREVIOUSDAY()…
cube side effect
Cube “Side Effect”
  • PowerPivotdb engine IS Analysis Services
  • PowerPivot Workbooks are exposed as cubes
    • ONLY when published to SharePoint
  • Query API (OLAP) is 100% unchanged
    • Must use updated provider (OLEDB, ADOMD)
    • Connect via workbook URL
  • Subset of cube concepts supported
    • No true hiearachies for instance
my advice for bi pros learn about sharepoint
My Advice for BI Pros: Learn About SharePoint
  • All MS BI now centered on SharePoint
    • This was bottom up process, not an executive edict…
    • …which makes it a much more significant trend
    • …and it’s also why we havenot said this as loudly as I’d like
  • Complete BI solutions will now often involve:
    • SharePoint Install/Config
    • Integration between BI apps
    • BI integration into existing SharePoint solutions
    • Custom web part development (or integration)
    • SharePoint authentication and security
  • SharePoint pros more aware of BI than I had expected
contact info
Contact Info
  • Send me questions!
  • http://PowerPivotPro.com
    • These demos, in detail
    • More demos, how-to’s
    • Observations
    • Humor (sometimes)
  • http://PowerPivotFAQ.com
    • Already > 100 questions answered
    • And Portuguese translation! :)
more info
More Info
  • Official Site http://powerpivot.com
    • Promo and how-to videos
    • Virtual lab – try PowerPivot with no install
  • Official team blog http://blogs.msdn.com/powerpivot/
  • PowerPivot Beta Links
    • http://powerpivotpro.com/powerpivot-beta/
  • Temp Mashup Demo
    • Workbook http://bit.ly/3wt59J
    • Posts/Videos http://bit.ly/1kBllz
    • DAX Measure Tutorial http://bit.ly/2megXY
more info cont d
More Info Cont’d
  • The Great Football Project
    • All posts: http://bit.ly/19OXdh
    • Start from beginning: http://bit.ly/4Fi6CE
  • Cube formulas
    • http://bit.ly/6IjmJi and http://bit.ly/7olQb3
  • Architecture http://bit.ly/bPnWHo
  • DAX-specific FAQhttp://bit.ly/cRtbSu
  • Data Feeds http://bit.ly/2igFYB
  • Links to many PowerPivot experts’ sites
    • http://bit.ly/7RKa6C
more info cont d1
More Info Cont’d
  • PowerPivot’s Impact on BI Pros
    • http://bit.ly/4D5h8Y
    • http://bit.ly/2eu4MU
    • (Mostly in comments on those pages)
  • SharePoint’s Rising Significance for BI
    • Pt 1 http://bit.ly/9XkxRs
    • Pt 2 http://bit.ly/c6lfNj
    • Pt 3 http://bit.ly/zFE0B
ad