Intro to powerpivot
Download
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


The funny thing about europe
“The funny thing about Europe”


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”



Schema diagram cont d
Schema Diagram, cont’d




Football demo 1 measures
Football Demo #1: Measures


The calculate function
The CALCULATE() Function

  • Your new best friend

  • Like SUMIF(), modernized

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


Football demo 2 cube formulas
Football Demo #2: Cube Formulas


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