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.
- 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?
How to build a Power View report within SharePoint?
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.
- Security against the cube passed through within the reports
- SharePoint providing an intuitive, secure, accessible, information chocked repository for viewing and building reports
-
- 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
- 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.
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?
- Work with Power View within an Excel Services workbook
- Upload that workbook to SharePoint
- Evaluate what can be done
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.
- 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