1 / 26

# Intro to PowerPivot - PowerPoint PPT Presentation

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

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

## 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

Stockholm, Jan 27, 2010

Rob Collie, Sr. Program Mgr, PowerPivot

“The funny thing about Europe”

• Why PowerPivot?

• Demos!

• Why SharePoint?

• Demos!

Why PowerPivot?

• Money

• Biz logic bottleneck

• Time

• Cost

• Lack of agility

• Embrace and improve Excel

• Manage and assist, not fight

• Supplement centralized BI

TempMashup Demo

• Simple Pivot

• Mashup with Internet data

• Calc columns and relationships

• DAX Measures

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

• Pretty sure I lost people here

• Probably (hopefully) clearer here:

• http://bit.ly/ambRrv

• http://bit.ly/bv7tBo

• 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

Football Demo #1: Measures

The CALCULATE() Function

• Your new best friend

• Like SUMIF(), modernized

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

Football Demo #2: Cube Formulas

• 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

• Gallery

• Autorefresh

• Usage dashboard

• 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”

• 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()…

• 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

• 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

• Send me questions!

• http://PowerPivotPro.com

• These demos, in detail

• More demos, how-to’s

• Observations

• Humor (sometimes)

• http://PowerPivotFAQ.com

• And Portuguese translation! :)

• 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

• 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

• 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