Power Pivot for Excel and Share Point Matthew

Power. Pivot for Excel and Share. Point Matthew Stephen & Will Thompson Business Intelligence Technical Specialists Microsoft Ltd

Power. Pivot

Questions? Text Microsoft to 60300 Or Tweet #uktechdays

Agenda The Business Intelligence Continuum Power. Pivot for Excel Power. Pivot for Share. Point Power. Pivot for I. T. 5

The Business Intelligence Continuum Strategy Driven Business Compliance & Risk Management Consistent Measurement & Rewards Innovative Organization Aligned Organisational BI 6 Local Decision Making Empowered Team BI The Org’s Context Our Context BI Solution created by IT, Established corporate context & is reusable, scalable and backed up. BI Solution created by power user. Context is for a small team & it’s managed on a server. Personal BI My Context BI solution created by user. Context is only for user & exists as document. SQL Server 2008 R 2 extends reach from organizational BI to team and personal BI

Power. Pivot for Excel 2010 Power. Pivoting Massive Data Volumes 7 With a few mouse clicks, a user can create and publish intuitive and interactive self-service analysis solutions

Power. Pivot - Data Analysis Expressions (DAX) DAX uses Excel syntax and many Excel functions Fetch data across relationships (one-many, many-one) Set or modify calculation context Sample DAX expression = [First Name] &“ ”& [Last Name] =SUM(Sales[Amount]) =RELATED (Product[Cost]) =[Sales](PREVIOUSYEAR(Dim. Date[Datekey])) 8 Comment String concatenation just like Excel SUM function takes a column name instead of a range of cells new RELATED function follows relationship between tables 35 time-series functions 8

Reporting Services as a Data Source RT PU BL ISH IN DA G OLTP AND OLAP DATA SOURCES 9 TA ased B t r o Rep a Feeds Dat O IMP

Power. Pivot for Share. Point Share and Collaborate With Share. Point: • Publish your Power. Pivots as Web applications for your team • Schedule data refreshes to keep your analysis up-to-date • Manage security just like a document 10

Power. Pivot is Great for I. T. Power. Pivot is great for IT: • Track the usage of Power. Pivots • Discover mission-critical applications 11

Power. Pivot 12

Power. Pivot - Summary Analyse external data in the context of corporate data Analyse large data sets beyond the limits of Excel Share insights Use reports as databases Enable I. T. to provide required resources Use as a tool to gather business requirements 13

For resources, decks and video: www. microsoft. com/uk/techdays

© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.