SharePoint 2010 and PowerPivot Webinar Q & A


This webinar included several live demonstrations and discussed:
  • The capabilities and target audience for this new technology
  • How self-service BI is transforming the way we think about and work with data
  • The myriad products & services that work in concert to deliver PowerPivot-driven BI
  • Advantages of self-service BI over traditional BI
  • SharePoint’s front & center role in the changing BI landscape

PowerPivot and SharePoint usher in a new era of self-service business intelligence and collaboration, which enables you to analyze and take advantage of the data and knowledge assets your organization already owns!

  • Question: Can you talk about using Power Pivot and SharePoint in non-Corporate environments? Will it work for me and my clients as a provider of hosted solutions?

Answer: Assuming you are hosting it yourself or the hosting service you select has installed PowerPivot for SharePoint it would work fine in a hosted environment. Full functionality in SharePoint requires an Enterprise license for Excel Services, so your hosting service would need to be at that level.
  • Question: Is PowerPivot going to get integrated with PPS and SSAS

Answer: PowerPivot is built on a foundation of SQL Server Analysis Services (SSAS) and PerformancePoint can use PowerPivot workbooks as a source for KPIs in PerformancePoint dashboards.

  • Question: I assume it should be able to connect to OLAP data source, right?

Answer:You can connect to an OLAP data source.

  • Question: Can PowerPivot use MapInfo data sources (TAB, DAT) to represent maps with added geo observations?

Answer: Microsoft has done some PowerPivot demos that included geographic information. Additional information about and analysis of the particular data of interest would be required to provide an accurate answer.

  • Question: Does Power Pivot work with Excel 2007 and SharePoint Services 3 or SharePoint 2007?

Answer: The PowerPivot for Excel add-in requires Excel 2010 and PowerPivot for SharePoint requires SharePoint Server 2010 Enterprise edition.

  • Question: Can you track schedules/calendar items with this?

Answer: PowerPivot works best on aggregated data, however if you are keeping your schedules in a SharePoint calendar then the event list behind that calendar could be used as a data source. You might use this to report on how people are spending their time based on appointments in the calendar.

  • Question: Does the user need a licensed copy of Excel to use PowerPivot or just a SharePoint CAL?

