This article was written to help me sort out SharePoint, PowerPivot, Power View and Analysis Services. Read on to discover the questions that arose as I embraced this technology set and the answers I’ve uncovered.
So what are the options for working with Power View within SharePoint with MDX as the source cube? Let me frame this a bit, I am referring to SharePoint 2013 and SQL Server 2012 SP 1 or SQL Server 2014. It appears that PowerPivot becomes the data source for Power View reports. That is, a PowerPivot workbook saved within a Document Library. So it seems feasible that building a PowerPivot report pulling from an MDX cube, then stored in a Doc Library should work. The first test is the Document Library, PowerPivot and MDX cube test. If this works the same way as using a tabular cube, then we are one step closer to a complete solution.
Test 1
- Build a PowerPivot report pulling from a tabular cube
- Store it within a Document Library
- Review it’s behavior
- Build a PowerPivot report pulling from a MDX cube
- Store it within a Document Library
- Review it’s behavior
Is the behavior for the 2 reports the same?
If this is successful, then we need to introduce Power View. I am unclear how to introduce Power View into SharePoint. What is the method for creating a Power View report within SharePoint?
Research 1
How to build a Power View report within SharePoint?
Research Findings 1
There is an Microsoft article with instructions for doing this.You must use an RSDS file for accessing a MDX model, a BISM file only works on tabular models.To create a Power View report, create a BISM Data Source pointing to a tabular cube, within a Data Source library. Then right click on the data source and select create Power View report. At this point the Power View builder displays for you to work in.ÂAt this point I’ve created an RSDS pointing to an MDX cube, while the Power View builder begins loading, an access error is thrown. This error was resolved by installing CU 4 for SQL Server 2012 SP1.
Excel Services vs PowerPivot
When using an MDX cube as the data source, Is there a need for PowerPivot? It seems PowerPivot is really meant for combining disparate data sources into one, and then reporting from that combined source. PowerPivot is simply another layer of abstraction that is unnecessary when querying a cube. Excel Services, PowerPivot, Power View; I really need some time with each of these in SharePoint to understand what satisfies the customers needs and my requirements as a solution provider
Requirements
- Security against the cube passed through within the reports
- SharePoint providing an intuitive, secure, accessible, information chocked repository for viewing and building reports
Research 2
-
- Build a PowerPivot report against the MDX cube
- Build an Excel Services report against the same
- This appears to be simply Excel saved to SharePoint
- Compare the differences. See how the experiences fit with the requirements listed above
Findings 2
- PowerPivot and Excel Services reports are built outside of SharePoint, in Excel, and uploaded to SharePoint.
- Excel Services Report
- Excel > Data > Existing Connections or From Other Sources
- PivotTable, PivotChart or Power View Reports
- Drag and Drop Pivot Table experience
- Drill through works
- Performance is fine, just not as good as PowerPivot
- Hierarchies work
- Data Refresh from Excel works
- Data Refresh in SharePoint works
- PowerPivot Report
- Excel > PowerPivot > Manage > From Database > Analysis Services
- Requires Windows Authentication
- Create Connection
- Design MDX
- Data is pulled in to PowerPivot
- Hierarchies are lost in Excel
- Performance is great!
- Drill through works
- Data Refresh to Excel works, but is slow
- Data Refresh in SharePoint fails withAn error occurred while working on the Data Model in the workbook. Please try again. We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh:AnalysisServices myConnection
- Excel Services Report
- PowerPivot, really seems to be the data merging Add-in provided within Excel. It isn’t required for reporting against a cube. And actually gets in the way.
PowerPivot Gallery
PowerPivot Gallery is simply a special SharePoint document library. The document library does a snapshot using Reporting Services to show a real time image of the report. It might be useful as a landing page for a BI site. Showing the latest reports, or reports that user is in the audience for. Or filtered on some other criteria.
SQL Server License on SharePoint Server
Everything from an Excel Services perspective works without having SQL Server on the SharePoint server. The only outstanding question is Power View. What can be done within Excel and SharePoint, without installing SQL Server on the front end server?
Research 3
- Work with Power View within an Excel Services workbook
- Upload that workbook to SharePoint
- Evaluate what can be done
Findings 3
Power View only works against an MDX cube within SharePoint. SQL Server Reporting Services must be installed on the SharePoint server in order for it to work.
Cube Security
How do we use Windows Authentication and Windows Integrated Security to manage access to cube data?
When accessing data via Excel Services set the EffectiveUserName property on Excel Services. This causes the current user’s credentials to be applied when accessing the cube. Therefore they can only see what they have access to.
Using PowerPivot does not apply security. The data within the workbook is visible to anyone.
Power View
Specifics about when Power View works and doesn’t work:
- Works with MDX cubes but only thru SharePoint
- SQL Server Reporting Services must be installed on SharePoint server
- Within Excel will NOT work with MDX cubes
- Works in Excel with Tabular cubes
- Works in Excel with PowerPivot
- Works in SharePoint against these models/cubes, but SQL Server Reporting Services must be installed on the SharePoint Server
- MDX
- Tabular
- Power Pivot