PowerPivot data models in Excel is a great way to quickly pull data from various sources, build relationship between different tabs along and creating different calculations. Once a model is built then it can be further utilized in reporting.
Users want to have their reports to show updated information that means the PowerPivot data model that support those analytics should hold updated data, i.e. all data tabs being sourced from different data sources need be refreshed.
We can refresh data in PowerPivot in two ways:
Manually or interactively by refreshing all or specific data connector.
Or we can schedule a data refresh if a PowerPivot model is deployed to SharePoint.
That's where a light disappointment starts developing when we realize that the most frequent data refresh cycle could be scheduled only once a day. Some people tried to interfere and tweak tables in the PowerPivot SQL Server database in SharePoint that are responsible for data model refresh schedules: http://smithicus.wordpress.com/2011/08/09/using-a-custom-data-refresh-schedule-in-powerpivot-for-sharepoint/
A Request has been at the Microsoft Connect website to enhance this functionality and add more granularity to how often a data model could be refreshed. However this request has been closed as by design in December of 2012: https://connect.microsoft.com/SQLServer/feedback/details/739949/powerpivot-2008-2012-for-sharepoint-data-refresh-more-than-once-a-day
At the recent PowerBI Saturday event in Mississauga Microsoft office I questioned a program manager who works in the SSRS team about this very aspect of the data refresh frequency, and he confirmed that there was no plan to bring that functionality to the PowerPivot model in SharePoint, however he suggested a workaround to solve this problem. The very same approach (suggested by one of my colleagues) is to store my tabular model in a SQL Server where I can have more control.
So this is what I needed to to:
1) Import a PowerPivot data model from an Excel file into a Tabular Model solution
2) then deploy this solution to an SSAS instance
3) create a SQL job to process the newly deployed SSAS tabular database with my unique schedule (hours, minutes, seconds,.. anything you want to)
4) create a BISM data connector in the SharePoint for the newly deployed SSAS tabular database
5) and finally change sourcing or starting developing all my PowerView reports based on that new BISM data connector in SharePoint.
And why this is a viable solution, because PowerPivot BI Semantic Model Connection (.bism) support two data source types:
- A tabular model database running on a standalone Analysis Services instance in tabular server mode.
- and PowerPivot workbooks saved to SharePoint.
At the end users were able to analyze their reports with the updated data.
If a daily data refresh is sufficient for business users reports than we can use out of box feature of SharePoint, however if more granular time intervals for PowerPivot model data refresh are required that we can:
A) design a PowerPivot data model in Excel
B) Import it to the SSAS tabular database
C) schedule more frequent database processing jobs for the new SSAS tabular database and use it as a source for your PowerView reports.
Happy data adventures!
PS. This material was also presented at the Toronto's SQL Server User Group on 2014-Oct-30.
