PowerPivot was originally (and still is) an add-in to Excel 2010, and is a built in add-in to Excel 2013. PowerPivot Connections Created in PowerPivot In order to help with the core issues, Microsoft introduced PowerPivot in 2009. A single point of storage along with a measure of oversight and browser access helps to restore a modicum of sanity to an out of control spreadmart environment, but the core issues remain. SharePoint itself supports ODC connections, and therefore users can access these workbooks stored within SharePoint and it also allows them to refresh data from the source either on demand or on open. SharePoint has provided a way to mitigate some of the concerns with these connections. With these spreadmarts, we have multiple versions of the same data being proliferated, and it becomes harder to discern which data is most accurate/current, not to mention the governance implications. Workbooks such as these have become known as “spreadmarts” and are the scourge of IT and business alike. In addition such a file is very large and extremely cumbersome to work with, but even as such, Excel has been the primary tool of choice for business analysts for years.ĭata loaded into the workbook can be refreshed on demand, but it can also be altered, shaped, mashed up, and as is too often the case, grow stale. For small amounts of data, this is just fine, but any large analysis is bound to quickly run into the data limits in Excel which is 1,048,576 rows by 16,384 columns in Excel 2013. In the case of an SSAS connected workbook, the results of the last analysis are saved along with it.
![disable excel queries and connections wizard disable excel queries and connections wizard](http://excelerator.solutions/wp-content/uploads/2017/07/sql-image-9.png)
When the workbook using an ODC connection is saved, the data is saved within it. No data is returned until an analysis is performed (through a pivot table, chart etc), and then only the query results are retrieved. When a connection is made to SSAS, only the connection is created. The one exception to this behaviour is the connection to SQL Server Analysis Services (SSAS). From there, the data can be manipulated and shaped in order to support whatever the end user is trying to do. When using an ODC connection, you establish a connection with a data source, form some sort of query and import the resultant data directly into the Excel workbook. You can create or reuse an ODC connection from the Data tab in the Excel ribbon. ODC (Office Data Connections) are the traditional method of accessing data in Excel. With that said, let’s have a look at all of the options. When it depends, the important thing is to understand the strengths and weaknesses of each approach. Given that there are now multiple tools in Excel for working with external data, it’s not always clear as to which one is the best, and unfortunately there is no single tool that wins over all, although I believe that that will be the case soon.
![disable excel queries and connections wizard disable excel queries and connections wizard](https://blog.crossjoin.co.uk/wp-content/uploads/2014/09/image_thumb6.png)
So why would anyone bother to write a blog post about this given that the capability is so mature? In recent years, Excel has adopted a number of new, and frankly better mechanisms for working with external data, while retaining the old. You should see a new sheet with all the issues listed out.Excel has been used with external data for… well, as long as I’ve been using Excel. In the Compatibility Checker dialog box click the Copy to New Sheet button In the Info section, select the Check for Issues drop-down Using The Compatibility Checker to Find Data Validation Errors: You can manually search through each of your Data Validation rules within your workbook however, that may be a daunting task if you have a lot of tabs to search through. An easier way is to use the Compatibility Checker to search for you. This can occur as the Source input for a List rule. Removing External Links From Data Validation RulesĮxternal Links can reside in Data Validation rules.