SharePoint 2010 Business Intelligence

1. SharePoint 2010 Business Intelligence Module 9: PowerPivot

2. Overview • PowerPivot For Excel • PowerPivot For SharePoint

3. Lesson: PowerPivot For Excel • PowerPivot Introduction • PowerPivot and Excel • PowerPivot Features • PowerPivot Functions • VertiPaq Engine • Where are my rows? • SQL Azure • CodePlex Projects • PowerPivot versus SSAS

4. PowerPivot Introduction • Microsoft's PowerPivot for Excel 2010 is a data analysis tool for business users that delivers unmatched computational power directly within Excel 2010 • Why Excel? • Excel is an easy and known tool to all business users • Why PowerPivot? • Creating and Building Cubes with Analysis Services is complex and over kill for many organizations • NOTE: PowerPivot creation and modification works in Excel 2010 only! • Requires Enterprise SKU of SharePoint

5. PowerPivot & Excel • PowerPivot extends Excel • Excel is the most popular tool for business users • Allows users to manage data and make decisions (If IT can’t provide the tools, Excel is used) • There are both 32bit and 64bit versions, must match your installed Office 2010 version • PowerPivot improves Excel and End users by • Increasing efficiencies • Powerful toolset of functions

6. Power Pivot Features • Data Analysis Expressions (DAX) • Express BI logic based on Excel formula syntax with added functionality • Calculated Columns (cleanse and extend) • PivotTable Measures (advanced analysis) • Tools for working with Data Sources • Table Import Wizard (html tables, other tabular data) • Join disparate data into composite data sources (local text file with remote database, two separate data warehouses) • Data Feeds (SharePoint 2010 REST, ATOM) • You can also copy and paste into the current in-memory data! • Data/Column-based compression • Load large data sets into memory and process in seconds • Bring Slicing and Dicing to Excel • Use to only exist in tools like Analysis Services (OLAP) when using large datasets

7. PowerPivot DAX Functions • PowerPivot has many different categories of functions: • Date and Time (EOMonth, Weekday) • Filter and Value (Calculate, All) • Logical (true, false) • Math and Trig • Statistical (CountRows, SumX) • Text • Time Intelligence (StartOfMonth, EndOfMonth) • Information (IsError)

8. VertiPaq Engine • Wait, you can load “millions” of rows in Excel? What? • Yep, using the VertiPaq engine, you can download and compress millions of rows of data into Excel • What is VertiPaq? • An SSAS implementation • AMO and ADOMD.NET are used to interface with VertiPaq • Means that traditional MDX and XMLA can be used to query the multidimensional data generated • Everything runs in-proc to Excel (best performance on 64bit client) • If one crashes, all crashes • Limited to the memory of the machine • Most data you are crunching should have been already pre-processed at some level • Alleviate some of the work from the client machine

9. Where are my rows? • When you pull down 100’s of millions of rows where does the data go? • Answer: Memory • In-Memory BI (IMBI) is a database storage technique used by PowerPivot • Common pattern used in large scale transaction systems • Allows for no-disk I/O, quick data scans and compression of data • Column striping versus Row striping • When saving the data, it goes into the workbook (zip file) • \customXml\item1.data • For caching purposes, some resides in the %temp%\VertiPaq_<GUID> folder before save • Note: SharePoint has a 2GB File limit, variable compression ratio means what runs in PowerPivot might not save in SharePoint! • Depending on your dataset, your compression will vary (15x), the more data, the better the compression

10. SQL Azure Support • One of the data sources supported with PowerPivot is SQL Azure • SQL Azure is a database server in the cloud • Just a simple SQL Server hosted somewhere else that will have (at some point) guaranteed uptime and performance features • As of today, the following is not supported in the Cloud • Analysis Services • Reporting Services • Replication • Service Broker

11. Avoiding PowerPivot PowerPitfalls • PowerPivot is no doubt easy and powerful, however, be sure to Plan! • Training your PowerPivot users • DAX, Best Practices (single workbook vs multiple) • Cooperation between business units and IT • Plan for power user access to data (data feeds) and support • Planning your PowerPivot for SharePoint deployment(s) • Dept, Enterprise • PowerPivot requires Office 2010 • You have to plan another rollout of Office • You still need highly experienced BI Consultants!