Answer: To use the PowerPivot for Excel add-in to create PowerPivot workbooks you need a licensed copy of Excel 2010. After the workbook is uploaded, you will only need a SharePoint Server Enterprise cal to open a PowerPivot workbook in Excel Services in a Web Browser.

  • Question: Does PowerPivot require Excel Services (would we need Enterprise CAL's?)

Answer: A full PowerPivot implementation does require Excel Services, so an Enterprise cal is required.

  • Question: Can PowerPivot connect with external .csv files, such as available with financial services and stock market data sets?

Answer: PowerPivot can use comma delimited files as a datasource.

  • Question: What about data level security? For e.g. I am only allowed to see West Coast data.

Answer: Data level security is dependent on how you define your connection to the data source involved. If you choose to impersonate the current user then data level security will be maintained. If you use a connection account, then the security of the connection account will be applied and the user may see data they don’t normally have access to.

  • Question: Does the PowerPivot Publishing Gallery also accept plain Excel files or only Excel PowerPivot workbooks?

Answer: Non-PowerPivot Excel Workbooks can be stored in a PowerPivot gallery just like any document library. You will still get a thumbnail and the ability to open the workbook in Excel Services, but you won’t have any PowerPivot data connectivity.

  • Question: Could the publishing be towards Blackberry and iPhone devices?

Answer: It is unlikely that the browser support built-in to Blackberry and iPhone devices will be compliant with the requirements for accessing PowerPivot workbooks in Excel services.

  • Question: Do the Silverlight controls come with SP 2010?

Answer: The Silverlight views of the PowerPivot Gallery are installed when you install the PowerPivot for SharePoint components. Silverlight support is built-in to all versions of SharePoint 2010.

  • Question: What's the difference between excel and excel services?

Answer: Excel is an Office application that runs on a client workstation. Excel Services is a feature of SharePoint Server Enterprise edition that allows the display and manipulation of Excel spreadsheets in a browser when they are published to a SharePoint document library.

  • Question: Can you comment on what is supported in 32 bit and what all needs to be 64 bit?

Answer: SharePoint 2010 requires a 64 bit server environment, but the PowerPivot for Excel add-in is fully functional in both a 32 bit and 64 bit version of Excel. The primary difference will be that the 64 bit version of Excel can access much higher quantities of RAM and can therefore manipulate much larger PowerPivot workbooks and data sources.

  • Question: Is PowerPivot free for both Excel and SharePoint?

Answer: Both the PowerPivot add-in for Excel and the PowerPivot extensions for SharePoint Server 2010 are free. Excel and SharePoint Server 2010 must be purchase.

  • Question: Will PowerPivot for Sharepoint work in any WSS version or does it require the full Office SharePoint?

Answer: PowerPivot for SharePoint requires Excel services, which is only provided with SharePoint Server 2010 Enterprise edition. It will not work with any other version of SharePoint. However, you can upload/download PowerPivot workbooks from any version of SharePoint. You just can’t open them in the browser without Excel services.

  • Question: Are PowerPivot for Excel and for SharePoint 2010 released and available to the general public?

Answer: PowerPivot for Excel and SharePoint Server 2010 are both currently in pubic beta and should be released to the retail channel sometime during the first half of 2010 (current estimate is May 2010).

  • Question: Can scheduling be based on a dynamic time frame? e.g., when the source file updates, run the report; one week the file may update on Monday, another week a source file may update on Tuesday.

Answer: No, scheduling is based on a set schedule. But since most refreshes require minimal server resources it would be best to setup a schedule to refresh more frequently than necessary.

  • Question: Will there be connections created for SAP BW?

Answer: There are ways to connect to SAP BW data from a PowerPivot workbook. This is most commonly done by connecting to SAP BW through SSRS. Take a look at the following post from the PowerPivot FAQ for more details: http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=124

  • Question: This is very cool if all BI is presented through worksheets, but can these dashboard tools be used if BI is presented through PerformancePoint Server dashboards or SSRS reports?

Answer: PowerPivot workbooks can be used as data sources when generating SSRS reports and PerformancePoint dashboards. In the same way SSRS reports can be used as data sources in PowerPivot. It is unclear at this time whether PerformancePoint can be used directly as a data source in PowerPivot, however the database behind PerformancePoint or exports from PerformancePoint could definitely be accessed as data sources.

  • Question: What is DAX? I missed that...

Answer: DAX stands for Data Analysis Expressions. DAX is an expression language based on Excel formula syntax that can be used to calculate new columns of data in a PowerPivot workbook.

  • Question: Where do I find more information on DAX syntax and formulas? Are there a lot of new capabilities with DAX or is it just the new interpretation of existing Excel formulas/functionality for the PowerPivot add-in?

Answer: There are quite a few new capabilities available in DAX. Check the following blog post from the product team for a good introduction to DAX: http://blogs.msdn.com/powerpivot/archive/2009/10/01/introduction-to-data-analysis-expressions-dax-in-gemini.aspx

  • Question: Can you run Office 2007 and Excel 2010 on the same client?

Answer: Office 2007 and Office 2010 can be installed and run on the same client. The only requirement is that they both need to be the same codebase. For example, if you have installed the 32 bit version of Office 2007 then you can only install the 32 bit version of Office 2010. To install the 64 bit version of Office 2010 all Office components need to be 64 bit. PowerPivot for Excel works with either the 32 or 64 bit versions of Excel 2010.

  • Question: I'm still not seeing a thin client solution that truly replaces office web component, am I missing something? Where is the dynamic slice and dice in a thin client?

Answer: The thin client (Excel services in a web browser) is not a complete replacement for loading a PowerPivot workbook in Excel. But you can click on the buttons in the slicers to change slicers in the web browser and the workbook values and graphs will change. You can also modify any row or column filters that you have added to the workbook.

  • Question: When you connect to an Analysis Services cube does the data come in and get stored in the PowerPivot AS Engine or does it continue to leverage the native AS cube? Is there any hit to the relational database source of the original AS cube or is the connection just hitting the processed cube? (Now that I see your demo I’m expecting it will get stored in the XML file that you showed. Can you confirm?)

Answer: The data from any data source will be processed and stored in the PowerPivot workbook.

  • Question: Can PowerPivot be deployed to SharePoint and browsed on the SharePoint server if the user does not have Office 2010 installed locally?

Answer: Yes, Excel services makes it possible to access PowerPivot workbooks with just a browser. Office 2010 is only required on the local workstation if you download the workbook.

  • Question: If we are looking at PowerPivot versus SQL Reporting Services web-based self-service cubes/reports, what are the advantages of PowerPivot?...is it better graphing flexibility/more sophisticated manipulation of the data...also would like to see a lower level roadmap of how PowerPivot would work in an underlying Sequel database environment....is it a faster or slower implementation timeframe/effort?

Answer: One of the real differentiating characteristics in PowerPivot is its ability to relate data from different data sources into a seamless data set for analysis. As shown in some of the demonstrations data can be pulled from SQL databases, Excel, and even text files and built into a single PowerPivot dataset. DBAs can do similar things with SQL, but this places that capability in the hands of the Information Worker.

  • Question: Does PowerPivot have write-back capability?

Answer: No, Data sources in PowerPivot are strictly Read-Only to allow for analysis of the data and relationships within the data.

  • Question: We currently use analysis services. This seems like its analysis services reinvented and being put into the hands of non bi professionals. What’s the future of analysis services? This tool looks like it is creating a bunch of pockets of what typically would be centralized in analysis services.

Answer: PowerPivot does provide many of the same capabilities that were traditionally only available to database professionals. That doesn’t remove the need for Analysis services; it simply removes one of the traditional bottlenecks that has restricted the availability of BI processing for business users. In the past users needed to rely on DBAs for the creation of complex OLAP cubes before they could do “what if” analysis. PowerPivot makes it possible for Information Workers to do much of this traditional work for themselves.

  • Question: Is there a SharePoint print solution for PowerPivot from the ribbon? Or does it rely on the browser print functionality?

Answer: Like SharePoint it relies on the browser and cascading style sheets for printer support.

  • Question: Are you able to connect to MySQL or to OLAP?

Answer: PowerPivot can connect to a variety of data sources including MySQL and OLAP.

  • Question: Is there anything you can do with PowerPivot with the free version of SharePoint 2010?

Answer: PowerPivot for SharePoint requires Excel services, which is only provided with SharePoint Server 2010 Enterprise edition. It will not work with any other version of SharePoint. However, you can upload/download PowerPivot workbooks from any version of SharePoint. You just can’t open them in the browser without Excel services.