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. 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. Excel has been used with external data for… well, as long as I’ve been using Excel.
0 Comments
Leave a Reply. |