12. PowerPivot Resources • Several helpful resources for PowerPivot exist • PowerPivotpro.com • PowerPivotTwins.com • CodePlex has a PowerPivot Sample Data project • http://powerpivotsampledata.codeplex.com • Data rich access databases that you can “play” with

13. Lesson: PowerPivot For SharePoint • Traditional Excel Issues • Why Excel And SharePoint? • Features • Management

14. Traditional Excel Problems • Emailing the data • Leaves the company, can cause Inbox problems (160MB reports) • Ensuring correct versions are published to recipients • Keeping the report up to date (changing data and requirements) • Report author is out for a week the report is due, what happens?

15. Why Excel and SharePoint? • Share and Collaborate • Put in document library, not in email • Publish your Excel reports to SharePoint • In place, scheduled data refreshes • Manage security with SharePoint • If they don’t have at least view permission, they will never know it exists!

16. PowerPivot, Excel and SharePoint, Oh My! • Combining all three technologies presents giant leap forward in the world of Business Intelligence • SharePoint provides the Security and Management • Excel provides the simple and easy user interface • PowerPivot provides the data analytics engine

17. PowerPivot for SharePoint Features • Tight integration with SharePoint through Solutions and Features • PowerPivot Gallery – Rich preview features • View Only Permissions allow interaction without modification • Set of custom actions added to Central Administration for management • Unattended Data Refresh • PowerPivot data sources can be refreshed based on your custom schedule • Highly informative charts and reports provide information on usage • Utilize built in reports to track usage • Discover mission-critical applications

18. PowerPivot for SharePoint Installation • Install is done through the SQL Server 2008 R2 installer package • Installer can create a new SharePoint Farm or add support for an existing Farm • Installation is MUCH simpler using the SQL Installer to create a NEW farm • PP4S requires a VertiPaq Analysis Services database instance • If you have installed the full Analysis Services you will need to create another instance that is in VertiPaq mode • ASOMD.NET 10.50.xxxx

19. PowerPivot for SharePoint Components • Components include: • SharePoint Service • SharePoint Service Application • Solutions and Features • Dashboards, web parts, content types and list templates (PowerPivot Gallery and Data Feed Library) • Application Database for configuration, schedule information for data refresh • Timer Jobs • Removes cached data files on the servers if workbooks are deleted or modified • PowerPivot Web Service and managed extension • Handles requests between clients and PowerPivot for SharePoint

20. PowerPivot and Reporting Services • Exciting things happen when both are installed together • A report can be used as a data source in PowerPivot • New reports can be created from PowerPivot workbooks • Requires SQL Server 2008 R2 Reporting Services

21. Programmatic Interfaces • AMO • Analysis management objects allows you to query the properties and structures of PowerPivot workbook in the farm • You can query, but you cannot create or modify AS objects. Only Excel 2010 can be used to modify PowerPivot workbooks

22. Data Storage Requirements • PowerPivot workbooks will be stored in SharePoint • Workbooks contain their data (remember our compression ratio)? These workbooks can get very large • SharePoint officially can only hold 2GB files (even with RBS) • Versioning enabled on a PowerPivot document library could pose significant storage issues

23. Server versus Client Processing • SharePoint 2010 is 64bit • PowerPivot workbooks that are created in a 64bit environment with a correspondingly high amount of memory will work great on a similar environments • Clients are not a server • Clients that try to tackle a workbook that has lots of data may not be successful (lack memory and processor capabilities of a server) • Evaluate the client infrastructure and ensure all users can take advantage of your PowerPivot workbooks • If working with more than 1 Million rows, you should be 64bit client

24. PowerPivot Monitoring • Server Status • CPU and Memory Utilization • System Capacity and Performance • Workbook and User Activity over Time • Quality of Service • Query Response Times • Current Server State • Largest and Most Popular Solutions • Solution Statistics and Monitoring • Document Information and Data Sources • Activity and Top Users • Query Performance per Workbook

25. Lab 1: Power Pivot • Install PowerPivot for Excel • Install PowerPivot for SharePoint 2010

26. Lab 2: Use Power Pivot • Create PowerPivot Workbooks • Configure PowerPivot for SharePoint • Upload PowerPivot Workbooks to SharePoint

27. Review • Your instructor will ask a series of questions on this module

28. Summary • PowerPivot is a powerful extension to Excel • Various new functions and tools give Excel true BI capabilities