Power BI supports Power Pivot data models of any version. Users can choose between SQL Server 2008 R2 and SQL Server 2012 Power Pivot for Excel 2010 or simply use the latest Power Pivot version directly integrated with Excel 2013. It goes without saying that the newest version includes more features than its predecessors, offers significant improvements to the user interface, and provides the best and most seamless user experience in Power BI. For these reasons, it is strongly recommended that Power BI user take advantage of the capabilities provided through Excel 2013 and upgrade their existing data models. Upgrading a data model is as easy as opening an existing workbook in Excel, then clicking Manage on the Power Pivot ribbon, and approving the upgrade notification. For step-by-step instructions, see Upgrade Power Pivot Data Models to Excel 2013 at https://office.microsoft.com/en-us/excel-help/upgrade-power-pivot-data-models-to-excel-2013-HA103356104.aspx.
Users who opt to stay with a previous Power Pivot version must accept that certain functionality is not available, such as Power View reports directly embedded into a workbook. Most importantly, users who are stilling using the SQL Server 2008 R2 PowerPivot add-in should upgrade at least to the SQL Server 2012 version and upgrade their workbooks. The SQL Server 2012 SP1 PowerPivot add-in is available for download free of charge at https://www.microsoft.com/en-us/download/details.aspx?id=29074.
If you do not upgrade your SQL Server 2008 R2 PowerPivot workbooks prior to uploading them to Power BI, you might notice that you cannot open these workbooks in the browser. The workbook initially opens fine, but when you interact with the data, such as by clicking on a slicer, an error message might be displayed stating that “An error occurred while working on the Data Model in the workbook.” This is due to a current issue in Power BI preventing the system to load these older data models. The following screenshot shows the error message.
While this issue is temporary and will be fixed shortly, note that essential features, such as external data refresh, will not be available for these older workbooks in any case. For refresh to work in Power BI, the workbook must be upgraded to at least the 2012 Power Pivot version. The following screenshot shows the notification a user receives when attempting to refresh a SQL Server 2008 R2 Power Pivot workbook.
So don’t wait for these issues to slow you down! Upgrade your workbooks prior to uploading the files to Power BI sites. For best results and maximum functionality in Power BI, upgrade your workbooks to the 2013 version of Excel and Power Pivot. Then upload your files to Power BI in order to share your work effortlessly and fully enjoy a seamless user experience with all BI capabilities. Remember, working with Data Models created using SQL Server 2008 R2 Power Pivot in Power BI is not a recommended best practice.